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

 T-SQL ```1 2 3 4 ``` ```declare @date varchar(100) select @date = '2009-06-26 14:30:00.000Z+4:30'   select right(@date,2)```
```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

 T-SQL ```1 2 3 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```
```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

 T-SQL ```1 2 3 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```
```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

 T-SQL ```1 2 3 ``` ```declare @date varchar(100) select @date = '2009-06-26 14:30:00.000Z+14:30' select replace(substring(@date,patindex('%z%',@date)+ 1,3),':','')```
```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

 T-SQL ```1 2 3 4 ``` ```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```
```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

 T-SQL ```1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 ``` ```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     select dateadd(mi, @multiplier *convert(int,right(@date,2)),dateadd(hh     ,-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   select dateadd(mi, @multiplier *convert(int,right(@date,2)),dateadd(hh     ,-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   select dateadd(mi, @multiplier *convert(int,right(@date,2)),dateadd(hh     ,-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   select dateadd(mi, @multiplier *convert(int,right(@date,2)),dateadd(hh     ,-1 * convert(int,replace(substring(@date,patindex('%z%',@date)+ 1,3),':',''))     ,left(@date,23))) go   --2009-06-27 05: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 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