topic Know date format from Database table in qlikview in QlikView

Know date format from Database table in qlikview

timanshu — Fri, 15 Sep 2017 06:21:28 GMT

How can I know format of date in oracle table in qlikview. Because qlikview converts each date in specified format (in Date Format). SO original format is gone.

I need to know this to restrict data in qlikview while fetching data from database table.

Re: Know date format from Database table in qlikview

timanshu — Mon, 18 Sep 2017 04:55:25 GMT

Anyone Please help.

Re: Know date format from Database table in qlikview

Chanty4u — Mon, 18 Sep 2017 07:57:46 GMT

while loading the data from oracle you can see the data how it is coming right ? what is the issue?

Re: Know date format from Database table in qlikview

prieper — Mon, 18 Sep 2017 08:32:52 GMT

Think that Oracle is using a format like DD-MMM-YYYY,

So you may load TEXT(OracleDate) AS OracleDate,

If you wish to convert please use

DATE(DATE#(OracleDate, 'DD-MMM-YYYY'))

This will read the date as provided by Oracle and convert into the settings used within your document.

If you need any other formatting you may use

DATE(DATE#(OracleDate, 'DD-MMM-YYYY') , 'DD-MMM-YYYY')

so you will have a field recognized as date (number) and shown in Oracle-Format.

Re: Know date format from Database table in qlikview

Or — Mon, 18 Sep 2017 08:56:54 GMT

There's no such thing as a "format of date in oracle table". Oracle databases store dates as follows:

The database stores dates internally as numbers. Dates are stored in fixed-length fields of 7 bytes each, corresponding to century, year, month, day, hour, minute, and second

(See below for specifics)

Whatever format you see when you're querying your Oracle database depends on the settings of your client or the database's regional settings. You might see these differently if you were to run the same query on multiple computers, different SQL clients, etc.

You can use TO_CHAR to convert the date in the SQL query itself, maintaining whatever format you want - but this will no longer be a date (though it can be broken back down into a date using its components parts).

select dump(begindate) from tab; Typ=12 Len=7: 100,112,2,7,1,1,1

The format of the date datatype is byte 1 - century (excess 100) 100 - 100 = 00 byte 2 - year (excess 100) 112 - 100 = 12 byte 3 - month = 2 byte 4 - day = 7 byte 5 - hour (excess 1) 1 - 1 = 0 byte 6 - minute (excess 1) 1 - 1 = 0 byte 7 - seconds (excess 1) 1 - 1 = 0

Re: Know date format from Database table in qlikview

Kushal_Chawda — Mon, 18 Sep 2017 09:31:39 GMT

To know the exact format of the Date field in database you need to ask this question to database team which handles the database. Generally oracle stores the date in YYYY-MM-DD format.

If you want to restrict the data from database then no need to check for format, you can do something like below

LET vDateOffset= date(makedate(21,1,2015),'DD/MM/YYYY'); // 21 Jan 2015

SQL SELECT *

FROM tablename

WHERE to_date(to_char(DATE,'DD/MM/YYYY'),'DD/MM/YYYY')>= to_date('$(vDateOffset)','DD/MM/YYYY');

I have defined the variable which you can change as per the requirement

Re: Know date format from Database table in qlikview

timanshu — Thu, 21 Sep 2017 05:03:08 GMT

Thanks Kushal.

But If qlikview can restrict the data by date directly if I know the format from database team. Then there must be some way to know format in qlikview. Since it seems so weird that I cannot know the input date format in qlikview.

Re: Know date format from Database table in qlikview

timanshu — Thu, 21 Sep 2017 05:07:55 GMT

Great Knowledge Shoham. Thanks. Though this does not solve my problem but u made things understandable.

Re: Know date format from Database table in qlikview

Kushal_Chawda — Thu, 21 Sep 2017 07:10:50 GMT

I am not sure why you want to know database format. Once you have Date field available in QlikView you can format the Date way you want to be display based on user requirement. If you want to restrict the data from database then I already replied with the method. I am still not clear on the use case to know Date format in database .