Converting a date from one timezone to another timezone

Hi,
I have a requirement for a function which would convert a given date with a present timezone to a new timezone.

For eg : the function would be called lets say TZ

then I can issue the following select statement to get the new timezone

select getdate(), TZ(getdate(), PST, EST)

where PST is the current timezone and EST is the new timezone.

I know that ORACLE has a built in function to do this. Am just wondering if theres some function that I can use to accomplish the task.

Thanks
-soumilyou can write your own function using info from this site (http://wwp.greenwichmeantime.com/info/timezone.htm).|||This is a real pain-in-the-butt to do, not just because of all the time zones, but because even within a time-zone some areas may use Daylight Savings Time, and some may not.

I had to deal with this when I designed an EDI tracking application. In the end we decided it was best to always output the data in GMT and then let the webpage translate it to whatever local time the page was being viewed in. For incoming data, I required all events to include a time stamp from their point of origin, and then I added a local time stamp. By comparing the two values we were able to synchronize events across time-zones without even caring what zone they came from, and it accounted for discrepancies in system-clock times as well.

I hope you can use some work-around like this, or find someone who has already written the procedure/function for you.|||there is one already written by tara check it out:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=28712
harsh.

Convert string into datetime with timezone

I have tried to execute the following sql statement to convert a string with time zone information to datetime without success:

SELECT CONVERT(datetime, '2007-07-24T14:00:00+07:00', 126);

Is there any advice? I'm SQL Server Express 2005 as follows:

Microsoft SQL Server Management Studio Express 9.00.3042.00
Microsoft Data Access Components (MDAC) 6.0.6000.16386 (vista_rtm.061101-2205)
Microsoft MSXML 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 7.0.6000.16473
Microsoft .NET Framework 2.0.50727.1318
Operating System 6.0.6000

Thanks,

Jong

You can only parse it. SQL doesn't recognize that as a legit date format.

You might try this:

Code Snippet

declare @.strDate varchar(255)

set @.strDate = '2007-07-24T14:00:00+07:00'

select convert(datetime,left(@.strDate,len(@.strDate) - patindex('%[+-]%',reverse(@.strDate))))

YOu have to reverse the string before Patindex becuase you need to allow for + or - and of course, there are dashes after the year.

Alternativly, if you can always rely on the exact format:

Code Snippet

declare @.strDate varchar(255)

set @.strDate = '2007-07-24T14:00:00+07:00'

select convert(datetime,left(@.strDate,19))

Neither of these converts the time to GMT...you would have to further parse and then use DateAdd.|||

Here is an illustration of one possible way to handle this issue.

Code Snippet

SET NOCOUNT ON

DECLARE @.MyTable table
( RowID int IDENTITY,
MyDate varchar(30)
)

INSERT INTO @.MyTable VALUES ( '2007-07-24T14:00:00+07:00' )
INSERT INTO @.MyTable VALUES ( '2007-07-24T14:00:00+11:00' )
INSERT INTO @.MyTable VALUES ( '2007-07-24T14:00:00-02:00' )
INSERT INTO @.MyTable VALUES ( '2007-07-24T14:00:00-11:00' )

SELECT
RowID,
LocalTime = dateadd( hour, cast( substring( MyDate, 20, 3 ) AS int ), convert( datetime, left( MyDate, 19 )))
FROM @.MyTable

RowID LocalTime
-- --
1 2007-07-24 21:00:00.000
2 2007-07-25 01:00:00.000
3 2007-07-24 12:00:00.000
4 2007-07-24 03:00:00.000

|||

rusag2 wrote:

You can only parse it. SQL doesn't recognize that as a legit date format.

I thought SQL Server 2005 should recognize it as instructed in http://msdn2.microsoft.com/en-us/library/ms187928.aspx. I'm not sure if there is any difference between SQL Server 2005 and SQL Server Express 2005.

Anyway, thanks for answering.

|||

SQL Server 2005 Xquery includes support for dateTime values with timezone information. So you can use the built-in xquery functions to do the appropriate conversion. You simply pass the datetime string value to one of the xquery methods like value and do the conversion. Below are some examples:

Code Snippet

select cast('' as xml).value('xs:dateTime("2007-07-24T14:00:00+07:00")', 'datetime')

declare @.d varchar(30);
set @.d = '2007-07-24T14:00:00+07:00'
select cast('' as xml).value('xs:dateTime(sql:variable("@.d"))', 'datetime')

select cast('' as xml).value('xs:dateTime(sql:column("t.d"))', 'datetime')

from (select '2007-07-24T14:00:00+07:00') as t(d)

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.