Tuesday, February 14, 2012

Convert GMT to Local Time

Hi,
I have the following query which works fine, but now I want to convert
the date/time to local time (-7 GMT).
Select DateAdd(ss, LastOccurTime, '19700101') as EventDate
>From Event
Order By EventDate DESC
The LastOccurTime field is in seconds in the database so I have to
conver that first.
Thanks!<hammerin.hankster@.gmail.com> wrote in message
news:1144788163.801906.131970@.i40g2000cwc.googlegroups.com...
> Hi,
> I have the following query which works fine, but now I want to convert
> the date/time to local time (-7 GMT).
> Select DateAdd(ss, LastOccurTime, '19700101') as EventDate
>>From Event
> Order By EventDate DESC
> The LastOccurTime field is in seconds in the database so I have to
> conver that first.
> Thanks!
>
Assuming all values represent the same time zone you can easily subtract a
fixed number of hours by adjusting the base date for the calculation:
SELECT DateAdd(ss, LastOccurTime, '1969-12-31T17:00:00.000') as EventDate
This won't account for Daylight Saving Time (if any). To do that you would
need to know what time difference was in effect for each date in your table.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||If 2005, here is a way to convert from UTC to any local time or between
local times, etc
http://channel9.msdn.com/ShowPost.aspx?PostID=142586#142586
--
William Stacey [MVP]
<hammerin.hankster@.gmail.com> wrote in message
news:1144788163.801906.131970@.i40g2000cwc.googlegroups.com...
| Hi,
|
| I have the following query which works fine, but now I want to convert
| the date/time to local time (-7 GMT).
|
| Select DateAdd(ss, LastOccurTime, '19700101') as EventDate
|
| >From Event
|
| Order By EventDate DESC
|
| The LastOccurTime field is in seconds in the database so I have to
| conver that first.
|
| Thanks!
||||Thanks for response. Worked great!

No comments:

Post a Comment