SQL Date Handling
Some tips for date handling with SQL Server
Creating Transact SQL queries that work with dates is not difficult but can be oblique. The information presented here is in the SQL Server documentation in greater detail but is spread among several topics. It’s my hope to present a brief but comprehensive overview of performing some common operations that involve dates.
List of Tips
- Difference between datetime and smalldatetime.
- Obtaining the current server time.
- Convert a datetime variable to a varchar.
- Using the datepart, dateadd and datediff functions.
- Find the week-beginning or week-ending of a given date.
- Find the month-beginning or month-ending of a given date.
- Find which week of the month a given date falls in.
- Account for missing dates when querying for by-date trends
-
The SMALLDATETIME data type is smaller and less precise than the DATETIME data type. The DATETIME data type
requires 8 bytes of storage, can be used to store dates between January 1, 1753 and January 1, 9999 and has
accuracy to the millisecond. In contrast, the SMALLDATETIME data type can store dates between January 1, 1900
and January 1, 2079, and has accuracy to the minute.
In short, the SMALLDATETIME data type is adequate for most purposes unless a high degree of accuracy is necessary. -
Obtaining the current server time.
It is often convenient to know the current time on the server and the CURRENT_TIMESTAMP built-in function (the GETDATE()) function is equivilant) returns a DATETIME value with the current system time on the server.
The CURRENT_TIMESTAMP function can be used as a default value for table columns, directly in queries to find dates older or newer than the present, in functions or selected into variables. -
Convert a datetime variable to a varchar.
The CONVERT function can be used to convert a SMALLDATETIME or DATETIME value to a CHAR or VARCHAR value. The CONVERT function takes the following form, where datatype is the type into which the DATETIME should be converted (usually VARCHAR), expression is the DATETIME, and style is an optional numeric value from the table below:CONVERT(datatype,expression[,style]) Converting dates can be a convient way to normalize them. Use style 101 to chop off the time portion then convert the date back to SMALLDATETIME if you wish to aggregate rows by day. style Output – or 0 or 100 Jan 15 2007 10:08PM 1 01/15/07 101 01/15/2007 2 07.01.15 102 2007.01.15 3 15/01/07 103 15/01/2007 4 15.01.07 104 15.01.2007 5 15-01-07 105 15-01-2007 6 15 Jan 07 106 15 Jan 2007 7 Jan 15, 07 107 Jan 15, 2007 8 or 24 or 108 22:08:00 9 or 109 Jan 15 2007 10:08:00:000PM 10 01-15-07 110 01-15-2007 11 07/01/15 111 2007/01/15 12 070115 112 20070115 13 15 Jan 2007 22:08:00:000 14 22:08:00:000 20 2007-01-15 22:08:00 21 or 25 2007-01-15 22:08:00.000 21 or 22 01/15/07 10:08:00 PM 121 01/15/07 10:08:00 PM 23 2007-01-15 126 2007-01-15T22:08:00 130 26 ?? ????? 1427 10:08:00:000PM 131 26/12/1427 10:08:00:000PM -
Using the datepart, dateadd and datediff functions.
The DATEPART, DATEADD and DATEDIFF functions are useful functions for working with dates or parts of dates.
The DATEPART function returns the value of the specified datepart from a date. Use this function to retrieve the month, hour, weekday, etc. from a given date.
DATEPART(datepart,date)
The DATEADD function returns a new datetime based on adding the specified count of the specified interval to a date.
DATEADD(datepart,number,date)
The DATEDIFF function the count of dateparts between startdate and enddate.
DATEDIFF(datepart,startdate,enddate)
All three functions accept the following datepart values
DatePart Abbreviation year yy or yyyy quarter qq or q month mm or m dayofyear dy or y day dd or d week wk or ww weekday dw hour hh minute mi, n second ss, s millisecond ms -
Find the week-beginning or week-ending of a given date.
This is a useful user defined function that returns the sunday of the week into which a given date belongs. This function is a convienient way to group values by week and is less complex than using DATEPART, DATEADD and the week datepart.
create function dbo.WeekBeginning(@CurDate datetime) returns datetime as begin return convert( smalldatetime, convert(varchar(25), dateadd(day,1-datepart(weekday,@CurDate),@CurDate),107)) end
This is the same function modified to return the last day of the week (Saturday) instead of the first.
create function dbo.WeekEnding(@CurDate datetime) returns datetime as begin return convert( smalldatetime, convert(varchar(25), dateadd(day,1-datepart(weekday,@CurDate),@CurDate)+6,107)) end -
Find the month-beginning or month-ending of a given date.
These simple functions return the first and last day of the month into which a given date falls.
To find the first day of the month, we determine what the current day of month is, and subtract that number of days minus one from the current date. The convert function is used to trim off any time element associated with the parameter date.
CREATE function dbo.BeginningOfMonth(@dtDate datetime) returns datetime as begin return convert(datetime,convert(varchar(30),dateadd(day,-1 * (datepart(day,@dtDate)-1),@dtDate),1)) end
To find the last day of the month, we use the BeginningOfMonth function above, then add one month.
CREATE function dbo.EndOfMonth(@dtDate datetime) returns datetime as begin return dateadd(second,-1,dateadd(month,1,dbo.BeginningOfMonth(@dtDate))) end -
Find which week of the month a given date falls in.
The WeekOfMonth function returns the ordinal week of month (1-5) that a given date falls in to. Again, the BeginningOfMonth function above is used to determine the beginning of month. One is added to the result so that the first week in the month will be “1” rather than “0”.
CREATE function dbo.WeekOfMonth(@dtDate datetime) returns int as begin return datediff(week,dbo.BeginningOfMonth(@dtDate),@dtDate)+1 end -
Account for missing dates when querying for by-date trends
A problem that often presents itself when creating charts based on data summerized by date is the issue of missing dates. For example, a chart of customers by day at a ski area where the ski area may not be open every day at the beginning or ending of the season should include those days when there are no customers because the ski area is closed. Otherwise the chart might appear to show an overly optimistic trend.
One way that I have developed to work around this problem is to use a table variable into which I have inserted all valid dates for the range to be displayed. The data is then joined against this table assuring that there will be at least one row per date segment even if there is no corresponding data.
Here is an example of the technique in use.
declare @CountOfWeeksToShow int set @CountOfWeeksToShow=20 ---Retrieve data into temporary table. select datepart(ww,a.[CreateDate]) 'iWeek', datepart(year,a.[CreateDate]) 'iYear', count(a.[Visitors]) ) 'Count_Of_Visitors' into #x from VisitHistory a where datediff(week,a.[CreateDate],current_timestamp)<@CountOfWeeksToShow group by datepart(ww,a.[CreateDate]), datepart(year,a.[CreateDate]) order by datepart(year,a.[CreateDate]), datepart(ww,a.[CreateDate]) ---Create table variable to contain all dates for given date period. declare @weeks table(dtWeekDate datetime,iWeek int,iYear int) declare @iCurWeek int, @dtCurDate datetime set @iCurWeek=0 while @iCurWeek<@CountOfWeeksToShow begin set @dtCurDate=dateadd(ww,@iCurWeek * -1,current_timestamp) ---See above for definition of UDF WeekBeginning insert into @weeks(dtWeekDate,iWeek,iYear) values( WeekBeginning(@dtCurDate), datepart(ww,@dtCurDate), datepart(year,@dtCurDate)) set @iCurWeek=@iCurWeek+1 end ---Use join to return results select a.dtWeekDate, b.[Count_Of_Visitors] from @weeks a left outer join #x b on b.[iYear]=a.[iYear] and b.[iWeek]=a.[iWeek] order by a.dtWeekDate drop table #x