Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Date Time Values and Time Zones

By Dinesh Asanka,

Introduction

DateTime always gives headaches to the database developers, because of their various combinations. Here is another problem of Datetime. I have given a solution and it is open to discussion. It will be highly appreciated if you can share your ideas to this and your solutions.

What is the Use

Problem arises when your servers are spread across multiple time zones and you are asked to combine all the data in to a main server. Let's take a Hotel System for an example.

Assume a company in Hawaii which owned two hotel's in Nairobi, Kenya   and another one in Kuala Lumpur ,Malaysia. In each location separate SQL Servers are running. All the data need to be transferred to the Main System which is running in Hawaii. Certainly there will be a problem in Datetime if you are saving the data with their respective times.

So we need a solution to identify the actual time. One way of doing this is, keeping the Location with each record. Then we know that we can get the  actual time. But as you can imagine it will be a tedious task. What if we can keep a common time for all of them ? We can keep all the Datetime in Universal Time Coordinate, better known as Greenwich Mean Time.

As far as end users are concerned, it will be difficult them to deal with GMT as they are already use to their own system time. So the option would be to display the system time and convert them to GMT when storing to database as well as convert when reading from the database.

How can we use SQL SERVER?

 GetUTCDate()  is a new function which added to the function family of in SQLServer 2000.This function returns the datetime value representing the current UTC time. The current time is derived from the current local time and the time zone setting in the operating system of the computer on which SQL Sever is running. It must be noted that it is not returning where your Query. Most of the users think it is returning the GMT of the PC where you run the Query.

If you are saving current time (GetDate()) now you have to save the current GMT Time(GetUTCDate())

If you are saving user defined time like reservation time of guests, then you must convert this time to GMT. Following Stored Procedure will convert the current time to GMT.

/* Purpose : Convert DateTime to GMT Time Format
Author  : PPG Dinesh Asanka
Create Date : 2003-08-23
 Version  Date      Modification 
  */
Create PROCEDURE [dbo].[Convert_DateTime_to_GMT]
@dt_Date_Time as datetime
AS
select DATEADD ( hh ,  (DATEDIFF ( hh , GetDate(), GetUTCDate() )) , @dt_Date_Time )
GO

Here is an example to use this function.

Select @dt = cast(‘2003/10/12 13:12’ as datetime)
Exec convert_GMT_to_DateTime @dt

Now the reading of datetime field. It will be a another simple function like above.

/* Purpose : Convert GMT Time Format to System DateTime
Author  : PPG Dinesh Asanka
Create Date : 2003-08-23
 Version  Date      Modification 
*/CREATE PROCEDURE [dbo].[Convert_GMT_to_DateTime]
@dt_GMT as datetime
AS
select DATEADD ( hh ,  (DATEDIFF ( hh , GetUTCDate(),GetDate() )) , @dt_GMT )
GO

I don't think you would need an example for this as it will be same as above.

Comparison with Oracle

I have a habit (Not sure whether it is good or bad) of comparing SQL Server with Oracle once I found any new feature in SQL Server. There are many functions in Oracle with relation to the Time Zones. DBTIMEZONE is function equalent of Oracle 9i to the SQL Server GetUTCDate(). SYS_EXTRACT_UTC returns the UTC time for a given time. TZ_OFFSET returns the offset from UTC for a given time Zone name. There is a fantastic function in Oracle, called NEW_TIME, which takes three arguments, which converts datetime of one zone to another and more importantly there are 18 defined time zones. Some of them are Atlantic Standard Time, Eastern Standard Time, Pacific Standard Time, Yukon Standard Time and many others! Therefore we don't have to convert system time to GMT.

Conclusion

Next version of SQL Server has to cater more functions for the Time Zone. Then SQL Server will be more user friendly as far as end users and DBAs are concerned and will make DBA's job much easier.

Total article views: 14677 | Views in the last 30 days: 6
 
Related Articles
FORUM

convert DateTime to BigInt

convert DateTime to BigInt

FORUM

Convert DateTime to MMDDYY

Convert DateTime to MMDDYY

FORUM

Convertion of integer to Datetime format

Convertion of integer to Datetime format

FORUM

Help with converting varchar to datetime

Convert varchar to datetime

FORUM

how to convert datetime excel sheet to sqlserver datetime

converting datetime from excel sheet to sql server

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones