Calendar Table and Date/Time Functions

Mon Jul 19, 2010 by Dan Guzman

I frequently see questions in the forums and newsgroups about how to best query date/time data and perform date manipulation. Let me first say that a permanent calendar table that materializes commonly used DATEPART values along with time periods you frequently use is invaluable. I’ve used such a table for over a decade with great success and strongly recommend you implement one on all of your database servers. I’ve included a sample calendar table (and numbers table) later in this post and you can find other variations of such a table via an internet search.

Removing the Time Portion

A common requirement I have is to remove the time portion from a date/time value. This is easy in SQL 2008 since you can simply “CAST(SomeDateTimeValue AS date)”. But the date data type is not available in older SQL Server versions so you need an alternate method. In SQL 2005 and earlier versions, I recommend the DATEADD…DATEDIFF method below with an arbitrary base date value specified in a format that is independent of the session DATAFORMAT setting:

SELECT CAST(GETDATE() AS date); --SQL 2008 and later

SELECT DATEADD(day, DATEDIFF(day, '19000101', GETDATE()), '19000101'); --SQL 2005 and earlier

I often see a variation of the DATEADD…DATEDIFF technique with the integer zero (no quotes) specified as the base date. Although this may provide the expected results (I’ve done it myself), I caution against it because it relies on implicit conversion from the internal SQL Server integer date/time storage format. If you want to be concise, a better approach is to specify an empty string for the base date value since the default value is ‘1900-01-01 00:00:00’. In my opinion, an explicit data value is more intuitive, though.

SELECT DATEADD(day, DATEDIFF(day, '', GETDATE()), '');

I also sometimes see code that extracts the year, month and day date parts and concatenates with separators. However, that method is dependent on session DATEFORMAT settings and slower than other methods. See Tibor Karaszi’s The ultimate guide to the datetime datatypes article for details.

First and Last Day of Period

Another common task is to determine the first or last day of a given period. The script below shows how to accomplish this of you don’t have a calendar table with the calculated values available.

DECLARE @Date date = GETDATE();

SELECT 'First day of year' [DateDescription], DATEADD(year, DATEDIFF(year,'19000101',@Date), '19000101') AS [CalendarDate]

UNION ALL

SELECT 'Last day of year', DATEADD(day,-1,DATEADD(year,0,DATEADD(year,DATEDIFF(year,'19000101',@Date)+1,'19000101')))

UNION ALL

SELECT 'First day of month', DATEADD(month, DATEDIFF(month,'19000101',@Date), '19000101')

UNION ALL

SELECT 'Last day of month', DATEADD(day,-1,DATEADD(month,0,DATEADD(month,DATEDIFF(month,'19000101',@Date)+1,'19000101')))

UNION ALL

SELECT 'First day week (based on DATEFIRST setting)', DATEADD(day,-(DATEPART(weekday ,@Date)-1),DATEDIFF(day,'19000101', @Date))

UNION ALL

SELECT 'Last day of week (based on DATEFIRST setting)', DATEADD(day,-(DATEPART(weekday ,@Date)-1)+6,DATEDIFF(day,'19000101', @Date));

With a calendar table like the one later in this post:

DECLARE @Date date = GETDATE();

SELECT 'First day of year' [DateDescription], (SELECT FirstDateOfYear FROM dbo.Calendar WHERE CalendarDate = @Date)

UNION ALL

SELECT 'Last day of year', (SELECT LastDateOfYear FROM dbo.Calendar WHERE CalendarDate = @Date)

UNION ALL

SELECT 'First day of month', (SELECT FirstDateOfMonth FROM dbo.Calendar WHERE CalendarDate = @Date)

UNION ALL

SELECT 'Last day of month', (SELECT LastDateOfMonth FROM dbo.Calendar WHERE CalendarDate = @Date)

UNION ALL

SELECT 'First day week (based on DATEFIRST setting)', (SELECT FirstDateOfWeek FROM dbo.Calendar WHERE CalendarDate = @Date)

UNION ALL

SELECT 'Last day of week (based on DATEFIRST setting)', (SELECT LastDateOfWeek FROM dbo.Calendar WHERE CalendarDate = @Date);

Calendar and Numbers Table

I think auxiliary calendar and number tables are a must-have on every database server. These objects allow you to easily perform set-based processing in a number of scenarios. In fact, the calendar table population script below uses a numbers table to populate the calendar table with several thousand rows in under a second. This is much more efficient that a WHILE loop.

