how to parse date format without leading zeros?

[daveatkins]

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???

[Kushal_Chawda]

@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  ];

[daveatkins]

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'))

[Saravanan_Desingh]

Try somethin like this,

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

[Kushal_Chawda]

@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  ];