I am really hoping someone will either be able to answer this in seconds or possibly someone has done this already with Adobe Data warehouse and SQL.
We have a feed that has a date column however the date is in the following format:
"MONTH DD, YYYY, 'Hour' H" so for example: "June 4, 2017, Hour 8" or "October 21, 2016, Hour 23"
I am looking for a simple one liner that can convert this into a more date time friendly format such as the following:
"YYYYMMDD HH:MM:SS" such as "20170604 08:00:00" or "20161021 23:00:00"
I would be very grateful for any help. Thanks
Declare @YourTable table (SomeCol varchar(50))
Insert Into @YourTable values
('June 4, 2017, Hour 8')
,('October 21, 2016, Hour 23')
Select *
,Convert(datetime,replace(SomeCol,', Hour','' )+':00:00' )
from @YourTable
If 2012+, I would recommend Try_Convert()... just in case you have unexpected values.
Returns
SomeCol (No column name)
June 4, 2017, Hour 8 2017-06-04 08:00:00.000
October 21, 2016, Hour 23 2016-10-21 23:00:00.000
Panagiotis Kanavos has a valid point
Parse(replace(SomeCol,', Hour','' )+':00:00' as datetime using 'en-US')