This calendar table population script also updates the table with most US holidays and adjusts business/non-business days accordingly. In addition to customizing the script for holidays as observed by your organization, you might add fiscal period start/end dates to facilitate querying based on those cycles. Also consider creating user-defined functions or stored procedures to encapsulate frequently used code that uses the calendar table. For example, here is a function that returns the date that is a specified number of business days from the date provided:

CREATE FUNCTION dbo.udf_AddBusinessDays

(@Date date, @BusinessDays int)

RETURNS date

BEGIN

RETURN (

SELECT TOP (1) CalendarDate AS BusinessDate

FROM (SELECT TOP (@BusinessDays) CalendarDate

FROM dbo.Calendar

WHERE

CalendarDate > @Date

AND BusinessDay = 1

ORDER BY CalendarDate) AS BusinessDays

ORDER BY CalendarDate DESC

)

END

Script 1: Example calendar table utility function

--auxiliary number table

CREATE TABLE dbo.Numbers(

Number int NOT NULL

CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED

);

--load Numbers table with 1,000,000 numbers

WITH t1 AS (SELECT 0 AS n UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0)

,t2 AS (SELECT 0 AS n FROM t1 t1a, t1 t1b, t1 t1c, t1 t1d)

,t3 AS (SELECT 0 AS n FROM t2 t2a, t2 t2b, t2 t2c)

,numbers AS (SELECT ROW_NUMBER() OVER(ORDER BY n) - 1 AS number FROM t3)

INSERT INTO dbo.Numbers WITH (TABLOCKX) (

Number

)

SELECT number

FROM numbers

WHERE number < 1000000;

Script 2: Create and populate numbers table

CREATE TABLE dbo.Calendar(
CalendarDate date NOT NULL
CONSTRAINT PK_Calendar PRIMARY KEY CLUSTERED
,CalendarYear int NOT NULL
,CalendarMonth int NOT NULL
,CalendarDay int NOT NULL
,DayOfWeekName varchar(10) NOT NULL
,FirstDateOfWeek date NOT NULL
,LastDateOfWeek date NOT NULL
,FirstDateOfMonth date NOT NULL
,LastDateOfMonth date NOT NULL
,FirstDateOfQuarter date NOT NULL
,LastDateOfQuarter date NOT NULL
,FirstDateOfYear date NOT NULL
,LastDateOfYear date NOT NULL
,BusinessDay bit NOT NULL
,NonBusinessDay bit NOT NULL
,Weekend bit NOT NULL
,Holiday bit NOT NULL
,Weekday bit NOT NULL
,CalendarDateDescription varchar(50) NULL
);
GO

