Parsing post meridiem time (PM) with to_timestamp() returns null

When converting 12-hour time to 24-hour time with to_timestamp() the hours variable must be lowercase.

Last published at: July 22nd, 2022

Problem

You are trying to parse a 12-hour (AM/PM) time value with to_timestamp(), but instead of returning a 24-hour time value it returns null.

For example, this sample code:

%sql

SELECT to_timestamp('2016-12-31 10:12:00 PM', 'yyyy-MM-dd HH:mm:ss a');

Returns null when run:

to_timestamp() requires the hour format to be in lowercase.

If the hour format is in capital letters, to_timestamp() returns null.

Make sure you specify the hour format in lowercase letters.

For example, this sample code:

%sql

SELECT to_timestamp('2016-12-31 10:12:00 PM', 'yyyy-MM-dd hh:mm:ss a');

Returns the time as a 24-hour time value.