Click here to Skip to main content
15,921,226 members
Articles / Programming Languages / SQL
Tip/Trick

Create Calendar by T-SQL

Rate me:
Please Sign up or sign in to vote.
4.82/5 (5 votes)
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'
DECLARE @EndDate AS DATETIME = DATEADD(DAY,-1,DATEADD(MONTH,1,@StartDate));

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
WHERE DATEADD(DD,number,@StartDate) BETWEEN @StartDate _
AND DATEADD(DAY,-1,DATEADD(MONTH,1,@StartDate))
AND v.type = 'P'
GROUP BY DATEPART(WEEK, DATEADD(DD,number,@StartDate)) 

History

  • 6th May, 2013: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


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

Comments and Discussions

 
QuestionCalendar in Oracle? Pin
Ka Lai Chan14-Aug-14 5:35
Ka Lai Chan14-Aug-14 5:35 
QuestionNice one.. Pin
Raja Sekhar S30-Jul-13 22:44
Raja Sekhar S30-Jul-13 22:44 
GeneralMy Vote Pin
Umer Akram28-May-13 1:10
Umer Akram28-May-13 1:10 
Pretty decent work
Rebel Inside

GeneralMy vote of 4 Pin
Hoangitk6-May-13 22:45
professionalHoangitk6-May-13 22:45 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.