When you’re working in several time zones, you need to save date values in a common format?the most obvious one being GMT. The following stored procedures store datetime in GMT and retrieve date in current PC datetime.
/*Convert Datetime to GMTTime FormatAuthor :PPG Dinesh AsankaDate :2003-08-23*/CREATE PROCEDURE [dbo].[Convert_DateTime_to_GMT]@dt_Date_Time as datetimeASselect DATEADD ( hh , (DATEDIFF ( hh , GetDate(), GetUTCDate() )) ,@dt_Date_Time )GO/*Convert GMT to DatetimeTime FormatAuthor :PPG Dinesh AsankaDate :2003-08-23*/CREATE PROCEDURE [dbo].[Convert_GMT_to_DateTime]@dt_GMT as datetimeASselect DATEADD ( hh , (DATEDIFF ( hh , GetUTCDate(),GetDate() )) ,@dt_GMT )GO
Heres a sample:
declare @dt nvarchar(20)Select @dt = cast('2003/10/12 13:12' as datetime)exec Convert_GMT_to_DateTime @dt