Know date format from Database table in qlikview

[timanshu]

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.

[timanshu]

Anyone Please help.

[Chanty4u]

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

[prieper]

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.

[Or]

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

[Kushal_Chawda]

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

[timanshu]

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.

[timanshu]

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

[Kushal_Chawda]

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 .