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.
  8. Account for missing dates when querying for by-date trends
  1. 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