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

I would like the format to be

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