15,917,940 members
Articles / Programming Languages / SQL
Tip/Trick

# Create Calendar by T-SQL

Rate me:
7 May 2013CPOL 20.2K   7   4
Create a calendar by inputting month

## Introduction

This SQL statement can generate a calendar by inputting a month and a year.

## Background

In my work, I usually need to generate a calendar by inputting a month and a year, so I write the following code to do it.

## Using the Code

SQL
```DECLARE @Month AS INT = 4 --Set the MONTH for which you want to generate the Calendar.
DECLARE @Year AS INT = 2013 --Set the YEAR for which you want to generate the Calendar.
DECLARE @StartDate AS DATETIME = CONVERT(VARCHAR,@Year) + RIGHT('0' + CONVERT(VARCHAR,@Month),2) + '01'

SELECT
SUM(CASE WHEN DATEPART(DW, DATEADD(DD,number,@StartDate)) = 1 _
THEN DATEPART(DAY, DATEADD(DD,NUMBER,@StartDate)) END) AS Sunday
,SUM(CASE WHEN DATEPART(DW, DATEADD(DD,number,@StartDate)) = 2 _
THEN DATEPART(DAY, DATEADD(DD,NUMBER,@StartDate)) END) AS Monday
,SUM(CASE WHEN DATEPART(DW, DATEADD(DD,number,@StartDate)) = 3 _
THEN DATEPART(DAY, DATEADD(DD,NUMBER,@StartDate)) END) AS Tuesday
,SUM(CASE WHEN DATEPART(DW, DATEADD(DD,number,@StartDate)) = 4 _
THEN DATEPART(DAY, DATEADD(DD,NUMBER,@StartDate)) END) AS Wednesday
,SUM(CASE WHEN DATEPART(DW, DATEADD(DD,number,@StartDate)) = 5 _
THEN DATEPART(DAY, DATEADD(DD,NUMBER,@StartDate)) END) AS Thursday
,SUM(CASE WHEN DATEPART(DW, DATEADD(DD,number,@StartDate)) = 6 _
THEN DATEPART(DAY, DATEADD(DD,NUMBER,@StartDate)) END) AS Friday
,SUM(CASE WHEN DATEPART(DW, DATEADD(DD,number,@StartDate)) = 7 _
THEN DATEPART(DAY, DATEADD(DD,NUMBER,@StartDate)) END) AS Saturday
FROM master.dbo.spt_values v
AND v.type = 'P'

## History

• 6th May, 2013: Initial post

Written By
Web Developer IBM
China
I am a software engineer using C# and SQL Server, a certified PMP working in IBM Data Collection team.