topic Re: Change date format in QlikView

Change date format

evansabres — Tue, 17 Mar 2015 17:51:47 GMT

Hello -

I have a field name '[Transaction Date' that is in a format of 'Jan 30, 2015, 12:00 AM.

In my script, using a date map, I changed the format to show as 1/30/2015 using the following script :

MakeDate(Mid([Transaction Date], 8,5), ApplyMap('MonthMap',Left([Transaction Date], 3)),Replace(Mid([Transaction Date],4,3), ',','')) AS Source,

However, I want the format to actually read as 'YYYY/MM/DD'

How can I alter my script to reflect this format.

Thank you.

buzzy996 — Tue, 17 Mar 2015 17:56:57 GMT

can u try,

Makedate(Year([DATEfield]),Month([ DATEfield]),Day([ DATEfield])) as DayMonthYear,

Anonymous — Tue, 17 Mar 2015 18:16:49 GMT

I think this should work:

date(date#("Transaction Date", 'MMM D, YYYY, hh:mm TT'),'YYYY/MM/DD')

Except, I'm not sure D or DD. If single digit days are like 9 - use D, if 09 - use DD.

evansabres — Tue, 17 Mar 2015 19:42:43 GMT

Thank you. Would I add that on the same line of code as my MakeDate or on a separate line?

Anonymous — Tue, 17 Mar 2015 19:46:36 GMT

That's the whole line, no need for makedate():

date(date#("Transaction Date", 'MMM D, YYYY, hh:mm TT'),'YYYY/MM/DD') as Source

evansabres — Tue, 17 Mar 2015 19:47:44 GMT

when I enter that as the line of code, it returns a value of -

Anonymous — Tue, 17 Mar 2015 19:52:01 GMT

See attached - it works. Maybe you missed something in problem description - could you please upload your file?