topic Re: Convert date format in QlikView

Convert date format

rdsuperlike — Mon, 26 Jan 2026 18:19:17 GMT

I am trying to convert the date from CY2014-01Jan to 1/1/2014 in qlikview. I used date function but didnt get any results

I am picking up CY2014-01Jan from excel load.

Any inputs?

Re: Convert date format

hic — Mon, 05 Jan 2015 19:46:35 GMT

Are the dates left-aligned? If so, they are text (i.e. not interpreted as dates), and you need to use the Date#()-function to force QlikView to understand that these are dates.

See more on Get the Dates Right

HIC

Re: Convert date format

engishfaque — Mon, 05 Jan 2015 20:07:00 GMT

Dear Rdsuperlike,

Kindly find attached document.

Table1:

LOAD date(date#(SubField(YourDateFieldName, 'CY'), 'YYYY-DDMMM'),'DD/MM/YYYY') as DateFormat

FROM

DateSample.xlsx

(ooxml, embedded labels, table is Sheet1);

Kind regards,

Ishfaque Ahmed

Re: Convert date format

jpapador — Mon, 05 Jan 2015 20:09:07 GMT

In your load script:

MakeDate(Mid(DateField, 3, 4), Mid(DateField, 8, 2), 1) as NewDateField

Re: Convert date format

MarcoWedel — Mon, 05 Jan 2015 20:10:38 GMT

Date(Date#(Mid(datefield,3),'YYYY-DDMMM'),'M/D/YYYY')

Re: Convert date format

MarcoWedel — Mon, 05 Jan 2015 20:11:41 GMT

Could be

Date(Date#(Mid(datefield,3),'YYYY-DDMMM'),'D/M/YYYY')

as well.

Re: Convert date format

rdsuperlike — Mon, 05 Jan 2015 22:22:06 GMT

Thanks!

I also wanted month, year and day as a seperate field along with this date conversion.

I tried using

Load

date(date#(SubField(Date, 'CY'), 'YYYY-DDMMM'),'DD/MM/YYYY') as Date,

Month(Date) as Month,

Day(Date) as Day,

Year(Date) as Year

But this didnt work. Ny help is appreciated

Re: Convert date format

engishfaque — Tue, 06 Jan 2015 07:39:04 GMT

Dear Rdsuperlike,

Kindly find attached document.

In attached document, everything is separated from the full date, example: (FullDate, Year, Month, Date).

Kind regards,

Ishfaque Ahmed

Re: Convert date format

hic — Tue, 06 Jan 2015 08:14:40 GMT

@ ria

You cannot have the definition of the Date field in the same Load as you use the Date. You need to use a Preceding Load:

Load *,

Month(Date) as Month,

Day(Date) as Day,

Year(Date) as Year;

Load

date(date#(SubField(Date, 'CY'), 'YYYY-DDMMM'),'DD/MM/YYYY') as Date,

...

See more on Preceding Load

HIC

Re: Convert date format

rdsuperlike — Tue, 06 Jan 2015 18:57:43 GMT

Thanks! yeah thats what I tried doing yesterday and it worked!