Date converted to 35 day calendar 2

[shaunhubbs]

Hi all,

I am working with operating room statistics and need to convert a given date to it's position on a 35 day calendar (as operating room blocks are assigned based on this type of calendar for a given period). The 35 day calendar starts on a Sunday in Week 1 (day 1) and goes to Saturday in Week 5 (day 35) for each month (therefore each month does not have all 35 days -- Feb. this year starts on day 4 and goes to day 31).

Example: If I am given a date such as today's (Feb. 27, 2006) I want to know what day it is (I know it's day 30 if I look at my computer's calendar).

Does anyone know how to derive this "day" from a given date?

Thanks to all in advance,
Shaun

 

[gmmastros]

This????

Declare @TestDate DateTime
Set @TestDate = '2006-02-28'

Select DatePart(Weekday, DateAdd(Day, -day(@TestDate) + 1, @TestDate)) + Day(@TestDate) - 1

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mhoyt]

Wow, that is cool. So cool I thought it was wrong several times, but have now reread the question enough to think I finally understand it and George's solution seems to work.

George, would you mind explaining HOW it works? What is the logic there?

TIA Mike

 

[gmmastros]

mhoyt

First, thanks for the star.

Here's my logic...

This part returns first day of month
DateAdd(Day, -day(@TestDate) + 1, @TestDate)

I then get the weekday for the first day of the month.
DatePart(Weekday, [Previous Stuff])

So, for this month (feb 2006), this returns 4. Since the day number for 2006-02-27 is:
Select Day('1006-02-27') -- = 27

We can wrap the whole thing up because 27 + 4 - 1 = 30

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Now I am confused. Because I thought that the '35 day calendar' wouldn't have any days beyond the 35. However, using my code, it is possible to have a 'day 36'. Run this in Query Analyzer, and you'll see that the last day in 2006 (Dec 31st) is, in fact day 36.

Declare @Test Table(TheDate DateTime)
Declare @i DateTime
Set @i = '2006-01-01'
While @i <= '2006-12-31'
	Begin
		Insert Into @Test Values(@i)
		Set @i = @i + 1
	End

Select 	TheDate, 
		DateAdd(Day, -day(TheDate) + 1, TheDate) As FirstDay,
		DatePart(Weekday, DateAdd(Day, -day(TheDate) + 1, TheDate)) + Day(TheDate) - 1 As DateOf35Cal
From @Test

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mhoyt]

So is April 30th. Happens because the 1st falls so late in the week (Sat for April 2006, Fri for Dec, But Dec has more days...). Maybe the first poster can clarify the rules?

 

[shaunhubbs]

First, thanks to George for the fantastic solution. It works very well for my application, despite there being the possibility of a 36th or 37th day (if a 31 day month begins on a Saturday).

The rules are simply that when we enter our operating room block schedule there are 35 days in a month. For the report that I am building anything after 35 would have to flip back over and begin again at 1. So a simple if then will correct the 36th and 37th days to become the 1st and 2nd days (as that's how they would be reading our block schedule).

Again, thanks for the responses and the solution from George.

Shaun