topic how to parse date format without leading zeros? in App Development

how to parse date format without leading zeros?

daveatkins — Wed, 01 Sep 2021 15:32:11 GMT

I am loading data from an SSIS task monitor database where the datetime fields come through in this format:

Sep 1 2021 9:35AM Aug 31 2021 9:00PM

When I attempt to use Date# or Timestamp# to interpret that, I find that

'MMM DD YYYY hh:mmtt'

does not work and I must resort to something like this...plus some if statements to test whether it's a single or multiple digit for the month or hour

=Timestamp(Timestamp#(LastRun,'MMM D YYYY h:mmTT'))

Is there a better way???

Re: how to parse date format without leading zeros?

Kushal_Chawda — Wed, 01 Sep 2021 15:45:16 GMT

@daveatkins if you use below, it works for both single and double digit day and hour values

LOAD * , timestamp#(Date,'MMM DD YYYY hh:mmTT') as Date2 Inline [ Date Sep 1 2021 9:35AM Aug 31 2021 9:00PM Aug 12 2021 10:00AM Aug 3 2021 10:30AM Aug 22 2021 6:30AM ];

Re: how to parse date format without leading zeros?

daveatkins — Wed, 01 Sep 2021 15:59:35 GMT

your example does not match my data; the issue is that my numbers are formatted with leading spaces. I was able to use this code to make it work:

=Timestamp(Timestamp#(
left(LastRun,4) & trim(mid(LastRun,5,2)) & mid(LastRun,7,6) & trim(right(LastRun,7))
,'MMM D YYYY hh:mmTT'))

Re: how to parse date format without leading zeros?

Saravanan_Desingh — Wed, 01 Sep 2021 17:03:48 GMT

Try somethin like this,

Timestamp(Alt(Timestamp#(LastRun,'MMM D YYYY h:mmTT'), Timestamp#(LastRun,'MMM DD YYYY h:mmTT')) )

Re: how to parse date format without leading zeros?

Kushal_Chawda — Thu, 02 Sep 2021 00:30:09 GMT

@daveatkins try below. If chr(32) does not work then try chr(160)

map_spaces: Mapping LOAD Repeat(chr(32),RowNo()) as Row, ' ' as Map_space AutoGenerate 5; LOAD * , timestamp(timestamp#(trim(MapSubString('map_spaces',Date)),'MMM DD YYYY hh:mmTT')) as Date_formatted Inline [ Date Sep 1 2021 9:35AM Aug 31 2021 9:00PM Aug 12 2021 10:00AM Aug 3 2021 10:30AM Aug 22 2021 6:30AM ];