November 28, 2005 at 5:36 pm
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)
November 28, 2005 at 7:02 pm
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
November 29, 2005 at 7:40 am
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)
November 29, 2005 at 4:15 pm
Good catch of the bad algorithm and the elegant solution.
SQL = Scarcely Qualifies as a Language
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply