topic Re: Issue with date formats in QlikView

Issue with date formats

Fri, 12 Oct 2012 09:40:33 GMT

Hi,

I have the following code to generate dates:

Date:
LOAD
today()-recno()+1 as Date
autogenerate(today()-'2011-01-01');

DateParts:

LOAD
Date as Date,
Year(Date) as Year,
Month(Date) as Month,

MonthName(Date) as monthYear
RESIDENT Date;

Drop table Date;

However the dates I'm pulling in from my db to the QV script comes in the following format 'DD/MM/YYYY'

This is causing a conflict in my data, and my Month and Year listboxes are not linking with this date from my db.

I've tried to change the date format with the following code:

date#(date(date,'YYYY-MM-DD')) as event_date

Is there anything else I can do?

Thanks

Re: Issue with date formats

Anonymous — Fri, 12 Oct 2012 10:54:07 GMT

Try this Date() or Date#() functions.

Re: Issue with date formats

jonathandienst — Fri, 12 Oct 2012 11:06:50 GMT

Hi - try this for the first load:

Date:

LOAD

Date(today()-recno()+1, 'YYYY-MM-D') as Date

Autogenerate(today()-Date#('2011-01-01', 'YYYY-MM-DD');

Is YYYY-MM-DD the default date format on your system?

Regards

Jonathan

Re: Issue with date formats

Fri, 12 Oct 2012 11:19:14 GMT

The date format I'm pulling into QV from my system is 'DD/MM/YYYY'

Thanks

Re: Issue with date formats

nilesh_gangurde — Fri, 12 Oct 2012 11:37:11 GMT

At load time write the script as follows:

load *,

DateField,

num(DateField) as DateField_NUM,

Date(DateField,'YYYY-MM-DD') as NewDateField;

Select * from TableName;

or anyways the formate of date will not affect the linking because it depends on the julien date.

Regards,

Nilesh Gangurde

Re: Issue with date formats

Fri, 12 Oct 2012 15:06:52 GMT

I used this, thanks for the help...

Date:

LOAD

Date(today()-recno()+1, 'DD/MM/YYYY') as Date

Autogenerate(today()-Date#('10/04/2012', 'DD/MM/YYYY'));