Is it the first, second,third, fourth or fifth Saturday of

Question

Is it the first, second,third, fourth or fifth Saturday of

Michael Crain

SSC Rookie

Points: 47
More actions
November 28, 2005 at 5:36 pm

#89974

Can SQL Server determine if today is the first, second,third, fourth or fifth Saturday of the month? The reason I ask is I have a re-ocurring task interface built into my calendar application. I can get it to recognize daily and specific days and weekdays, but this is the only one I'm stick on.

For example, Thanksgiving is always Thursday # 4 in November.

Here is my table:

[RAgendaID] [int] , ( Tracking #)
[userID] [varchar] (25) ,
[TaskName] [varchar] (250) ,
[Priority] [int] ,
[AgendaType] [varchar] (50) ,(Work, Education, Family)
[StartMonth] [int] ,
[StartDate] [int] ,
[StartYear] [int] ,
[StartTime] [varchar] (25) ,
[EndTime] [varchar] (25) ,
[EndMonth] [int] ,
[EndDay] [int] ,
[EndYear] [int] ,
[ReoccurType] [int] (1 - Daily, 2 - Weekly),
[WeeklyDay] [int] ,(1- Sunday, 2-Monday)
[MonthlyDay] [int] , (Used for specific day every month)
[MonthlyFreq] [int] , (Used to specify the month)
[MonthlyDayFreq] [int] ,(1- Sunday, 2-Monday)
[YearlyDay] [int] ,(Like BirthDay)
[YearlyMonth] [int] ,(Like BirthMonth)
[intReoccurCount] [int] ,(Storing # of times to occur)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Carl Federl One Orange Chip Points: 25384 More actions · Answer 1

The calculation is relatively simple:

Take the day of the month for the date and divide by seven (as this is integer calculations, truncation will occur) to get the number of weeks since the first of the month, then if the day of the week of the date is greater than or equal to the day of the week of the first day of the date's month and year, then add 1.

Whew !! the SQL is easier to understand.

Since November 2005 began on a Tuesday, then November 28th is the 4th Monday and November 29th is the fifth Tuesday.

select MyDate

, DATENAME ( dw,MyDate ) as MyDate_DOW

, ( DAY(MyDate) / 7 )

+ CASE when DATEPART (dw,MyDate) >= DATEPART (dw, CAST((CONVERT(char(6),MyDate,112)+'01') as datetime ) ) then 1 else 0 end

as OccuranceWithinMonth

from (select CAST( '20051101' as datetime ) union all

select CAST( '20051102' as datetime ) union all

select CAST( '20051103' as datetime ) union all

select CAST( '20051104' as datetime ) union all

select CAST( '20051105' as datetime ) union all

select CAST( '20051106' as datetime ) union all

select CAST( '20051107' as datetime ) union all

select CAST( '20051108' as datetime ) union all

select CAST( '20051109' as datetime ) union all

select CAST( '20051110' as datetime ) union all

select CAST( '20051111' as datetime ) union all

select CAST( '20051112' as datetime ) union all

select CAST( '20051113' as datetime ) union all

select CAST( '20051114' as datetime ) union all

select CAST( '20051115' as datetime ) union all

select CAST( '20051116' as datetime ) union all

select CAST( '20051117' as datetime ) union all

select CAST( '20051118' as datetime ) union all

select CAST( '20051119' as datetime ) union all

select CAST( '20051120' as datetime ) union all

select CAST( '20051121' as datetime ) union all

select CAST( '20051122' as datetime ) union all

select CAST( '20051123' as datetime ) union all

select CAST( '20051124' as datetime ) union all

select CAST( '20051125' as datetime ) union all

select CAST( '20051126' as datetime ) union all

select CAST( '20051127' as datetime ) union all

select CAST( '20051128' as datetime ) union all

select CAST( '20051129' as datetime ) union all

select CAST( '20051130' as datetime ) ) as D (MyDate)

SQL = Scarcely Qualifies as a Language

Brian Munier SSCrazy Points: 2875 More actions · Answer 2

The SQL looks good except every Suday is one digit behind. I am not sure what the solution is to that little glitch.

I also tried running it for december and the numbers were very much off.

I think the logic is sound, taking the day of the month and dividing by 7

I figured out the problem. It was the case statement, basically if the Day of the month Modulo 7 = 0 then add 0, else add 1 My new code, it works for Nov and Dec 2005, I didn't check others is:

select MyDate

, DATENAME ( dw,MyDate ) as MyDate_DOW

, ( DAY(MyDate) / 7 )

+ CASE when DAY(MyDate) % 7=0 then 0 else 1 end

as OccuranceWithinMonth

from (select CAST( '20051201' as datetime ) union all

select CAST( '20051202' as datetime ) union all

select CAST( '20051203' as datetime ) union all

select CAST( '20051204' as datetime ) union all

select CAST( '20051205' as datetime ) union all

select CAST( '20051206' as datetime ) union all

select CAST( '20051207' as datetime ) union all

select CAST( '20051208' as datetime ) union all

select CAST( '20051209' as datetime ) union all

select CAST( '20051210' as datetime ) union all

select CAST( '20051211' as datetime ) union all

select CAST( '20051212' as datetime ) union all

select CAST( '20051213' as datetime ) union all

select CAST( '20051214' as datetime ) union all

select CAST( '20051215' as datetime ) union all

select CAST( '20051216' as datetime ) union all

select CAST( '20051217' as datetime ) union all

select CAST( '20051218' as datetime ) union all

select CAST( '20051219' as datetime ) union all

select CAST( '20051220' as datetime ) union all

select CAST( '20051221' as datetime ) union all

select CAST( '20051222' as datetime ) union all

select CAST( '20051223' as datetime ) union all

select CAST( '20051224' as datetime ) union all

select CAST( '20051225' as datetime ) union all

select CAST( '20051226' as datetime ) union all

select CAST( '20051227' as datetime ) union all

select CAST( '20051228' as datetime ) union all

select CAST( '20051229' as datetime ) union all

select CAST( '20051230' as datetime ) ) as D (MyDate)

Carl Federl One Orange Chip Points: 25384 More actions · Answer 3

Good catch of the bad algorithm and the elegant solution.

SQL = Scarcely Qualifies as a Language