SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2016
download SQL Server 2014
download SQL Server 2012



Create Monthly Calendar using SQL in SQL Server

This T-SQL tutorial shares SQL codes to create monthly calendar using SQL Server datetime functions like emonth, datepart,dateadd, datename, recursive CTE queries, etc. SQL programmers and database administrators can create calendars executing the final SQL code and SQL Server stored procedure, as well as follow the SQL calendar code step by step explained to understand how SQL Server date functions, SQL CTE queries are used for SQL training.

In this SQL tutorial, I want to share T-SQL codes I used to develop an application which will enable programmers to build monthly calendars similar to seen in below screenshot. What is important about this calendar, or for all calendars in our T-SQL tutorial is that Monday is always on the far-left column and Sunday is on the right-most column.

monthly calendar using SQL
Sample monthly calendar developed by using SQL on a SQL Server database

Let's start coding in SQL using Transact-SQL features and enhancements.

The starting point is a date parameter which will enable SQL programmers to get the full-month. This can be the first date of the month, or the month number and year, too.
I'll be using GETDATE() SQL function as an example. So my reference will be today's date value.

declare @date as date = dateadd(mm,0,getdate())

As SQL developers can realize easily, I read today's date value using SQL Server GetDate() datetime function and add or substract n months to or from today to prepare the new month's calendar.

As second step, I'll get the last day of the month, or the end of month date value using SQL EOMONTH() End Of Month function.

select eomonth(@date) endofmonth

Then T-SQL programmers can add a new value in the SELECT list for the first date of the month as follows

select
 dateadd(dd,1,eomonth(dateadd(mm,-1,@date))) firstofmonth,
 eomonth(@date) endofmonth

Up to here, it was the easy part. We have just set the boundaries of the month using SQL date functions like EMONTH() and DATEADD()

The second block of our sample SQL calendar code will be for defining the boundaries of dates seen on the monthly calendar.
This means, SQL programmers will be able to set the last Monday which belongs to the previous month or if the first of the month is Monday, then we will use it as the beginning date of the calendar.
Using similar methods, but this time applying the same solution for finding the last Sunday. If the end of the month date is a Sunday, it is the date we are querying for.
Otherwise, we will be selecting all dates from the next month until we reach the first Sunday.

Is it complex?
Let's check below sample calendar for April, 2015. We have already defined the dates between 1st of April and 30th of April.
Unfortunately, as seen in below calendar page, using SQL we have to be able to identify the dates from 30th to 31st of March and from 1st to 3rd of May additionally.
I marked around those days within red rectangles.

create month calendar in SQL Server
April calendar includes days from March and May as well for filling the remaining dates of weeks apart from the days of April

declare @date as date = dateadd(mm,-1,getdate())

;with cte as (
 select
  dateadd(dd,1,eomonth(dateadd(mm,-1,@date))) firstofmonth,
  eomonth(@date) endofmonth
), cte1 as (
 select
  dateadd(dd, -1 * (
  case datepart(weekday, firstofmonth)
   when 1 then 6
   else datepart(weekday, firstofmonth) - 2
  end), firstofmonth) previousmonday,
  firstofmonth,
  endofmonth,
  case
   when datepart(dw,endofmonth) = 1 then endofmonth
   else
   dateadd(dd, 8 - datepart(dw,endofmonth), endofmonth)
  end as lastsunday
 from cte
)
select * from cte1

When programmers execute above SQL CTE (Common Table Expression) query, the output will be as follows for April.

get dates in specific month using SQL
Find calendar boundary dates using SQL for a given month's calendar

SQL developers can now execute a SQL recursive query using CTE for fetching all dates between previousmonday and lastsunday date values.

Instead of selecting all data from CTE1 resultset in previous code, append following CTE expression and final select.

SQL
SQL Server Recursive Query structure using SQL CTE (Common Table Expression)

...
), cte2 as (
 select
  1 cnt, previousmonday as calendarday, lastsunday
 from cte1

 union all

 select
  cnt+1, dateadd(dd, 1, calendarday) as calendarday, lastsunday
 from cte2
 where
  dateadd(dd, 1, calendarday) <= lastsunday
)
select cnt, calendarday, datename(dw,calendarday) nameofday from cte2

The output of the above SQL statement formed of multiple CTE's will be as follows.

SQL CTE for monthly calendar for SQL Server
SQL CTE expression

SQL developers are now ready to create a calendar based on monthly basis using SQL functions as follows

declare @date as date = dateadd(mm,-1,getdate())

