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
1) Difference between datetime and smalldatetime.
2) Obtaining the current server time.
3) Convert a datetime variable to a varchar.
4) Using the datepart, dateadd and datediff functions.
5) Find the week-beginning or week-ending of a given date.
6) Find the month-beginning or month-ending of a given date.
7) Find which week of the month a given date falls in.
6) Account for missing dates when querying for by-date trends

1) Difference between datetime and smalldatetime.

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.

2) 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.

3) 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

4) 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

5) 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

6) 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

7) 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

8) 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