--load dates from 2000-01-01 through 2099-12-31
WITH t1 AS (SELECT 0 AS n UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0)
,t2 AS (SELECT 0 AS n FROM t1 t1a, t1 t1b, t1 t1c, t1 t1d)
,t3 AS (SELECT 0 AS n FROM t2 t2a, t2 t2b)
,numbers AS (SELECT ROW_NUMBER() OVER(ORDER BY n) - 1 AS number FROM t3)
INSERT INTO dbo.Calendar WITH (TABLOCKX) (
CalendarDate
,CalendarYear
,CalendarMonth
,CalendarDay
,DayOfWeekName
,FirstDateOfWeek
,LastDateOfWeek
,FirstDateOfMonth
,LastDateOfMonth
,FirstDateOfQuarter
,LastDateOfQuarter
,FirstDateOfYear
,LastDateOfYear
,BusinessDay
,NonBusinessDay
,Weekend
,Holiday
,Weekday
,CalendarDateDescription
)
SELECT
CalendarDate = DATEADD(day, number, '20000101')
,CalendarYear = DATEPART(year, DATEADD(day, number, '20000101'))
,CalendarMonth = DATEPART(month, DATEADD(day, number, '20000101'))
,CalendarDay = DATEPART(day, DATEADD(day, number, '20000101'))
,DayOfWeekName = DATENAME(weekday, DATEADD(day, number, '20000101'))
,FirstDateOfWeek = DATEADD(day,-(DATEPART(weekday ,DATEADD(day, number, '20000101'))-1),DATEADD(day, number, '20000101'))
,LastDateOfWeek = DATEADD(day,-(DATEPART(weekday ,DATEADD(day, number, '20000101'))-1)+6,DATEADD(day, number, '20000101'))
,FirstDateOfMonth = DATEADD(month, DATEDIFF(month,'20000101',DATEADD(day, number, '20000101')), '20000101')
,LastDateOfMonth = DATEADD(day,-1,DATEADD(month,0,DATEADD(month,DATEDIFF(month,'20000101',DATEADD(day, number, '20000101'))+1,'20000101')))
,FirstDateOfQuarter = DATEADD(quarter, DATEDIFF(quarter,'20000101',DATEADD(day, number, '20000101')), '20000101')
,LastDateOfQuarter = DATEADD(day, -1, DATEADD(quarter, DATEDIFF(quarter,'20000101',DATEADD(day, number, '20000101'))+1, '20000101'))
,FirstDateOfYear = DATEADD(year, DATEDIFF(year,'20000101',DATEADD(day, number, '20000101')), '20000101')
,LastDateOfYear = DATEADD(day,-1,DATEADD(year, DATEDIFF(year,'20000101',DATEADD(day, number, '20000101'))+1, '20000101'))
--initially set all weekdays are business days
,BusinessDay = CASE WHEN DATENAME(weekday, DATEADD(day, number, '20000101')) IN('Monday','Tuesday','Wednesday','Thursday','Friday') THEN 1 ELSE 0 END
--all weekends are non-business days
,NonBusinessDay = CASE WHEN DATENAME(weekday, DATEADD(day, number, '20000101')) IN('Saturday','Sunday') THEN 1 ELSE 0 END
,Weekend = CASE WHEN DATENAME(weekday, DATEADD(day, number, '20000101')) IN('Saturday','Sunday') THEN 1 ELSE 0 END
,Holiday = 0 --initially no holidays
,Weekday = CASE WHEN DATENAME(weekday, DATEADD(day, number, '20000101')) IN('Monday','Tuesday','Wednesday','Thursday','Friday') THEN 1 ELSE 0 END
,CalendarDateDescription = NULL
FROM numbers
WHERE number < DATEDIFF(day, '20000101', '20991231') + 1;

--New Year's Day
UPDATE dbo.calendar
SET
BusinessDay = 0
,NonBusinessDay = 1
,Holiday = 1
    ,CalendarDateDescription = 'New Year''s Day'
WHERE
    CalendarMonth = 1
    AND CalendarDay = 1;

--New Year's Day celebrated on Friday, December 31 when January 1 falls on Saturday
UPDATE dbo.Calendar
SET
BusinessDay = 0
,NonBusinessDay = 1
    ,CalendarDateDescription = 'New Year''s Day Celebrated'
WHERE
    CalendarMonth = 12
    AND CalendarDay = 31
    AND DayOfWeekName = 'Friday';

--New Year's Day celebrated on Monday, January 2 when January 1 falls on Sunday
UPDATE dbo.Calendar
SET
BusinessDay = 0
,NonBusinessDay = 1
    ,CalendarDateDescription = 'New Year''s Day Celebrated'
WHERE
    CalendarMonth = 1
    AND CalendarDay = 2
    AND DayOfWeekName = 'Monday';

--Martin Luther King Day - 3rd Monday in January
UPDATE dbo.Calendar
SET
BusinessDay = 0
,NonBusinessDay = 1
,Holiday = 1
    ,CalendarDateDescription = 'Martin Luther King Day'
WHERE
    CalendarMonth = 1
    AND DayOfWeekName = 'Monday'
    AND (SELECT COUNT(*)
  FROM dbo.Calendar c2
        WHERE
            c2.CalendarDate <= Calendar.CalendarDate
            AND c2.CalendarYear = Calendar.CalendarYear
            AND c2.CalendarMonth = Calendar.CalendarMonth
            AND c2.DayOfWeekName = 'Monday'
        ) = 3;

--President's Day - 3rd Monday in February
UPDATE dbo.Calendar
SET
BusinessDay = 0
,NonBusinessDay = 1
,Holiday = 1
    ,CalendarDateDescription = 'President''s Day'
WHERE
    CalendarMonth = 2
    AND DayOfWeekName = 'Monday'
    AND (SELECT COUNT(*)
  FROM dbo.Calendar c2
        WHERE
            c2.CalendarDate <= Calendar.CalendarDate
            AND c2.CalendarYear = Calendar.CalendarYear
            AND c2.CalendarMonth = Calendar.CalendarMonth
            AND c2.DayOfWeekName = 'Monday'
        ) = 3;