;with cte as (
 select
  dateadd(dd,1,eomonth(dateadd(mm,-1,@date))) firstofmonth,
  eomonth(@date) endofmonth
), cte1 as (
 select
  dateadd(dd, -1 * (
  case datepart(weekday, firstofmonth)
   when 1 then 6
   else datepart(weekday, firstofmonth) - 2
  end), firstofmonth) previousmonday,
  firstofmonth,
  endofmonth,
  case
   when datepart(dw,endofmonth) = 1 then endofmonth
   else
   dateadd(dd, 8 - datepart(dw,endofmonth), endofmonth)
  end as lastsunday
 from cte
), cte2 as (
 select
  1 cnt, previousmonday as calendarday, lastsunday
 from cte1

 union all

 select
  cnt+1, dateadd(dd, 1, calendarday) as calendarday, lastsunday
 from cte2
 where
  dateadd(dd, 1, calendarday) <= lastsunday
), calendar as (
 select
  cnt,
  ((cnt-1)/7)+1 weeknumber,
  calendarday,
  datename(dw,calendarday) nameofday,
  case when (cnt % 7) = 1 then cast(calendarday as varchar) else '' end as Monday,
  case when (cnt % 7) = 2 then cast(calendarday as varchar) else '' end as Tuesday,
  case when (cnt % 7) = 3 then cast(calendarday as varchar) else '' end as Wednesday,
  case when (cnt % 7) = 4 then cast(calendarday as varchar) else '' end as Thursday,
  case when (cnt % 7) = 5 then cast(calendarday as varchar) else '' end as Friday,
  case when (cnt % 7) = 6 then cast(calendarday as varchar) else '' end as Saturday,
  case when (cnt % 7) = 0 then cast(calendarday as varchar) else '' end as Sunday
 from cte2
)
select
 weeknumber,
 max(Monday) Monday,
 max(Tuesday) Tuesday,
 max(Wednesday) Wednesday,
 max(Thursday) Thursday,
 max(Friday) Friday,
 max(Saturday) Saturday,
 max(Sunday) Sunday
from calendar
group by weeknumber

Here is the calendar we has SQL Server developers have created by running above SQL command is as follows

SQL calendar in week display
SQL calendar in week display

If you change the inner part of the CTE expression named "calendar" and add additional condition in CASE statement as follows, you can modify the output calendar view as well

select
 cnt,
 ((cnt-1)/7)+1 weeknumber,
 calendarday,
 datename(dw,calendarday) nameofday,
-- instead of following commented CASE stements
--case when (cnt % 7) = 1 then cast(calendarday as varchar) else '' end as Monday,
--case when (cnt % 7) = 2 then cast(calendarday as varchar) else '' end as Tuesday,
--case when (cnt % 7) = 3 then cast(calendarday as varchar) else '' end as Wednesday,
--case when (cnt % 7) = 4 then cast(calendarday as varchar) else '' end as Thursday,
--case when (cnt % 7) = 5 then cast(calendarday as varchar) else '' end as Friday,
--case when (cnt % 7) = 6 then cast(calendarday as varchar) else '' end as Saturday,
--case when (cnt % 7) = 0 then cast(calendarday as varchar) else '' end as Sunday
-- use following SQL CASE conditional command
 case when (cnt % 7) = 1 and month(calendarday) = month(@date) then cast(calendarday as varchar) else '' end as Monday,
 case when (cnt % 7) = 2 and month(calendarday) = month(@date) then cast(calendarday as varchar) else '' end as Tuesday,
 case when (cnt % 7) = 3 and month(calendarday) = month(@date) then cast(calendarday as varchar) else '' end as Wednesday,
 case when (cnt % 7) = 4 and month(calendarday) = month(@date) then cast(calendarday as varchar) else '' end as Thursday,
 case when (cnt % 7) = 5 and month(calendarday) = month(@date) then cast(calendarday as varchar) else '' end as Friday,
 case when (cnt % 7) = 6 and month(calendarday) = month(@date) then cast(calendarday as varchar) else '' end as Saturday,
 case when (cnt % 7) = 0 and month(calendarday) = month(@date) then cast(calendarday as varchar) else '' end as Sunday
from cte2

SQL calendar code in weekly display
Here is the output in weekly display mode when SQL calendar code is executed

I modified the beginning of the script and placed it into a SQL stored procedure to use repeatitively
This is how the beginning of the SQL calendar script is altered

create procedure sp_create_kodyaz_calendar_for_month
(
 @year int,
 @month int
)
as

declare @date as date
set @date = CONCAT(cast(@year as varchar),'-',cast(@month as varchar),'-01')

As developers will realize at first look, I create a new SQL stored procedure named sp_create_kodyaz_calendar_for_month.
This new stored procedure takes two parameters; year and month as number.
Instead of using GetDate() and create calendar tables for months referencing to today, I create the @date parameter on the fly dynamically by using the new @year and @month input parameters.
I also used SQL CONCAT() function (string concatenation function in Transact-SQL) in this sample.

Here is how I executed the new SQL calendar stored procedure and compare it with Windows 7 calendar gadget.

exec dbo.sp_create_kodyaz_calendar_for_month 2015, 6 -- June, 2015 calendar

compare SQL calendar and Windows 7 gadget
SQL calendar table for June, 2015 comparing with Windows 7 calendar gadget

exec dbo.sp_create_kodyaz_calendar_for_month 2015, 3 -- March, 2015 calendar

calendar created for specific month in SQL
SQL calendar table for March 2015 includes 6 weeks as seen in above screenshot

exec dbo.sp_create_kodyaz_calendar_for_month 2010, 2 -- February, 2010 calendar

create calendar using SQL Server stored procedure
For February in 2010, SQL calendar table has only 4 weeks

download SQL code SQL programmers can download and use SQL Calendar Script.

I hope calendar table creation script in this SQL tutorial will be useful for SQL programmers and SQL Server database administrators.
Developers and admins can use the script to create calendar tables or at least they can use this as a sample for understanding SQL Server date functions like DateName() SQL function, EMonth(), DateAdd(), and DatePart(), also SQL CTE recursive query structure, multiple CTE's, etc.







Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums







Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems