December 9, 2008 at 12:59 am
Hi,
How can we convert the System local date to UTC date. Is there any function to convert the date.
I have the production server in Belgium and Another dev server in US.
Now I am checking one of the tables records whose modified date is less than 2 hours, then it will update the records in destination table. If not it skips
Due to a time difference of 9 hours, the package trying to insert the same data which causes the Primary key violation.
Is there a fix for this?
Thank You
December 9, 2008 at 7:29 am
you can change the system time on your SQL servers to be all the same timezone or you can have the timezone accurate for each location and insert all your records in UTC time.
You can update the information in your tables with something like.
DECLARE @Offset int
SELECT @Offset = DATEDIFF(hour,GetDate(),GetUTCDate())
UPDATE )
FROM
December 9, 2008 at 7:33 am
The above solution will work. SQL Server doesn't care what the date data is. It stores it. The applications need to ensure they are both inserting properly and they are then converting the data back to local time.
If you were using getdate(), getutcdate() will help, but clients and code then need to convert this back if clients need to see local time.
In 2008 we have numerous other functions that help handle date offsets.
December 9, 2008 at 8:37 am
Joel Ewald (12/9/2008)
you can change the system time on your SQL servers to be all the same timezone or you can have the timezone accurate for each location and insert all your records in UTC time.You can update the information in your tables with something like.
DECLARE @Offset int
SELECT @Offset = DATEDIFF(hour,GetDate(),GetUTCDate())
I agree with the first statement, about getting UTC time going forward, but I believe that the second statement is wrong about changing the date stored in existing records. It will only work if the times recorded were never in daylight savings time, since UTC doesn't adjust durring daylight savings.
December 9, 2008 at 8:40 am
Steve Jones - Editor (12/9/2008)
...getutcdate() will help, but clients and code then need to convert this back if clients need to see local time.
in 2005 is there a way for the client know if the UTC time recorded was durring daylight savings? I haven't worked with 2008 yet
December 9, 2008 at 9:17 am
Here is some good code you can leverage for the DST issue.
It will have to be modified for your specific needs but is a good starting point.
December 10, 2008 at 5:55 am
Thanks to one and all for your valuable sugesstions.
Yes, I do agree with your discussion that it is not good to change the total records form local time to UTC time.
That's why I thought of changing the time from one zone to another zone.
Joel has given a good link to work around. It has the user deifined function to change the times.
But one issue is that for a simple SSIS package do we need to do this much effort that need to create one function and a table. Is there any predefined function in SQL Server 2005 to covert the time from one zone to another?
And also mentioned in the link that it may give some performace issues?
I am adding the code where I need this____
Where hrec_LNDtTimestamp >= DATEADD(hh,-2,GETDATE())
Here I have to check for modified records for the past 2 hours and need to transfer the data from one table to another. The package is running for every 2 hours.
but source data is coming from Beligium (EST) and the package is running in USA(PST)
If we conver the both source and the sysdate to UTC, will it work?
Is there any predefined function to do that?
Thank You
December 10, 2008 at 7:02 am
I don't see much in the way of functions for 2005 (http://msdn.microsoft.com/en-us/library/ms186724(SQL.90).aspx).
2008 has a lot of time zone stuff.
I thought in 2005 that all the dates were just stored as they were submitted, so unless you have a field that lets you know which time zone it's supposed to be in, I'm not sure how you can tell or convert these.
December 11, 2008 at 12:25 am
Hi,
Any other Ideas on this discussion from any one?
Thank You
December 11, 2008 at 9:07 am
What we do in our systems, for datetimes that our clients can see in the user interface or reports, we use their local time (keeping track of what time zone each client location is in) and for internal datetimes that are only used by us for our internal purposes, we just use our own consistant time (in our case U.S. Eastern, but for someone else could be UTC)
Below is a code sample of the time zone table we use and a query with the formula we use to convert our server's Eastern time into the client location's time. This formula will adjust for Hawaii and Arizona that doesn't use daylight savings:
CREATE TABLE #TimeZone (
TimeZoneCode varchar(5),
TimeZoneDesc varchar(50),
UTCDifference decimal(5,2),
ObserveDaylightSavings bit,
DisplaySeq smallint)
INSERT INTO #TimeZone
(TimeZoneCode, TimeZoneDesc, UTCDifference, ObserveDaylightSavings, DisplaySeq)
SELECT 'HIST', '(UTC - 10:00) Hawaii', -10.00, 0, 1
UNION ALL
SELECT 'AKST', '(UTC - 09:00) Alaska', -9.00, 1, 2
UNION ALL
SELECT 'PST', '(UTC - 08:00) Pacific Time', -8.00, 1, 3
UNION ALL
SELECT 'AZST', '(UTC - 07:00) Arizona', -7.00, 0, 4
UNION ALL
SELECT 'MST', '(UTC - 07:00) Mountain Time', -7.00, 1, 5
UNION ALL
SELECT 'CST', '(UTC - 06:00) Central Time', -6.00, 1, 6
UNION ALL
SELECT 'EST', '(UTC - 05:00) Eastern Time', -5.00, 1, 7
SELECT TimeZoneCode,
DateAdd(hour, ObserveDaylightSavings * ABS(DateDiff(hour, GetDate(), GetUTCDate()) - 5), DateAdd(minute, UTCDifference * 60, GetUTCDate())) AS CurrentDateTime
FROM #TimeZone
The -5 in the first DateAdd is because our server is on Eastern time.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply