Date Format

[soha1902]

Hi All,

I am loading Date Fields from Data base in the following format:

DB_Date

20010430

20150615

20150616

20150617

20150618

20150619

20150622

20150629

20150630

20020430

20141229

20020501

20020502

20020503

20020504

20020507

20020508

20020509

20020510

20020511

20020514

20020515

20020516

20020517

20020518

Now I am generating another date using below script:

Calendar_Temp

load
        if(recno() = 1, makedate('2009', '1', '1'), peek('Cal_Date')+1) as Cal_Date
         autogenerate(365*(year(today())-2008));

I did the following Transformation in both the dates:

Date:

Load
Date,
Date(Date,'YYYY/MM/DD') as Date_DB
Resident DB_Date;

Calender:

LOAD

Date(Date,'YYYY/MM/DD') as Date_CalGen

Resident Calendar_Temp;

DROP Tables Calendar_Temp,Date_Temp;

This executes sucessfully and I am getting the fields like

　

I used the same date format (YYYY/MM/DD) but still I am getting both the date format different. Can Any one suggest me how I'll fix this and how I'll get both the dates in same format.

I am using the personal edition so please share the script.

Thanks in advance.

[avinashelite]

Hi Soha,

Check the date format in DB, might be it is saved in UNIX time Stamp or other format ..and convert it accordingly

[qlikmsg4u]

Date:

Load
DB_Date,
Date(Date#(DB_Date,'YYYYMMDD'),'YYYY/MM/DD') as Date
Resident DB_Date;

try this

[sasiparupudi1]

Date(Date#(Date,'YYYYMMDD'),'YYYY/MM/DD') as Date_DB

[hic]

You need to use Date(Date#(...)) as indicated in several answers above.

A date is internally stored as a number that is roughly equal to 42000, but you load numbers that are roughly 20 millions. See more on Why don’t my dates work? and Get the Dates Right

HIC

[#6]

I had same issue. our date DB the shows a PeriodID as YYYYMMDD but this is a numeric format and not a date number format.

however, if you have another DB Table, with the normal date format such as MM/DD/YYYY and what to join both table. you might want to use the script "Date(Date#(Date,'YYYYMMDD'),'YYYY/MM/DD') as Date_DB" on the Date_DB and any other table that uses that date format.

Regards,

Daniel

[qlikviewwizard]

Hi,

For DB_Date use the following transformations.

Date(Date#(DB_Date,'YYYYMMDD'),'YYYY/MM/DD') as Date

[evan_kurowski]

Yeah, sometimes that alt function can return a number, but it is still way out of bounds.  Some range checking helps bring things back inline sometimes.