Converting character string representing a timestamp to a timestamp data type with specific format in SQL

Question

I have a column in my SQL database where timestamps are stored as character strings in the format 'YYYY-MM-DD HH24:MI:SS.FF'. For example, '2006-01-01 00:00:00.000000'.

Now, I need to convert these character strings into timestamp data types with a specific format, 'MM/DD/YYYY HH12:MI:SS AM'. For example, '1/1/2006 12:00:00 AM'. It's important to remove the '0'-es from the result.

I've tried using the TO_TIMESTAMP() function along with TO_CHAR(), but I'm not getting the desired output. Here's what I've tried:

SELECT TO_CHAR(TO_TIMESTAMP('2006-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF'), 'MM/DD/YYYY HH12:MI:SS AM') AS formatted_timestamp;

However, this query doesn't give me the exact output I'm looking for. The result the script above is giving is this:

01/01/2006 12:00:00 AM

Can someone please help me with the correct SQL query to achieve this conversion?

Thanks in advance!

Answer 1

Following statement works in most of the SQL databases, when a string is in proper date format. May work for yours as well.

Also note that, not all the time standards in capital letter as you specified in format, like months have MM whereas minutes have mm

FORMAT(CAST([column] AS DATETIME), 'MM-dd-yyyy HH:mm:ss tt')

You can use either DATETIME or DATETIME2 in the CAST function

Answer 2

In order to get the result without the zeroes you need to add FM to the parts where you want to exclude the zeroes. FM suppresses blanks and zeroes. Do it like this:

SELECT TO_CHAR(TO_TIMESTAMP('2006-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF'), 'FMMM/FMDD/YYYY HH12:MI:SS AM') AS formatted_timestamp