Date and Time as Separated Types

INT

• Download SQL Server Scripts v1 - 2.68 KB
• Download SQL Server Scripts v2 - 2.56 KB

Introduction  

Many times I find myself need to use the date and time as separate types, and because SQL Server 2000 and SQL Server 2005 does not have this option so i have to find a way, the way i found is the focus of this article. 

I've made some changes to old scripts for performance issues, the new version you can download under SQL Server Scripts v2

Background 

Datetime is stored in 8 bytes and internally it represented as follow:

First 4 bytes used for date and the date unit is a day and the base date is 1-1-1900 

Next 4 bytes used for time and the time unit is 300 per second and the base time is zero and represents 12:00 AM 

Now we'll do the separation using datetime functions.

Using the code   

Now we will start by coding the function DateTimeToDate which returns the date in integer from

CREATE FUNCTION dbo.DateTimeToDate
(@datetime DATETIME)
RETURNS INT
AS
BEGIN
	DECLARE @date INT
	
	SET @date = 0
	
	IF @datetime IS NOT NULL
		SET @date = DATEDIFF(DAY, 0, @datetime)

	RETURN @date
END

All we do here is to get number of days in passed date since 1-1-1900

Next function is DateTimeToTime which returns the number of seconds since midnight

CREATE FUNCTION dbo.DateTimeToTime
(@datetime DATETIME)
RETURNS INT
AS
BEGIN
	DECLARE @time INT
	
	SET @time = 0
	
	IF @datetime IS NOT NULL
		SET @time = CONVERT(int, CONVERT(BINARY(8), @datetime)) / 300

	RETURN @time
END

as you see we divide the result time by 300 to get number of seconds for passed time. 

Next function is DateToDateTime which take only date and returns datetime that represent that date

CREATE FUNCTION dbo.DateToDateTime
(@date INT)
RETURNS DATETIME
AS
BEGIN
	RETURN DATEADD(DAY, ISNULL(@date, 0), 0)
END

since the date we use is the number of days since 1-1-1900 so all we do to get a datetime instance is to add that number of days to 1-1-1900 to get your date again in datetime data type. 

you may notice that we add passed number of days to zero and thats right since 1-1-1900 is the base date; we can refer to it as 0 rather than the string version '1-1-1900'

Next function is TimeToDateTime which store time back in a datetime format

CREATE FUNCTION dbo.TimeToDateTime
(@time INT, @date INT)
RETURNS DATETIME
AS
BEGIN
	RETURN DATEADD(second, ISNULL(@time, 0), ISNULL(dbo.DateToDateTime(@date), 0))
END

as you see this function takes 2 arguments and that because datetime data type did not allows you to have a time without a date to to return the time in datetime format you must specify a date or you can pass null to use a the base date.

for now every thing is fine and you can use the value returned from above functions to do whatever you want and you can use the integer values to do any compare you want to, however there is something is missing here at least for me and it is the formatting.

Formatting datetime in SQL Server for many of us is a pain, so how we going to format those types as they are separated, actually i made 2 more function and they are DateToString and TimeToString respectively and all they do is formatting the date and the time without having to cast them to any other format, the 2 functions are: (they code in the attachment)

CREATE FUNCTION dbo.DateToString
(@date INT, @split CHAR, @format VARCHAR(3))
RETURNS VARCHAR(10)
		
CREATE FUNCTION [dbo].[TimeToString]
(@time INT, @use24 BIT, @showAM_PM BIT)
RETURNS VARCHAR(12)	

for function DateToString
     @date: is the date returned from function DateTimeToDate, if null nothing returned.
     @split: single character used to split day from month from year, if null the default character is '-'.
     @format: is the way which date will arranged, use D for day, M for month and Y for year for example 'MDY' will result month-day-year, if null the default is 'DMY'.

for function TimeToString
     @time: is the time returned from function DateTimeToTime, if null nothing returned.
     @use24: if 1 the returned format is 24 hour clock and next parameter is ignored, if 0 the returned format is 12 hour clock.
     @showAM_PM: if 1 the 'AM' or 'PM' will appear at the end of the time, if 0 this mark will not appeared.

Points of Interest

Using integer data type to represent the date and time separately will give you more control and more options to program your own scripts, you can use the integer number produced from DateTimeToDate and DateTimeToTime as you use the datetime data type, for example if the date 2/15/2010 is older than 1/1/2000 the result integer for first date will be greater than the number represents last date and the same with time, if the time 11:00 AM is more early than 3:00 PM the result integer for first time will be smaller comparing to last time.