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
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
DECLARE @date INT
SET @date = 0
IF @datetime IS NOT NULL
SET @date = DATEDIFF(DAY, 0, @datetime)
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
CREATE FUNCTION dbo.DateTimeToTime
DECLARE @time INT
SET @time = 0
IF @datetime IS NOT NULL
SET @time = CONVERT(int, CONVERT(BINARY(8), @datetime)) / 300
as you see we divide the result time by 300 to get number of seconds for
Next function is DateToDateTime which take only date and returns datetime
that represent that date
CREATE FUNCTION dbo.DateToDateTime
RETURN DATEADD(DAY, ISNULL(@date, 0), 0)
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
CREATE FUNCTION dbo.TimeToDateTime
(@time INT, @date INT)
RETURN DATEADD(second, ISNULL(@time, 0), ISNULL(dbo.DateToDateTime(@date), 0))
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
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))
CREATE FUNCTION [dbo].[TimeToString]
(@time INT, @use24 BIT, @showAM_PM BIT)
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.