I answered this question today and thought it would be useful to create a little post

If you have a varchar value like this ‘2009-06-26 14:30:00.000Z+4:30’ you want to take 4 hours and 30 minutes and then subtract that from the date itself so in this case you will get 2009-06-26 11:00:00.000. The reason you subtract is because the +4:30 means that this was generated in a zone that is 4:30 ahead of utc

So first we need to figure out a couple of things

1) where are the minutes?

2) where is the hour?

3) is it positive or negative?

1) where are the minutes?

The minutes are the last 2 characters

``````declare @date varchar(100)
select @date = '2009-06-26 14:30:00.000Z+4:30'

select right(@date,2)
``````

2) where is the hour?

The hour starts after the Z and last for 2 or 3 characters including the sign, we will just grab 3 and replace : with an empty string

``````declare @date varchar(100)
select @date = '2009-06-26 14:30:00.000Z+4:30'
select replace(substring(@date,patindex('%z%',@date)+ 1,3),':','')
go
``````

+4

``````declare @date varchar(100)
select @date = '2009-06-26 14:30:00.000Z-4:30'
select replace(substring(@date,patindex('%z%',@date)+ 1,3),':','')
go
``````

-4

``````declare @date varchar(100)
select @date = '2009-06-26 14:30:00.000Z+14:30'
select replace(substring(@date,patindex('%z%',@date)+ 1,3),':','')
``````

+14

3) is it positive or negative?

That we already grabbed above for the hour, for the minute we need to do something like this

``````declare @date varchar(100),@multiplier int

select @date = '2009-06-26 14:30:00.000Z+4:30'
select  case when @date like '%+%' then -1 else 1 end
``````

We also need to convert the stuff we did above to integers in order to add

So here is the complete code

``````declare @date varchar(100),@multiplier int

select @date = '2009-06-26 14:30:00.000Z+4:30'
select @multiplier = case when @date like '%+%' then -1 else 1 end

,-1 * convert(int,replace(substring(@date,patindex('%z%',@date)+ 1,3),':',''))
,left(@date,23)))
go

--2009-06-26 10:00:00.000

declare @date varchar(100),@multiplier int

select @date = '2009-06-26 14:30:00.000Z-4:30'
select @multiplier = case when @date like '%+%' then -1 else 1 end

,-1 * convert(int,replace(substring(@date,patindex('%z%',@date)+ 1,3),':',''))
,left(@date,23)))
go

--2009-06-26 19:00:00.000

declare @date varchar(100),@multiplier int

select @date = '2009-06-26 14:30:00.000Z+14:30'
select @multiplier = case when @date like '%+%' then -1 else 1 end

,-1 * convert(int,replace(substring(@date,patindex('%z%',@date)+ 1,3),':',''))
,left(@date,23)))
go

--2009-06-26 01:00:00.000

declare @date varchar(100),@multiplier int
select @date = '2009-06-26 14:30:00.000Z-14:30'
select @multiplier = case when @date like '%+%' then -1 else 1 end