SQL stament to determine the number of Mondays in a month

Is there a way to get the number of Mondays in a given month (and year) without using T-开发者_C百科SQL?

Thanks

I'm not sure what you mean by saying:

Is there a way to get the number of Mondays in a given month (and year) without using T-SQL?

If you are hoping for a universal code fragment that will do this across all databases, forget it. I doubt that you'll even be able to get a version to run on two different databases. Dates and things like weekdays tend to be be implemented differently across database vendors.

Here is the TSQL way (Monday Month Count):

;with AllDates AS
(SELECT CONVERT(datetime,CONVERT(varchar(6),GETDATE(),112)+'01') AS DateOf
 UNION ALL
 SELECT DateOf+1
 FROM AllDates
 WHERE
 MONTH(DateOf+1)=MONTH(CONVERT(datetime,CONVERT(varchar(6),GETDATE(),112)+'01'))
)
SELECT COUNT(DateOf) AS MondayCountMonth
FROM AllDates 
WHERE DATENAME(weekday,DateOf)='Monday'

Here is the TSQL way (Monday Year Count):

;with AllDates AS
(SELECT CONVERT(datetime,CONVERT(varchar(4),GETDATE(),112)+'0101') AS DateOf
 UNION ALL
 SELECT DateOf+1
 FROM AllDates
 WHERE
 YEAR(DateOf+1)=Year(CONVERT(datetime,CONVERT(varchar(4),GETDATE(),112)+'0101'))
)
SELECT COUNT(DateOf) AS MondayCountYear
FROM AllDates 
WHERE DATENAME(weekday,DateOf)='Monday'
OPTION (MAXRECURSION 367)

EDIT based on OP comment, here is a version which finds the monthly and yearly Monday counts as sub-queries within another query:

DECLARE @YourTable table (Col1 int, Col2 varchar(5))
INSERT @YourTable VALUES (1,'aaa')
INSERT @YourTable VALUES (2,'bbb')
INSERT @YourTable VALUES (3,'ccc')

;with MonthMondayCount AS
(SELECT CONVERT(datetime,CONVERT(varchar(6),GETDATE(),112)+'01') AS DateOf
 UNION ALL
 SELECT DateOf+1
 FROM MonthMondayCount
 WHERE
 MONTH(DateOf+1)=MONTH(CONVERT(datetime,CONVERT(varchar(6),GETDATE(),112)+'01'))
)
,YearMondayCount AS
(SELECT CONVERT(datetime,CONVERT(varchar(4),GETDATE(),112)+'0101') AS DateOf
 UNION ALL
 SELECT DateOf+1
 FROM YearMondayCount
 WHERE
 YEAR(DateOf+1)=Year(CONVERT(datetime,CONVERT(varchar(4),GETDATE(),112)+'0101'))
)
SELECT
    y.*
    ,(SELECT COUNT(DateOf) AS MondayCountMonth FROM MonthMondayCount WHERE DATENAME(weekday,DateOf)='Monday') AS MondayCountMonth
    ,(SELECT COUNT(DateOf) AS MondayCountYear FROM YearMondayCount WHERE DATENAME(weekday,DateOf)='Monday') AS MondayCountYear
    FROM @YourTable  y
    OPTION (MAXRECURSION 367)

OUTPUT:

Col1        Col2  MondayCountMonth MondayCountYear
----------- ----- ---------------- ---------------
1           aaa   5                52
2           bbb   5                52
3           ccc   5                52

(3 row(s) affected) 

Try this : )

DECLARE @tmpDate as date
set @tmpDate  = getdate(); --you can add any date
DECLARE @Startdate as varchar( 8)
DECLARE @Enddate as varchar( 8)
SELECT @Startdate = replace(convert(varchar,cast(DATEADD(month, DATEDIFF(month, 0, @tmpDate), 0) as date) , 111), '/', '');
SELECT @Enddate =  replace(convert(varchar, cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@tmpDate)+1,0)) as date), 111), '/', '');

with [dates] as (
    select  convert(date , @Startdate  ) as [date] --start
    union all
    select   dateadd(day , 1 , [date]) 
    from [dates]
    where [date] < @Enddate )
Select X.WeekDayNumber, count(X.WeekDayNumber) as NumberOfDays
from (
SELECT [date] , DATEPART(weekday,[date] ) as WeekDayNumber
from [dates]
WHERE [date] IS NOT NULL)X
Group  by X.WeekDayNumber
option (maxrecursion 0 )

( DATEADD( DAY, -1, DATEADD(  MONTH, 1 , month + '-01' ) -
  DATEADD( DAY, 7 - DATEPART( WEEKDAY, month + '-01' ), month + '-01' )
) DIV 7 + 1

I don't know how much ANSI SQL compatible this is but it works in MySql (not after the changes, it should work in SQL Server now).

month should be in 'yyyy-mm' format