--Easter - first Sunday after the full moon following the vernal (March 21) equinox
WITH
t4 AS (SELECT 0 AS n UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0)
    ,t256 AS (SELECT 0 AS n FROM t4 t4a, t4 t4b, t4 t4c)
    ,years AS (SELECT ROW_NUMBER() OVER(ORDER BY n) + 1999 AS year FROM t256)
,n AS (SELECT years.year, years.year - (19 * (years.year / 19)) AS n FROM years)
,century AS (SELECT years.year, years.year / 100 AS century FROM years)
,i AS (SELECT century.year, century.century - (century.century / 4) - ((century.century - ((century.century - 17) / 25)) / 3) + (19 * n.n) + 15 AS i
  FROM century
  JOIN n ON n.year = century.year)
,i2 AS (SELECT i.year, i.i - (30 * (i.i / 30 ) ) AS i2
  FROM i)
,i3 AS (SELECT i2.year, i2.i2 - ((i2.i2 / 28) * (1 - (i2.i2 / 28) * (29 / (i2.i2 + 1)) * ((21 - n.n) / 11)) ) AS i3
  FROM i2
  JOIN n ON n.year = i2.year)
,j AS (SELECT i3.year, i3.year + (i3.year / 4) + i3.i3 + 2 - century.century + (century.century / 4 ) AS j
  FROM i3
  JOIN century ON century.year = i3.year)
,j2 AS (SELECT j.year, j.j - (7 * (j.j / 7) ) AS j2
  FROM j)
,month AS (SELECT j2.year, 3 + (((i3.i3 - j2.j2) + 40) / 44 ) AS month
  FROM j2
  JOIN i3 ON i3.year = j2.year)
,day AS (SELECT month.year, month.month, i3.i3 - j2.j2 + 28 - (31 * ( month.month / 4 ) ) AS day
  FROM i3
  JOIN j2 ON j2.year = i3.year
  JOIN month ON month.year = j2.year)
,easter AS (SELECT CAST(DATEADD(year, month.year-1900, DATEADD(day, day.day-1, DATEADD(month, month.month-1, ''))) AS date) AS easter
  FROM month
  JOIN day ON day.month = month.month AND day.year = month.year)
UPDATE dbo.Calendar
SET
Holiday = 1
    ,CalendarDateDescription = 'Easter'
WHERE
    CalendarDate IN(
  SELECT easter
  FROM easter
  );

--Good Friday - 2 days before Easter Sunday
UPDATE dbo.Calendar
SET
BusinessDay = 0
,NonBusinessDay = 1
,Holiday = 1
    ,CalendarDateDescription = 'Good Friday'
WHERE
    CalendarDate IN(
        SELECT DATEADD(day, -2, c2.CalendarDate)
        FROM dbo.Calendar c2
        WHERE c2.CalendarDateDescription = 'Easter'
        );

--Memorial Day - last Monday in May
UPDATE dbo.Calendar
SET
BusinessDay = 0
,NonBusinessDay = 1
,Holiday = 1
    ,CalendarDateDescription = 'Memorial Day'
WHERE
    CalendarMonth = 5
    AND DayOfWeekName = 'Monday'
    AND CalendarDate IN(
        SELECT MAX(c2.CalendarDate)
        FROM dbo.Calendar c2
        WHERE
            c2.CalendarYear = Calendar.CalendarYear
            AND c2.CalendarMonth = 5
            AND c2.DayOfWeekName = 'Monday'
        );

--Independence Day - July 4th
UPDATE dbo.Calendar
SET
BusinessDay = 0
,NonBusinessDay = 1
,Holiday = 1
    ,CalendarDateDescription = 'Independence Day'
WHERE
    CalendarMonth = 7
    AND CalendarDay = 4;

--Independence Day celebrated on Friday, July 3 when July 4 falls on a Saturday
UPDATE dbo.Calendar
SET
BusinessDay = 0
,NonBusinessDay = 1
    ,CalendarDateDescription = 'Independence Day Celebrated'
WHERE
    CalendarMonth = 7
    AND CalendarDay = 5
    AND DayOfWeekName = 'Monday';

--Labor Day - first Monday in September
UPDATE dbo.Calendar
SET
BusinessDay = 0
,NonBusinessDay = 1
,Holiday = 1
    ,CalendarDateDescription = 'Labor Day'
