Date Functions

One of the most commonly asked questions, I have seen across varying forums and newsgroups pertains to using or calculating date values. The most important thing to remember when working with dates is to not use functions in the predicate. Using functions in the predicate prevents the optimizer from using an index to seek the row. The optimizer cannot seek the row because the function makes the where clause non-SARGable. So what does this fancy lingo mean? Well…. it means the where clause does not have a valid search argument, so the optimizer has to scan the table/index. An example is presented below.

SELECT *
FROM MyTable
WHERE
     MONTH(MyDateCol) = MONTH(GetDate())
     AND YEAR(MyDateCol) = YEAR(GetDate())

The query should be written as below, which has a valid SARGable predicate.

SELECT *
FROM MyTable
WHERE
     MyDateColumn >= DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)
     AND MyDateColumn < DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())+1,0)

The code above tells the database engine to return all rows that have date greater than or equal to the first day of the current month and less than the first day of the next month. You should try to use the >= and < signs when working with dates because time can render your results incorrect. This type of search yields better performance and allows the optimizer to use indices.

Now let’s get to the useful stuff :)

DECLARE @DATE AS DateTime
SET @DATE = GETDATE()

--common date functions
SELECT
   DATEADD(DAY,DATEDIFF(DAY,0,@DATE),0) AS DayStart,
   DATEADD(SECOND,-1,DATEADD(DAY,DATEDIFF(DAY,0,@DATE)+1,0) ) AS DayEnd,
   DATEADD(week,DATEDIFF(week,0,@DATE),0) AS WeekStart,
   DATEADD(SECOND,-1,DATEADD(week,DATEDIFF(week,0,@DATE)+1,0) ) AS WeedEnd,
   DATEADD(MONTH,DATEDIFF(MONTH,0,@DATE),0) AS MonthStart,
   DATEADD(SECOND,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,@DATE)+1,0) ) AS MonthEnd,
   DATEADD(YEAR,DATEDIFF(YEAR,0,@DATE),0) AS YearStart,
   DATEADD(SECOND,-1,DATEADD(YEAR,DATEDIFF(YEAR,0,@DATE)+1,0) ) AS YearEnd ,
   DATEADD(QUARTER,DATEDIFF(QUARTER,0,@DATE),0) AS FirstDayQtr,
   DATEADD(SECOND,-1,DATEADD(QUARTER,DATEDIFF(QUARTER,0,@DATE)+1,0)) AS LastDayOfQuarter

--calculate age
DECLARE @birthdate DATETIME
SET @birthdate='1982-09-22'

SELECT CONVERT(VARCHAR,YEAR(GETDATE()-@birthdate)-1900)+' Years '+
       CONVERT(VARCHAR,MONTH(GETDATE()-@birthdate))+' Months '+
       CONVERT(VARCHAR,DAY(GETDATE()-@birthdate))+' Days'

SELECT 
    DATEDIFF(
        MONTH,
        CASE 
        WHEN DAY(@birthdate) > DAY(GETDATE())
        THEN DATEADD(MONTH,1,@birthdate)
        ELSE @birthdate
        END,
        GETDATE()
    ) AS 'Tot_Months',
    DATEDIFF(
        MONTH,
        CASE 
        WHEN DAY(@birthdate) > DAY(GETDATE())
        THEN DATEADD(MONTH,1,@birthdate)
        ELSE @birthdate
        END,
        GETDATE()
    ) / 12 AS YearsOld    
     
--calculate leap year
DECLARE @year INT
SET @year = 2008

SELECT
CASE
    WHEN ( @year & 3 ) > 0 THEN 0 -- Anything with 1st or 2nd bits set is not a leap year
    WHEN ( @year % 400 ) = 0 THEN 1 -- Anything divisible by 400 is a leap year
    WHEN ( @year % 100 ) = 0 AND ( @year % 400 ) > 0 THEN 0 -- 100-year rule 
    WHEN ( @year % 4 ) = 0 THEN 1 --when divisible by 4 then leap year
ELSE 0 --else not a leap year
END

SELECT 
    CASE
    WHEN DAY(DATEADD(DAY,-1,DATEADD(MONTH,((@year-1900)*12)+2,0))) = 29
    THEN 1
    ELSE 0
    END

So there you go.. You now have the most commonly requested date functions and an example of how to use it.