How to create a Calendar table in SQL Server

Below Query to generate Calendar table based on given Start and End Date:

---------Code to generate Calendar table -----------

If OBJECT_ID('tempdb..#Calendar') IS NOT NULL DROP TABLE #Calendar

Declare @StartDate date

Declare @EndDate as Date

Select @StartDate = '2017-03-01'

Select @EndDate=  '2019-04-30'

 

CREATE TABLE #Calendar(

         CalendarMonthStart date PRIMARY KEY

       , CalendarMonthEnd date

       );

WITH

       t4 AS (SELECT n FROM (VALUES(0),(0),(0),(0)) t(n))

       ,t256 AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) - 1 AS num  FROM t4 AS a CROSS JOIN t4 AS b CROSS JOIN t4 AS c CROSS JOIN t4 AS d)

INSERT INTO #Calendar

SELECT

         DATEADD(month, num, @StartDate)

       , DATEADD(day, -1, DATEADD(month, num + 1, @StartDate))

FROM t256

WHERE

       num <= DATEDIFF(month, @StartDate, @EndDate);

       Select * from #Calendar

Below is output:

CalendarMonthStart	CalendarMonthEnd
3/1/2017	3/31/2017
4/1/2017	4/30/2017
5/1/2017	5/31/2017
6/1/2017	6/30/2017
7/1/2017	7/31/2017
8/1/2017	8/31/2017
9/1/2017	9/30/2017
10/1/2017	10/31/2017
11/1/2017	11/30/2017
12/1/2017	12/31/2017
1/1/2018	1/31/2018
2/1/2018	2/28/2018
3/1/2018	3/31/2018
4/1/2018	4/30/2018
5/1/2018	5/31/2018
6/1/2018	6/30/2018
7/1/2018	7/31/2018
8/1/2018	8/31/2018
9/1/2018	9/30/2018
10/1/2018	10/31/2018
11/1/2018	11/30/2018
12/1/2018	12/31/2018
1/1/2019	1/31/2019
2/1/2019	2/28/2019
3/1/2019	3/31/2019
4/1/2019	4/30/2019

Get Week End Date Using SQL Server

Below SQL code will provide End Date of a week:

Here in below SQL code, I have used SET DATEFIRST 1 because 1 is for Start of my week is from Monday so If you want to start your week from another day of week than you can replace 1 with that particular Day number so based on your start week day, end day of week will get calculated.


SET DATEFIRST 1

  Declare @UpdateDate Date


Set @UpdateDate=GetDate()

　
Select


Case When DATEPART(dw,@UpdateDate) =7 then @UpdateDate

Else
Dateadd(dd,(7-DATEPART(dw,@UpdateDate)),@UpdateDate)

end as WeekEndDate

Get Week Start Date in SQL Server with example

Below SQL code will provide Start Date of a week:

Here in below SQL code, I have used SET DATEFIRST 1 because 1 is for Start of my week is from Monday so If you want to start your week from another day of week than you can replace 1 with that particular Day number.


SET DATEFIRST 1

Declare @UpdateDate Date

Set @UpdateDate='03/12/2019'

 

Select

Case When DATEPART(dw,@UpdateDate) =1 then @UpdateDate

Else

Dateadd(dd,-(DATEPART(dw,@UpdateDate)-1),@UpdateDate)

end as WeekStartDate