Home > Enterprise >  How do I convert field to time
How do I convert field to time

Time:12-20

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

enter image description here

  • Related