see this sql below

DECLARE @FirstOfMonth DATE={ts'2011-02-01 00:00:00'};



 SELECT DAY(DayNumber) AS DayNumber
       ,CAST(DayNumber AS Date) DayDate
       ,DATEPART(WEEKDAY,DayNumber) AS WeekDayIndx
FROM(
      SELECT TOP (CASE WHEN YEAR(@FirstOfMonth) % 4 = 0 THEN 366 ELSE 365 END) 
           DATEADD(DAY 
                 ,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1
                 , CAST(YEAR(@FirstOfMonth) AS VARCHAR(4)) + '0101' )  DayNumber
      From master..spt_values
     )x
WHERE DayNumber > DATEADD(SECOND,-1,DATEADD(MONTH, DATEDIFF(MONTH,0,@FirstOfMonth),0))
 AND  DayNumber < DATEADD(SECOND,-1,DATEADD(MONTH, DATEDIFF(MONTH,0,@FirstOfMonth)+1,0))

does this line perfectly calculate leap year

CASE WHEN YEAR(@FirstOfMonth) % 4 = 0 THEN 366 ELSE 365 END

see this url http://blog.sqlauthority.com/2012/03/03/sql-server-various-leap-year-logics/

they use bit different logic to calculate leap year

IIF((@Year%4=0 AND @Year%100 != 0) OR @Year%400=0, 1,0)

so tell me what to change in first sql to make it calculate right way leap year. thanks

Hi,

DECLARE @FirstOfMonth DATE={ts'2000-02-01 00:00:00'};
DECLARE @year INT = YEAR(@FirstOfMonth)



 SELECT DAY(DayNumber) AS DayNumber
       ,CAST(DayNumber AS Date) DayDate
       ,DATEPART(WEEKDAY,DayNumber) AS WeekDayIndx
	  
FROM(
      SELECT TOP (CASE WHEN (((@year % 4 = 0) AND (@year % 100 != 0)) OR (@year % 400 = 0))
 THEN 366 ELSE 365 END) 
           DATEADD(DAY 
                 ,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1
                 , CAST(YEAR(@FirstOfMonth) AS VARCHAR(4)) + '0101' )  DayNumber
      From master..spt_values
     )x
WHERE DayNumber > DATEADD(SECOND,-1,DATEADD(MONTH, DATEDIFF(MONTH,0,@FirstOfMonth),0))
 AND  DayNumber < DATEADD(SECOND,-1,DATEADD(MONTH, DATEDIFF(MONTH,0,@FirstOfMonth)+1,0))

But TOP has no sense in this query since the records number returned will be less than 365.

So:

.......FROM(
      SELECT      DATEADD(DAY 
                 ,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1
                 , CAST(YEAR(@FirstOfMonth) AS VARCHAR(4)) + '0101' )  DayNumber
      From master..spt_values
     )x
........

does this line perfectly calculate leap year

CASE WHEN YEAR(@FirstOfMonth) % 4 = 0 THEN 366 ELSE 365 END

see this url http://blog.sqlauthority.com/2012/03/03/sql-server-various-leap-year-logics/

they use bit different logic to calculate leap year

IIF((@Year%4=0 AND @Year%100 != 0) OR @Year%400=0, 1,0)

so tell me what to change in first sql to make it calculate right way leap year. thanks

No, your case expression does not properly determine the number of days in a year because there are exceptions when the year is a multiple of a century.  When the year is a multiple of 100, only those that are also multiples of 400 are leap years.  For example, the year 1900 has 365 days but year 2000 has 366 days.  The CASE expression below, derived from the IIF expression, is more correct:

CASE WHEN
        (YEAR(@FirstOfMonth) % 4 = 0
	 AND YEAR(@FirstOfMonth) % 100 <> 0)
	 OR YEAR(@FirstOfMonth) % 400 = 0 THEN 366 ELSE 365 END

This detail is not important for this query because you are returning no more than 31 days.  You will get the desired results regardless of leap years.

I suggest you avoid the undocumented spt_values table and instead create your own numbers table (a.k.a. tally table), or create a permanent calendar table.  See http://www.dbdelta.com/calendar-table-and-datetime-functions/ for examples.

u said : TOP has no sense in this query since the records

if i use TOP then could not i get my desired result?

if possible give me full modified sql what u think is right. thanks

Yes TOP has no sense because the result is less than 32 leap or not leap year.

I wrote th modified query:

DECLARE @FirstOfMonth DATE={ts'2000-02-01 00:00:00'};
DECLARE @year INT = YEAR(@FirstOfMonth)



 SELECT DAY(DayNumber) AS DayNumber
       ,CAST(DayNumber AS Date) DayDate
       ,DATEPART(WEEKDAY,DayNumber) AS WeekDayIndx
	  
FROM(
      SELECT  
           DATEADD(DAY 
                 ,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1
                 , CAST(YEAR(@FirstOfMonth) AS VARCHAR(4)) + '0101' )  DayNumber
      From master..spt_values
     )x
WHERE DayNumber > DATEADD(SECOND,-1,DATEADD(MONTH, DATEDIFF(MONTH,0,@FirstOfMonth),0))
 AND  DayNumber < DATEADD(SECOND,-1,DATEADD(MONTH, DATEDIFF(MONTH,0,@FirstOfMonth)+1,0))

Hi Mou_kolkata,

A trick to calculate to leap year, you don't have to re-invent the logic, rather you could rely on SQL Server's built-in logic.

DECLARE @FirstOfMonth DATE={ts'2011-02-01 00:00:00'};
SELECT CASE WHEN ISDATE(CAST(YEAR(@FirstOfMonth) AS VARCHAR(4))+'0229') =1 THEN 366 ELSE 365 END