MS SQL Query to generate calendar

Asked By 20 points N/A Posted on -
qa-featured

Hi Techyv Friends,

I need a sample coding in MS SQL Query to generate calendar, that will help me to understand the coding.

Thanks in Advance,

Abelina Sophia

SHARE
Answered By 0 points N/A #198686

MS SQL Query to generate calendar

qa-featured

The below is the sample code to generate calendar for the base month and year you input:

DECLARE @Base_Month AS INT = 1 –Setting the base month the MONTH for which you want to generate the Calendar.

DECLARE @Base_Year AS INT = 2014 –Setting the base year.

DECLARE @IntialDate AS DATETIME = CONVERT(VARCHAR,@Base_Year) + RIGHT('0' + CONVERT(VARCHAR,@Base_Month),2) + '01'

DECLARE @FinalEndDate AS DATETIME = DATEADD(DAY,-1,DATEADD(MONTH,1,@InitialDate));

SELECT

SUM(CASE WHEN DATEPART(DW, DATEADD(DD,number,@InitialDate)) = 1

THEN DATEPART(DAY, DATEADD(DD,NUMBER,@InitialDate)) END) AS Sunday

,SUM(CASE WHEN DATEPART(DW, DATEADD(DD,number,@InitialDate)) = 2

THEN DATEPART(DAY, DATEADD(DD,NUMBER,@InitialDate)) END) AS Monday

,SUM(CASE WHEN DATEPART(DW, DATEADD(DD,number,@InitialDate)) = 3

THEN DATEPART(DAY, DATEADD(DD,NUMBER,@InitialDate)) END) AS Tuesday

,SUM(CASE WHEN DATEPART(DW, DATEADD(DD,number,@InitialDate)) = 4

THEN DATEPART(DAY, DATEADD(DD,NUMBER,@InitialDate)) END) AS Wednesday

,SUM(CASE WHEN DATEPART(DW, DATEADD(DD,number,@InitialDate)) = 5

THEN DATEPART(DAY, DATEADD(DD,NUMBER,@InitialDate)) END) AS Thursday

,SUM(CASE WHEN DATEPART(DW, DATEADD(DD,number,@InitialDate)) = 6

THEN DATEPART(DAY, DATEADD(DD,NUMBER,@InitialDate)) END) AS Friday

,SUM(CASE WHEN DATEPART(DW, DATEADD(DD,number,@InitialDate)) = 7

THEN DATEPART(DAY, DATEADD(DD,NUMBER,@InitialDate)) END) AS Saturday

FROM master.dbo.spt_values m

WHERE DATEADD(DD,number,@InitialDate) BETWEEN @InitialDate

AND DATEADD(DAY,-1,DATEADD(MONTH,1,@InitialDate))

AND v.type = 'P'

GROUP BY DATEPART(WEEK, DATEADD(DD,number,@InitialDate))

Login/Register to Answer

Related Questions