Date formatting

[#1]

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

[#2]

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.

[#3]

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.

[#4]

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.

[#5]

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

[#6 · Accepted solution]

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.

[#7]

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

[#8]

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

Regards GJ.