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.
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.
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.
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 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 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
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
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
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
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
For February in 2010, SQL calendar table has only 4 weeks
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.