Netezza how read date time from character in the format of MON DD YYYY hh:mi am. I am getting an invalid date

Question

In Netezza I have a field which contains characters which represents dates in the format of (I'm guessing) 'MON DD YYYY hh:miam'

select distinct nbr_cust ,as_of_date ,to_date(as_of_date,'Mon DD YYYY') as asOfDate from MyNetezzaTable

Sample of the as_of_date Jul 2 2018 4:30PM

Mar 6 2017 6:32PM

Feb 2 2016 12:58PM

Mar 31 2014 5:18PM

Jun 4 2018 6:55PM

I've tried to convert with to_date, and to_timestamp without any luck.

I keep getting an Invalid Date

Answer 1

Cause The problem is the single digit hour. If the date string.

Resolving the problem If it is not possible to ensure that the input strings always provide two digits for the hour value, you can use "FM" (fill mode) modifier in the template pattern. The “FM” prefix provides missing leading zeros. This SQL inserts the original string without error:

to_date(as_of_date,'Mon DD YYYY FMHH12:MIAM') as asOfDate