Convert GMT to Local Timezone

I've got the following to convert my seconds field to date/hours, but
now I'd like to convert the NewDate to local time.
Select DATEADD(ss, LastOccurTime, '19700101') as NewDate,
LastOccurTime
Found this on the newsgroup, but I need to go the other way:
SELECT DATEADD(HOUR, DATEDIFF(HOUR, GETDATE(), GETUTCDATE()),
GETDATE())
Suggestions/help are greatly appreciated!
Thanks,
Hank> Found this on the newsgroup, but I need to go the other way:
> SELECT DATEADD(HOUR, DATEDIFF(HOUR, GETDATE(), GETUTCDATE()),
> GETDATE())
How about just swapping GETDATE() and GETUTCDATE() inside DATEDIFF()? This
makes the DATEADD perform the inverse operation.
SELECT DATEADD(HOUR, DATEDIFF(HOUR, GETUTCDATE(), GETDATE()), GETDATE())
An example of exactly what you expect to see would be helpful.

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
>
> 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.a...D=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!

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!

convert datetime to age

I have used the following query to get an age from datetime:
select age
from (select case when dateadd(year, datediff(year, birthdate, getdate()),
birthdate) > getdate() then datediff(year, birthdate, getdate())
-1 else datediff(year, birthdate, getdate()) end as age
from userprofile) as age
I'm trying to get two fields from table userprofile (userid, birthdate) and
viewed as
userid and the age.
gone blank on trying to figure out how to implement the previous query with
mulitple fields from a table.Why are you using a subquery?
select userid, birthdate as age case when dateadd(year, datediff(year,
birthdate, getdate()),
birthdate) > getdate() then datediff(year, birthdate, getdate()) -1 else
datediff(year, birthdate, getdate()) end as age
from userprofile
"chad" wrote:

> I have used the following query to get an age from datetime:
> select age
> from (select case when dateadd(year, datediff(year, birthdate, getdate()),
> birthdate) > getdate() then datediff(year, birthdate, getdate())
> -1 else datediff(year, birthdate, getdate()) end as age
> from userprofile) as age
> I'm trying to get two fields from table userprofile (userid, birthdate) an
d
> viewed as
> userid and the age.
> gone blank on trying to figure out how to implement the previous query wit
h
> mulitple fields from a table.|||Select DateDiff(yy, BirthDate, @.TargetDate)
- Case
When Cast(
Cast(DatePart(yyyy, BirthDate) As Char(4))
+ Right('00' + Cast(DatePart(mm, @.TargetDate) As
VarChar(2)), 2)
+ Right('00' + Cast(DatePart(dd, @.TargetDate) As
VarChar(2)), 2) As SmallDateTime)
< @.TargetDate
Then 1
Else 0
End
From Table
Where @.TargetDate is the date you are comparing against. You could replace
this with GetDate() if you simply wanted their age as of now.
The second part creates an ISO date (yyyymmdd) in the year of the target
date for the month and day of the birth date. If the birth day in the target
year has not transpired, it subtracts one from the total. This technique is
impervious to leap years (well, as impervious as SQL's date interpretation)
and ensures that the value that comes out is one that a human would
interpret as the correct age.
Thomas
"chad" <chad@.discussions.microsoft.com> wrote in message
news:9048E685-D96B-40F4-9B6C-7C6D56D859E4@.microsoft.com...
>I have used the following query to get an age from datetime:
> select age
> from (select case when dateadd(year, datediff(year, birthdate, getdate()),
> birthdate) > getdate() then datediff(year, birthdate, getdate())
> -1 else datediff(year, birthdate, getdate()) end as age
> from userprofile) as age
> I'm trying to get two fields from table userprofile (userid, birthdate)
> and
> viewed as
> userid and the age.
> gone blank on trying to figure out how to implement the previous query
> with
> mulitple fields from a table.|||the original query was for age conversion only and it worked well.
there was syntax errors in your query, where's the THEN expression.
appreciate the help.
"HP" wrote:
> Why are you using a subquery?
> select userid, birthdate as age case when dateadd(year, datediff(year,
> birthdate, getdate()),
> birthdate) > getdate() then datediff(year, birthdate, getdate()) -1 else
> datediff(year, birthdate, getdate()) end as age
> from userprofile
>
> "chad" wrote:
>|||errors out at row 2893 out of 9998.
The conversion of char data type to smalldatetime data type resulted in an
out-of-range smalldatetime value.
"Thomas C" wrote:

> Select DateDiff(yy, BirthDate, @.TargetDate)
> - Case
> When Cast(
> Cast(DatePart(yyyy, BirthDate) As Char(4))
> + Right('00' + Cast(DatePart(mm, @.TargetDate) As
> VarChar(2)), 2)
> + Right('00' + Cast(DatePart(dd, @.TargetDate) As
> VarChar(2)), 2) As SmallDateTime)
> < @.TargetDate
> Then 1
> Else 0
> End
> From Table
> Where @.TargetDate is the date you are comparing against. You could replace
> this with GetDate() if you simply wanted their age as of now.
> The second part creates an ISO date (yyyymmdd) in the year of the target
> date for the month and day of the birth date. If the birth day in the targ
et
> year has not transpired, it subtracts one from the total. This technique i
s
> impervious to leap years (well, as impervious as SQL's date interpretation
)
> and ensures that the value that comes out is one that a human would
> interpret as the correct age.
>
> Thomas
>
> "chad" <chad@.discussions.microsoft.com> wrote in message
> news:9048E685-D96B-40F4-9B6C-7C6D56D859E4@.microsoft.com...
>
>|||Figured it out:
SELECT Age=datediff(year,Birthdate,getdate()),
userid
FROM UserProfile
WHERE UserProfile.Birthdate IS NOT NULL AND
datediff(year,Birthdate,getdate())>=0
ORDER BY userid
"chad" wrote:

> I have used the following query to get an age from datetime:
> select age
> from (select case when dateadd(year, datediff(year, birthdate, getdate()),
> birthdate) > getdate() then datediff(year, birthdate, getdate())
> -1 else datediff(year, birthdate, getdate()) end as age
> from userprofile) as age
> I'm trying to get two fields from table userprofile (userid, birthdate) an
d
> viewed as
> userid and the age.
> gone blank on trying to figure out how to implement the previous query wit
h
> mulitple fields from a table.