SQL - How to get a complete month calendar table

Question

I need to generate a report that will show 7 Columns Monday to Sunday and 5 Rows, just like a normal month calendar:

enter image description here

I need the dates in a list table, so the desire table result should look like this:

enter image description here

That way my report designer will automatically create a view like above.

The above table result should be generated on the fly with no need of any database table because after this I will have to left join the dates to one of my db tables so I can populate other kind of information that I will use to display in my report for each day.

Any clue?

Look at the similar question [link](http://stackoverflow.com/questions/5635594/how-to-create-a-calender-table-for-100-years-in-sql). Possible duplicate. — vendettamit, May 21 '15 at 00:15
Do your other reports for example need to exclude public holidays? You should consider creating a calendar table. — Nick.Mc, May 21 '15 at 00:37
All public holidays, etc.. are already in tables in my database, but I have table for employee holidays, table for public holidays, table for disability days so thats why I need this temp calendar table because then I will join it with my other tables to print the right info on the right day. — VAAA, May 21 '15 at 00:46
Possible duplicate of [How to create a Calendar table for 100 years in Sql](https://stackoverflow.com/questions/5635594/how-to-create-a-calendar-table-for-100-years-in-sql) — Liam, Jul 18 '18 at 10:00

score 3 · Answer 1 · edited Jul 18 '18 at 10:01

SQL Fiddle for arbitrary sequence of dates, no tables required.

http://sqlfiddle.com/#!3/9eecb7/275

DECLARE @dtStartDate datetime
DECLARE @dtEndDate datetime
SET @dtStartDate = '2015-05-01'
SET @dtEndDate = '2015-05-31'

SELECT
  CASE DATEPART(weekday, T.DateVal)
    WHEN 1 THEN 'Sunday'
    WHEN 2 THEN 'Monday'
    WHEN 3 THEN 'Tuesday'
    WHEN 4 THEN 'Wednesday'
    WHEN 5 THEN 'Thursday'
    WHEN 6 THEN 'Friday'
    WHEN 7 THEN 'Saturday'
  END AS WeekDay,
  DATEPART(day, T.DateVal) AS [Date],
  DATEPART(month, T.DateVal) AS [Month],
  DATEPART(year, T.DateVal) AS [Year]
FROM
(
  SELECT
      DATEADD(day, SEQ.SeqValue, @dtStartDate) DateVal
  FROM
  (
  SELECT
      (HUNDREDS.SeqValue + TENS.SeqValue + ONES.SeqValue) SeqValue
  FROM
      (
      SELECT 0  SeqValue
      UNION ALL
      SELECT 1 SeqValue
      UNION ALL
      SELECT 2 SeqValue
      UNION ALL
      SELECT 3 SeqValue
      UNION ALL
      SELECT 4 SeqValue
      UNION ALL
      SELECT 5 SeqValue
      UNION ALL
      SELECT 6 SeqValue
      UNION ALL
      SELECT 7 SeqValue
      UNION ALL
      SELECT 8 SeqValue
      UNION ALL
      SELECT 9 SeqValue
      ) ONES
  CROSS JOIN
      (
      SELECT 0 SeqValue
      UNION ALL
      SELECT 10 SeqValue
      UNION ALL
      SELECT 20 SeqValue
      UNION ALL
      SELECT 30 SeqValue
      UNION ALL
      SELECT 40 SeqValue
      UNION ALL
      SELECT 50 SeqValue
      UNION ALL
      SELECT 60 SeqValue
      UNION ALL
      SELECT 70 SeqValue
      UNION ALL
      SELECT 80 SeqValue
      UNION ALL
      SELECT 90 SeqValue
      ) TENS
  CROSS JOIN
      (
      SELECT 0 SeqValue
      UNION ALL
      SELECT 100 SeqValue
      UNION ALL
      SELECT 200 SeqValue
      UNION ALL
      SELECT 300 SeqValue
      UNION ALL
      SELECT 400 SeqValue
      UNION ALL
      SELECT 500 SeqValue
      UNION ALL
      SELECT 600 SeqValue
      UNION ALL
      SELECT 700 SeqValue
      UNION ALL
      SELECT 800 SeqValue
      UNION ALL
      SELECT 900 SeqValue
      ) HUNDREDS
  ) SEQ
) T
WHERE
  T.DateVal <= @dtEndDate
ORDER BY
  T.DateVal ASC

@Liam Thank you for noticing and doing this! – Pittsburgh DBA Aug 25 '18 at 20:09 — Pittsburgh DBA, Aug 25 '18 at 20:09

APH · Answer 2 · 2015-05-21T01:02:52.213

Edited to add beginning blank days. Same logic can be used to append blank days if necessary.

declare @begindate date = '5-1-2015'
declare @enddate date = '6-1-2015'
create table MyCal (MyWeekday varchar(10), MyDate varchar(2), MyMonth varchar(10), MyYear int)

declare @DaysFromMonday int = 
    case datepart(weekday, @begindate)
    when 1 then 6
    else datepart(weekday, @begindate) - 2
    end

declare @datecounter date = dateadd(dd, -1* @daysFromMonday, @begindate)

while @datecounter < @enddate
begin
  insert into MyCal values (
      datename(weekday, @datecounter)
      , case when @datecounter < @begindate then '' else cast(datepart(DD, @datecounter) as varchar) end
      , datename(month, @datecounter)
      , datepart(YYYY, @datecounter)
  )
set @datecounter = dateadd(day, 1, @datecounter)
end

select * from MyCal

score 0 · Answer 3 · answered May 21 '15 at 00:43

You can use this piece of code. Replace the #tmp table with a proper table name that you prefer.

declare @start_date datetime, @end_date datetime, @cur_date datetime

set @start_date = '2015-05-01'
set @end_date = '2016-04-30'
set @cur_date = @start_date

create table #tmp 
(weekday varchar(10), 
date int,
month varchar(10),
year int)

while @cur_date <= @end_date
begin

insert into #tmp
select datename(dw, @cur_date), datepart(day, @cur_date), datename(month, @cur_date), datepart(year, @cur_date)

set @cur_date = dateadd(dd, 1, @cur_date) 

end

select * from #tmp

drop table #tmp

Almost working, but I don't get the 4 days before 1 may (the empty ones) — VAAA, May 21 '15 at 00:44

score 0 · Answer 4 · answered May 21 '15 at 02:36

Try this version - You will have to run one month at a time. I've also added the blanks if the last day of the month is not a Sunday e.g. Try he month of August 2015.

declare @start_date datetime, @end_date datetime, @cur_date datetime

set @start_date = '2015-05-01'
set @end_date = '2015-05-31'
set @cur_date = @start_date

create table #tmp 
(weekday varchar(10), 
date varchar(2),
month varchar(10),
year int)


while datepart(dw, @cur_date) > 2
begin 

insert into #tmp
select datename(dw, dateadd(dd, -(datepart(dw, @cur_date) - 2), @start_date)), '', datename(month, @start_date), datepart(year, @start_date)

set @cur_date = dateadd(dd, -1, @cur_date) 

end

set @cur_date = @start_date

while @cur_date <= @end_date
begin

insert into #tmp
select datename(dw, @cur_date), datepart(day, @cur_date), datename(month, @cur_date), datepart(year, @cur_date)

set @cur_date = dateadd(dd, 1, @cur_date) 

end

set @cur_date = @end_date

while datepart(dw, @cur_date) > 1
begin 

insert into #tmp
select datename(dw, dateadd(dd, 1, @cur_date)), '', datename(month, @end_date), datepart(year, @end_date)

set @cur_date = dateadd(dd, 1, @cur_date) 

end


select * from #tmp

drop table #tmp

Hope this helps.

score 0 · Answer 5 · answered May 21 '15 at 08:21

Here is an alternative method using multiple CTE queries and SQL EMONTH end of month function with DATENAME SQL function

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

;with monthdates as (
    SELECT dateadd(dd,1,EOMONTH(dateadd(mm,-1,@date))) firstofmonth, EOMONTH (@date) lastofmonth
), calc as (
    select
        firstofmonth, lastofmonth,
        dateadd(dd, 
                -1 * case when datepart(dw,firstofmonth)-2 >= 0 then datepart(dw,firstofmonth)-2 else datepart(dw,firstofmonth)+7-2 end,
                firstofmonth
                ) firstofcalendar,
        datediff(WEEK, 
                dateadd(dd, 
                        -1 * case when datepart(dw,firstofmonth)-2 >= 0 then datepart(dw,firstofmonth)-2 else datepart(dw,firstofmonth)+7-2 end,
                        firstofmonth
                        ),
                lastofmonth) weekcount
    from monthdates
), calendar1 as (
    select
        firstofcalendar,
        firstofmonth,
        lastofmonth,
        case when dateadd(dd, weekcount * 7 - 1, firstofcalendar) = lastofmonth then lastofmonth
        else
            dateadd(dd, (weekcount+1) * 7 - 1, firstofcalendar)
        end as lastofcalendar
    from calc
), calendar as (
select
    datename(dw,date) dayname,
    case when month(date) = month(@date) then cast(cast(date as date) as nvarchar) else '' end as date
from calendar1
cross apply [dbo].[DateTable](firstofcalendar, lastofcalendar)
)
select *, datename(mm,@date) month, datepart(yyyy,@date) year from calendar

score -1 · Answer 6 · answered May 21 '15 at 01:38

Something like this should do you. It should work for any start-of-week you choose: you just need to set datefirst N on your connection/query to suit. If you want the week to start on Monday, use set datefirst 1.

Here's the query:

set datefirst = 1 -- start the week on Monday

declare @month date = '1 May 2015'

;with
month_days as
(
  select dd = -5 union all
  select dd = -4 union all
  select dd = -3 union all
  select dd = -2 union all
  select dd = -1 union all
  select dd =  0 union all
  select dd =  1 union all
  select dd =  2 union all
  select dd =  3 union all
  select dd =  4 union all
  select dd =  5 union all
  select dd =  6 union all
  select dd =  7 union all
  select dd =  8 union all
  select dd =  9 union all
  select dd = 10 union all
  select dd = 11 union all
  select dd = 12 union all
  select dd = 13 union all
  select dd = 14 union all
  select dd = 15 union all
  select dd = 16 union all
  select dd = 17 union all
  select dd = 18 union all
  select dd = 19 union all
  select dd = 20 union all
  select dd = 21 union all
  select dd = 22 union all
  select dd = 23 union all
  select dd = 24 union all
  select dd = 25 union all
  select dd = 26 union all
  select dd = 27 union all
  select dd = 28 union all
  select dd = 29 union all
  select dd = 30 union all
  select dd = 31 union all
  select dd = 32 union all
  select dd = 33 union all
  select dd = 34 union all
  select dd = 35 union all
  select dd = 36 union all
  select dd = 37
),
calendar as
(
  select * ,
         yyyymmdd = dateadd(day,t.dd-1,@month)
  from month_days t
)
select Day_Of_Week = datename(weekday,c.yyyymmdd ) ,
       DD          = datepart(day   , c.yyyymmdd ) ,
       MM          = datepart(month , c.yyyymmdd ) ,
       YYYY        = datepart(year  , c.yyyymmdd )
from calendar c
where       datediff(month,@month,c.yyyymmdd) =  0                                        -- current month
   OR (     datediff(month,@month,c.yyyymmdd) <  0                                        -- previous month
        and datepart(weekday,c.yyyymmdd)      <  datepart(weekday,@month) )               -- ...to pad out the first week
   OR (     datediff(month,@month,c.yyyymmdd) >  0                                        -- next month
     and datepart(weekday,c.yyyymmdd)         >= datepart(weekday,dateadd(month,1,@month)) -- ... to pad out the last week
   )
order by c.yyyymmdd

Running the above query produces the expected

Day_Of_Week DD MM YYYY
=========== == == ====
Monday      27  4 2015
Tuesday     28  4 2015
Wednesday   29  4 2015
Thursday    30  4 2015
Friday       1  5 2015
Saturday     2  5 2015
Sunday       3  5 2015
Monday       4  5 2015
Tuesday      5  5 2015
Wednesday    6  5 2015
Thursday     7  5 2015
Friday       8  5 2015
Saturday     9  5 2015
Sunday      10  5 2015
Monday      11  5 2015
Tuesday     12  5 2015
Wednesday   13  5 2015
Thursday    14  5 2015
Friday      15  5 2015
Saturday    16  5 2015
Sunday      17  5 2015
Monday      18  5 2015
Tuesday     19  5 2015
Wednesday   20  5 2015
Thursday    21  5 2015
Friday      22  5 2015
Saturday    23  5 2015
Sunday      24  5 2015
Monday      25  5 2015
Tuesday     26  5 2015
Wednesday   27  5 2015
Thursday    28  5 2015
Friday      29  5 2015
Saturday    30  5 2015
Sunday      31  5 2015
Monday       1  6 2015
Tuesday      2  6 2015
Wednesday    3  6 2015
Thursday     4  6 2015
Friday       5  6 2015
Saturday     6  6 2015

But in this case i dont need June days, there will be months where I will have to show other months before and after, if sequence to show has more days that the matrix 7x5 then i have to add a row to the matrix, so I must have always multiples of 7 (sun to sat) — VAAA, May 21 '15 at 01:46
This covers it. There are at most 31 days per month, plus, at most, 6 days on either side of the the month necessary to round out the 1st or last week. Hence the range of days in the virtual table. So the raw result set is larger than you need. The where clause restricts the result set to the rows necessary for a complete monthly calendar page. — Nicholas Carey, May 21 '15 at 05:02

SQL - How to get a complete month calendar table

6 Answers6

Linked