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.