XML FeedsI 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?
Here are the answers
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
- 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
*** If you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
Is it allowed to use datetime data type?
This was a response to a question where it cam in as a string, if you had a datetime hoiw would you pass in an offset? you would have to use a different param. But yes would make it much easier
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.