topic Re: Date Format in QlikView

Date Format

soha1902 — Tue, 08 Sep 2015 07:30:52 GMT

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.

Re: Date Format

avinashelite — Tue, 08 Sep 2015 07:34:25 GMT

Hi Soha,

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

Re: Date Format

qlikmsg4u — Tue, 08 Sep 2015 07:34:38 GMT

Date:

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

try this

Re: Date Format

sasiparupudi1 — Tue, 08 Sep 2015 07:35:11 GMT

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

Re: Date Format

hic — Tue, 08 Sep 2015 08:06:14 GMT

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

Re: Date Format

Tue, 13 Oct 2015 19:48:10 GMT

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

Re: Date Format

qlikviewwizard — Wed, 14 Oct 2015 01:37:12 GMT

Hi,

For DB_Date use the following transformations.

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

Re: Date Format

evan_kurowski — Wed, 14 Oct 2015 02:03:57 GMT

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.