Posted by Joseph, Jan. 4, 2024, 6:30 a.m.
hi
I have a field with a start and end date (sometimes no end date) in a varchar(4000) format(as below) .
How can I parse this out to have two fields start and end dates?
Data looks like
~__2019121407025301GMTOFFSET=-18000~__2019121415164202GMTOFFSET=-18000~
desired out is
Start Date 12/14/2019 7:02 AM
End Date 12/14/2019 3:16 PM
Thanks
What databse are you using?
For MySQL, something like:
SELECT
-- Extract and format the start date and time
DATE_FORMAT(STR_TO_DATE(SUBSTRING(column_name, 4, 14), '%Y%m%d%H%i%s'), '%m/%d/%Y %h:%i %p') AS `Start Date`,
-- Extract and format the end date and time, handling cases where it might be missing
CASE
WHEN CHAR_LENGTH(column_name) > 32 THEN
DATE_FORMAT(STR_TO_DATE(SUBSTRING(column_name, 34, 14), '%Y%m%d%H%i%s'), '%m/%d/%Y %h:%i %p')
ELSE
'No End Date'
END AS `End Date`
FROM your_table_name;
