Abdul Rasheed Abdul Rasheed - 7 months ago 59
SQL Question

How to get all the dates in a full calendar month

In a calendar control, we can see some dates from the previous month and next month also. Sample image below

enter image description here

(ie Apr-2016: Starts from Mar-28 and ends in May-08
Mar-2016: Starts from Apr Feb-29 and ends in Apr-10)


Here, i need to generate a list of all the dates in a calendar control for a particular year month. My week start is Monday.
Here is the tsql script i have tried so far.

DECLARE @V_DATE DATE = GETDATE()

;WITH CTE_DATE AS (
SELECT DATEADD(dd,-(DAY(@V_DATE)-1),@V_DATE) CDATE
UNION ALL
SELECT DATEADD(dd,1,CDATE)
FROM CTE_DATE
WHERE DATEADD(dd,1,CDATE) <= DATEADD(dd,-(DAY(DATEADD(mm,1,CDATE))),DATEADD(mm,1,CDATE))
)
SELECT * FROM CTE_DATE


Result Is:

2016-04-01
2016-04-02
.
.
2016-04-29
2016-04-30


It will list all the days from a inputted year month, but i need to include the
missing dates from the previous month as well as next month.

Expected result for
Apr-2016


2016-03-28
2016-03-29
.
2016-04-15
.
2016-05-07
2016-05-08


Expected result for
May-2016


2016-04-25
2016-04-26
.
2016-05-15
.
2016-06-04
2016-06-05


Note:- The calendar control is always showing 42 days.

Answer

since your week is starts on Monday,you can take referece to date 0 '1900-01-01' which is a Monday. Adding 41 days would gives you your end date

select  
      date_fr = dateadd(day, datediff(day, 0, '2016-05-01') / 7 * 7, 0),
      date_to = dateadd(day, datediff(day, 0, '2016-05-01') / 7 * 7, 41)

the following gives you date 1900-01-01 and Monday

select  convert(datetime, 0), datename(weekday, 0)
Comments