I would like to convert a field containing times to 3 columns. I do not have control over the data as it resides in a linked read only table from a vendor. The data looks like:
Time
09:00 am - 10:00 am PST
0800 am - 10:00 am EST
I would like it to be 3 fields for start, and end timezone if possible. Any help would be appreciated.
I've been trying to parse the field using statements like this but I have not been successful getting all 3 separated.
select parse('09:30 am' as time) as time_value
Time |
---|
09:00 am - 10:00 am PST |
09:00 am - 10:00 am PST |
12:00 pm - 01:00 pm EST |
10:00 am - 11:00 am PST |
10:00 am - 11:00 am PST |
10:00 am - 11:00 am PST |
01:00 pm - 02:00 pm EST |
10:00 am - 11:00 am PST |
10:00 am - 11:00 am PST |
10:00 am - 11:00 am PST |
01:00 pm - 02:00 pm EST |
01:00 pm - 02:00 pm EST |
10:00 am - 11:00 am PST |
10:00 am - 11:00 am PST |
10:00 am - 11:00 am PST |
11:00 am - 12:00 pm PST |
10:00 am - 11:00 am PST |
10:00 am - 11:00 am PST |
10:00 am - 11:00 am PST |
10:00 am - 11:00 am PST |
I would like the format to be
StartTime | EndTime | TimeZone |
---|---|---|
9:00 | 10:00 | PST |
9:00 | 10:00 | PST |
12:00 | 1:00 | EST |
10:00 | 11:00 | PST |
10:00 | 11:00 | PST |
10:00 | 11:00 | PST |
1:00 | 2:00 | EST |
10:00 | 11:00 | PST |
10:00 | 11:00 | PST |
10:00 | 11:00 | PST |
1:00 | 2:00 | EST |
1:00 | 2:00 | EST |
10:00 | 11:00 | PST |
10:00 | 11:00 | PST |
10:00 | 11:00 | PST |
11:00 | 12:00 | PST |
10:00 | 11:00 | PST |
10:00 | 11:00 | PST |
10:00 | 11:00 | PST |
10:00 | 11:00 | PST |
CodePudding user response:
With just a bit of string manipulation
Declare @YourTable Table ([Time] varchar(50)) Insert Into @YourTable Values
('09:00 am - 10:00 am PST')
,('09:00 am - 10:00 am PST')
,('12:00 pm - 01:00 pm EST')
Select *
,StartTime = try_convert(time,substring([Time],1 ,8))
,EndTime = try_convert(time,substring([Time],12,8))
,TimeZone = right([Time],3)
From @YourTable
Results