Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Date formatting

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

1 Solution

Accepted Solutions
Not applicable
Author

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.

View solution in original post

7 Replies
Not applicable
Author

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.

Not applicable
Author

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.

Not applicable
Author

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.

Not applicable
Author

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

Not applicable
Author

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.

Not applicable
Author

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

Not applicable
Author

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

Regards GJ.