topic Re: Date in Different Format in QlikView

Date in Different Format

Fri, 08 Jun 2012 21:42:16 GMT

the Databse has data for Document Creted_date in the given below format

13-FEB-01 12.00.00.000000000 AM

now I want to generate a report with has Status,Date Created and other fatcts in Qlikview.

Qlikview is not fecthing data for Dates can anyone pleas help me.

Date in Different Format

Fri, 08 Jun 2012 23:50:39 GMT

Hi,

If my previous script not working, try to define more the time format, with this:

Date(TimeStamp#(DateField, 'DD-MMM-YY hh.mm.ssTT')) as CreatedDate

Hope its can help you.

Regards,

Heri

Re: Date in Different Format

Sokkorn — Sat, 09 Jun 2012 02:18:51 GMT

Hi Vikas,

This one should work for you

[Data]:
LOAD
     DateField AS [Date1],
     DATE(DATE#(DateField,'DD-MMM-YY hh.mm.ss.fff tt'),'DD-MMM-YYYY') AS [Date2],
     ...
SELECT * FROM TableName;
/********************************************************************************************************
If your DB have DateField format as 13-FEB-01 12.00.00.000000000 AM. Then after load above script, you will get:
     [Date1] = 13-FEB-01 12.00.00.000000000 AM
     [Date2] = 13-Feb-2001

Hope this help.

Regards,

Sokkorn

Date in Different Format

Mon, 11 Jun 2012 15:48:58 GMT

Hi Sokkorn, The format you gave is showing error.

But I am getting null values when I am putting this in Qlikview. Actually I am not able to see the the Created_Date field after reloading the data in Qlikview.SET TimeFormat ='hh.mm.ss.fff tt';
SET DateFormat ='DD-MMM-YY';
SET TimestampFormat='DD-MMM-YY hh:mm:ss[.fff] TT';
SET MonthNames='JAN;FEB;MAR;APR;MAY;JUN;JUL;AUG;SEP;OCT;NOV;DEC';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

OLEDB CONNECT TO

LOAD "CREATED_DATE";
SQL SELECT "CREATED_DATE" FROM Table_Name;

Regards,

Vikas

Re: Date in Different Format

Sokkorn — Wed, 13 Jun 2012 03:18:57 GMT

Hi Vikas,

Sorry for not response you back on time. Let try this load statement

LOAD *;
SQL SELECT CREATED_DATE FROM Table_Name;

Regards,

Sokkorn