WHERE
    CalendarMonth = 9
    AND DayOfWeekName = 'Monday'
    AND CalendarDate IN(
        SELECT MIN(c2.CalendarDate)
        FROM dbo.Calendar c2
        WHERE
            c2.CalendarYear = calendar.CalendarYear
            AND c2.CalendarMonth = 9
            AND c2.DayOfWeekName = 'Monday'
        );

--Columbus Day - second Monday in October
UPDATE dbo.Calendar
SET
Holiday = 1
    ,CalendarDateDescription = 'Columbus Day'
WHERE
    CalendarMonth = 10
    AND DayOfWeekName = 'Monday'
    AND (SELECT COUNT(*)
  FROM dbo.Calendar c2
        WHERE
            c2.CalendarDate <= Calendar.CalendarDate
            AND c2.CalendarYear = Calendar.CalendarYear
            AND c2.CalendarMonth = Calendar.CalendarMonth
            AND c2.DayOfWeekName = 'Monday'
        ) = 2;

--Veteran's Day - November 11
UPDATE dbo.Calendar
SET
Holiday = 1
    ,CalendarDateDescription = 'Veteran''s Day'
WHERE
    CalendarMonth = 11
    AND CalendarDay = 11;

--Thanksgiving - fourth Thursday in November
UPDATE dbo.Calendar
SET
BusinessDay = 0
,NonBusinessDay = 1
,Holiday = 1
    ,CalendarDateDescription = 'Thanksgiving'
WHERE
    CalendarMonth = 11
    AND DayOfWeekName = 'Thursday'

    AND (SELECT COUNT(*) FROM
  dbo.Calendar c2
        WHERE
            c2.CalendarDate <= Calendar.CalendarDate
            AND c2.CalendarYear = Calendar.CalendarYear
            AND c2.CalendarMonth = Calendar.CalendarMonth
            AND c2.DayOfWeekName = 'Thursday'
        ) = 4;

UPDATE dbo.Calendar
SET
BusinessDay = 0
,NonBusinessDay = 1
,Holiday = 1
    ,CalendarDateDescription = 'Day after Thanksgiving'
WHERE
    CalendarDate IN(
        SELECT DATEADD(day, 1, c2.CalendarDate)
        FROM dbo.Calendar c2
        WHERE c2.CalendarDateDescription = 'Thanksgiving'
        );

--Christmas Day - December 25th
UPDATE dbo.Calendar
SET
BusinessDay = 0
,NonBusinessDay = 1
,Holiday = 1
    ,CalendarDateDescription = 'Christmas Day'
WHERE
    CalendarMonth = 12
    AND CalendarDay = 25;

--Christmas day celebrated on Friday, December 24 when December 25 falls on a Saturday
UPDATE dbo.Calendar
SET
BusinessDay = 0
,NonBusinessDay = 1
    ,CalendarDateDescription = 'Christmas Day Celebrated'
WHERE
    CalendarMonth = 12
    AND CalendarDay = 24
    AND DayOfWeekName = 'Friday';

--Christmas day celebrated on Monday, December 24 when December 25 falls on a Sunday
UPDATE dbo.Calendar
SET
BusinessDay = 0
,NonBusinessDay = 1
    ,CalendarDateDescription = 'Christmas Day Celebrated'
WHERE
    CalendarMonth = 12
    AND CalendarDay = 26
    AND DayOfWeekName = 'Monday';

Script 3: Create and populate calendar table and update with holidays

Legacy Comments


MrDee 2010-07-20	re: Calendar Table and Date/Time Functions Brilliant - Thank you! The 'Removing the Time Portion' solution is sheer elegance.

Chirag Shah 2010-09-09	re: Calendar Table and Date/Time Functions Dan, this is an excellent post on the subject. If you could elaborate various use of numbers table to solve some T-SQL problems (may be on a next blog post) Thanks!

Ramjet 2012-06-30	re: Calendar Table and Date/Time Functions Good Morning Mr. Guzman, I've reference your calendar script before (It's a favorite) but it used to have 5-10 (don't remember exactly) common functions listed as well. Am I remembering incorrectly? JB

guzmanda 2012-06-30	re: Calendar Table and Date/Time Functions I included only a few sample queries in my post so you might be thinking of another site or article. If you need help developing other ones you need, let me know your requirements and I'll try to help.

jamin 2012-08-24	re: Calendar Table and Date/Time Functions great stuff - very helpful!