Fraser_Admin
asked on
getdate to get proper date
I have a server which is located in the eastern time zone and all of my clients are in the atlantic time zone. when i put in getdate() it gets the eastern date. i need to be able to get the atlantic date. is there a command which will show you the date/time where you are at?
Not in sql server 2000/2005; you can get the GMT by using GETUTCDate()
Use
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
function to get the result according to your format
Last parameter is about the required format
Get help in your T-SQL Help on Convert Function and look for the avaible formats.
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
function to get the result according to your format
Last parameter is about the required format
Get help in your T-SQL Help on Convert Function and look for the avaible formats.
Hello Fraser_Admin,
*If* your clients use the same DST rules as you, then you could use:
SELECT DATEADD(h, -1, GETDATE()) AS TimeInAtlanticZone
That will work except for when you get within an hour of the change-over from standard time to
daylight time, and vice versa.
Regards,
Patrick
*If* your clients use the same DST rules as you, then you could use:
SELECT DATEADD(h, -1, GETDATE()) AS TimeInAtlanticZone
That will work except for when you get within an hour of the change-over from standard time to
daylight time, and vice versa.
Regards,
Patrick
ASKER
that is what i ended up doing, but now we may have another client coming on board in the eastern time zone, so i somehow need to find out what time zone they are in so i can display the proper date for them.
I would suggest either using aneeshattingal's solution of using UTC, or pass the date/time from the client application to the server.
Is it possible to add another column to your table (TZO-Time Zone Offset)? In your client app (assuming VB, but can be done in most languages), code something like:
Dim tzo As Integer = DateDiff(DateInterval.Hour , DateTime.UtcNow, dateTime.Now)
And pass tzo to SQL Server. That way, you can use GETUTCDate() in SQL Server to store the time, and store tzo. That way, you can ALWAYS get the current local time by adding tzo to the datetime.
Is it possible to add another column to your table (TZO-Time Zone Offset)? In your client app (assuming VB, but can be done in most languages), code something like:
Dim tzo As Integer = DateDiff(DateInterval.Hour
And pass tzo to SQL Server. That way, you can use GETUTCDate() in SQL Server to store the time, and store tzo. That way, you can ALWAYS get the current local time by adding tzo to the datetime.
The good thing about the solution I present is that it will work for ANY time zone the client is in, Eastern, Central, Pacific, Hawaiian, or another country.
Fraser_Admin,
With the possibility of supporting multiple time zones, my simplistic suggestion no longer
merits consideration. The other Experts are on a better track.
Regards,
Patrick
With the possibility of supporting multiple time zones, my simplistic suggestion no longer
merits consideration. The other Experts are on a better track.
Regards,
Patrick
ASKER
yea i will have to put some thought into this. the big thing i was looking for is say you have a user who is in one time zone and a user in another time zone and they are both running the same report. i would like it to show them the current date in their time zone, not the time zone the server is sitting in. maybe i will somehow have to assign time zones to a user and work that way. just thought sqlserver could figure that out for you.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.