Convert date string into a date timestamp

Question

I have this field in a table that houses a date timestamp in this format (e.g '2023_11_20_23_18_03').

I am trying to find a way to convert this string field into a date field with the formatting ('YYYY/MM/DD HH:MM:ss'). I can not seem to find a good way to parse this field out to create this new format besides using 'LEFT'/'RIGHT' functions.

I am trying to have the field show as '2023/12/31 21:18:03'

Answer 1

for Snowflake use TRY_TO_TIMESTAMP,

select $1 as str
    ,try_to_timestamp_ntz($1, 'yyyy_mm_dd_hh24_mi_ss') as ts
from values
('2023_12_20_23_18_03'),
('2023_11_01_10_18_03');

STR	TS
2023_12_20_23_18_03	2023-12-20 23:18:03.000
2023_11_01_10_18_03	2023-11-01 10:18:03.000

Answer 2

The thing to understand is database do not let you pick the format for date/datetime values. The database will keep these value the way it needs to be efficient for storage, indexing, and date operations, regardless of how you want to display them.

This means when calling to_date(), it's expecting the input format, not the result format, and you actually need to do two operations: one to convert the string to a date, and the other to convert the date a string in the desired format.

SELECT VERSION_CREATE_AT, to_char(to_date(VERSION_CREATED_AT, 'YYYY_MM_DD_HH24_MI_SS'), 'YYYY/MM/DD HH24:MI:SS')
FROM US_OPS_ANALYTICS.UPLOADS.MV_US_LOGISTICS_FINAL_MILE_DETAIL
WHERE left(SCM_WEEK,4)='2024'

---

Also note you still had months in the minutes position, and assuming the question is tagged correctly, Snowflake needs to know if you wanted 12 or 24 -hour time.