ASDATE doesnt convert AM/PM correctly

Answered

Follow

Elwyn Roberts

• September 18, 2019 14:45

I have a column which holds the date but as a string.  Its in GMT so I need to convert it to EST as well. 

The original date as string, looks like this:

     14-SEP-19 05.06.25.734000 PM

Before I can convert it, I have to change it to a date field so I used ASDATE function:

     ASDATE(#MODIFIED_DATE; 'dd-MMM-yy HH.mm.ss')

But the result it outputs is:

     2019/09/14 5:06:25 AM

So its converting the date and time ok, but not the PM, for whatever reason its converting it to the opposite - AM.

Any reason for this?

I have tried changing the function to include the "a" at the end as part of the format like this:

     ASDATE(#MODIFIED_DATE; 'dd-MMM-yy HH.mm.ss a')

But it returns an error that it cant parse the string.

0

• Official comment

  

  Alan Mark

  • October 03, 2019 16:31
  • Edited

  Hi Elwyn,

  I created some test data to figure this out and we needed to account for the milliseconds:

  ASDATE(#date_test!date; 'dd-MMM-yy hh.mm.ss.SSSSSS a')

  Comment actions Permalink
• 

  Alan Mark

  • October 03, 2019 16:39
  • Edited

  Hi Elwyn,

  The mistakes here are you are using capital letter 'HH' in your date parse pattern, and you have not indicated an AM/PM marker in your date.  So you're specifying 24 hour time and not informing the parser of the time of day.

  If you use 'hh' it should respect the 12 hour clock, and you'll want to add 'a' to the end of your date pattern as so:

  ASDATE(#MODIFIED_DATE; 'dd-MMM-yy hh.mm.ss a')

  I'd expect the returned output to be: 2019/09/14 5:06:25 PM

  For more information please see our documentation on Date and Time Parse Patterns

  0

  Comment actions Permalink
• 

  Elwyn Roberts

  • October 03, 2019 15:56

  Hi Alan,

   

  Unfortunately I tried that in the past and the "a" doesnt seem to work.  See screenshot attached.  MODIFIED_DT is a STRING datatype.  MODIFIED_DT1 has the formula as shown.
  
  

  0

  Comment actions Permalink
• 

  Elwyn Roberts

  • October 03, 2019 16:36

  Perfect - thanks so much.  Funnily enough in my very first attempts to figure this out I did include the milliseconds, but unbeknownst to me the HH was causing an issue too.
  
  Great work, thank you so much for all your help.

  0

  Comment actions Permalink

Please sign in to leave a comment.