topic Re: Date formatting in QlikView

Date formatting

Thu, 13 Oct 2011 07:42:41 GMT

Hi,

I'm having trouble with date formatting,

I have a table with order dates, and as we all know; date formats are rarely all standardized. I have the following kind of dates:

YYYY-MM-DD
39448 (as a number)
M/DD/YYY

and so on..

Is there any way to script these to make them all the same? I want the output to be YYYY/MM/DD

Thanks,

asolsson

Date formatting

Thu, 13 Oct 2011 07:49:56 GMT

Hi There,

You could try the following in the script:

DATE("Date Field",'YYYY/MM/DD') AS "Your Date Field"

Hope it helps,

Regards GJ.

Re: Date formatting

Thu, 13 Oct 2011 07:55:49 GMT

Worked on some cells, but most of my cells turned into " - "-signs. Any ideas?

EDIT:

When I went through my RAW data it showed that most of the fields that are shown as odd signs are the ones that are originally formateed as M/DD/YYYY.

Date formatting

Thu, 13 Oct 2011 08:01:42 GMT

What about using DATE# instead of DATE on the other fields. For Example:

DATE#("Date Field",'YYYY/MM/DD') AS "Your Date Field"

Are all of you fields in date format from the data source?

The above will interpret fields that are not necessarily in a date format, as a date.

Might be the solution to your problem.

Let me know if it works?

Regards GJ.

Re: Date formatting

Thu, 13 Oct 2011 08:16:35 GMT

It changed the functional date formats into numbers, yet the M/DD/YYYY format remains.

Re: Date formatting

Thu, 13 Oct 2011 09:25:40 GMT

I thing I might have found the solution to your issue, change the DateFormat variable in your script to 'M/DD/YYYY', please see below.

SET DateFormat='M/D/YYYY';

Then do the date conversion in your script:

DATE("Your Date",'YYYY-MM-DD') AS 'Your Date"

See the attached qvw for an example.

Hope this helps.

Regards GJ.

Re: Date formatting

Thu, 13 Oct 2011 09:43:31 GMT

Ah, you're the best! Thanks a lot! Fixed my problems!

Re: Date formatting

Thu, 13 Oct 2011 10:19:03 GMT

That's Great news! Glad that I could be of assistance.

Regards GJ.