August 29, 2011 at 4:25 am
I have a date time as '4/26/2011 5:00'. I need to convert this to UTC format and find out the difference of this in seconds with '01/01/1970 00:00'. How to query this ?
August 29, 2011 at 9:44 am
sanujss (8/29/2011)
I have a date time as '4/26/2011 5:00'. I need to convert this to UTC format and find out the difference of this in seconds with '01/01/1970 00:00'. How to query this ?
Not sure what you mean by UTC format? All datetime values are just that. If what you are trying to determine is that was the UTC data at the time of the datetime value on your system that is somewhat trickier due to changes in daylight saving time adjustments and such. You probably need to build in a case structure to get it accurate.
Think something like this MIGHT get you close to what you are looking for.
declare @BeginDate datetime = '1/1/1970'
declare @MyDate datetime = '4/26/2011 5:00'
select DATEADD(hh, DATEDIFF(HH, getutcdate(), getdate()), @MyDate)
select DATEDIFF(S, @BeginDate, DATEADD(hh, DATEDIFF(HH, getutcdate(), getdate()), @MyDate))
check out the DATEDIFF function from BOL.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 29, 2011 at 9:54 am
Yeah!! This was what I was looking for .. Thanks dear 🙂
August 29, 2011 at 11:46 am
Wouldn't it be easier to convert your constant '1970-01-01' UTC to local time than convert your variable local time to UTC time, particularly since DST is driven by local time? Since you're interested in the difference between the two dates, it doesn't matter which time frame you use as long as the time frames match.
Drew
PS: You need to be careful about how you write dates. The format you used, depends on the localization setting on the the computer to be correctly interpreted. I tend to use ODBC Canonical for SQL scripts: 'yyyy-mm-dd hh:mm.ss' in 24h format.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply