Skip to content

Instantly share code, notes, and snippets.

@quickstep25
Created August 22, 2026 22:32
Show Gist options
  • Select an option

  • Save quickstep25/842e2d008cdfa05f733f1ace43bf3288 to your computer and use it in GitHub Desktop.

Select an option

Save quickstep25/842e2d008cdfa05f733f1ace43bf3288 to your computer and use it in GitHub Desktop.
SQL Calendar / Date Table
DECLARE @dtStart DATE
DECLARE @dtEnd DATE
SET @dtStart = '1990-01-01'
SET @dtEnd = '2026-12-31'
;WITH SERIES (NUM) AS
(
SELECT 0
UNION ALL
SELECT NUM + 1
FROM SERIES
WHERE NUM < DATEDIFF(DAY, @dtStart, @dtEnd)
),
DATES (DATES) AS
(
SELECT DATEADD(DAY, NUM, @dtStart) FROM SERIES
),
BASE AS
(
SELECT
DATE = CONVERT(DATE, DATES)
,DAY = DATEPART(DAY, DATES)
,DAY_NAME = DATENAME(WEEKDAY, DATES)
,WEEK = DATEPART(WEEK, DATES)
,WEEK_ISO = DATEPART(ISO_WEEK, DATES)
,WEEK_DAY = DATEPART(WEEKDAY, DATES)
,[MONTH] = DATEPART(MONTH, DATES)
,MONTH_NAME = DATENAME(MONTH, DATES)
,MO_FIRST = DATEFROMPARTS(YEAR(DATES), MONTH(DATES), 1)
,MO_LAST = EOMONTH(DATES)
,QUARTER = DATEPART(QUARTER, DATES)
,[YEAR] = DATEPART(YEAR, DATES)
,YR_FIRST = DATEFROMPARTS(YEAR(DATES), 1, 1)
,YR_LAST = DATEFROMPARTS(YEAR(DATES), 12, 31)
,YR_DAY = DATEPART(DAYOFYEAR, DATES)
FROM DATES
),
CORE AS
(
SELECT
DATE
,[DAY]
,DAY_NAME
,DAY_SUFFIX = CONCAT(DAY, '', CONVERT(CHAR(2),
CASE
WHEN [DAY] / 10 = 1 THEN 'th'
ELSE
CASE RIGHT(DAY, 1)
WHEN '1' THEN 'st'
WHEN '2' THEN 'nd'
WHEN '3' THEN 'rd'
ELSE 'th'
END
END
))
,WEEK
,WEEK_ISO
,WEEK_53 =
CASE
WHEN DATEPART(ISO_WEEK, YR_LAST) = 53 THEN 1
ELSE 0
END
,WEEK_ISO_53 =
CASE
WHEN DATEPART(WEEK, YR_LAST) = 53 THEN 1
ELSE 0
END
,WEEK_DAY
,WEEKEND =
CASE
WHEN WEEK_DAY IN ( CASE @@DATEFIRST WHEN 1 THEN 6 WHEN 7 THEN 1 END, 7) THEN 1
ELSE 0
END
,MONTH
,MONTH_NAME
,MO_FIRST
,MO_LAST
,QUARTER
,[YEAR]
,YR_ISO = [YEAR] -
CASE
WHEN MONTH = 1 AND WEEK_ISO > 51 THEN 1
WHEN MONTH = 12 AND WEEK_ISO = 1 THEN -1
ELSE 0
END
,YR_FIRST
,YR_LAST
,YR_DAY
,YR_LEAP = CONVERT(BIT,
CASE
WHEN (YEAR % 400 = 0) OR (YEAR % 4 = 0 AND YEAR % 100 <> 0) THEN 1
ELSE 0
END)
FROM BASE
)
SELECT [DATE],[DAY],DAY_NAME,DAY_SUFFIX,WEEK,WEEK_ISO,WEEK_53,WEEK_ISO_53,WEEK_DAY,WEEKEND,[MONTH],MONTH_NAME,MO_FIRST,MO_LAST,QUARTER,[YEAR],YR_ISO,YR_FIRST,YR_LAST,YR_DAY,YR_LEAP FROM CORE
OPTION (MAXRECURSION 0);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment