Select X Day For X Week Between @d1 and @d2

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Select X Day For X Week Between @d1 and @d2

Author

Topic

djseng
Starting Member

25 Posts

Posted - 2005-12-05 : 12:51:23

I would like to write a function that returns, for example, the first wednesday of every month between @d1 and @d2 where @d1 and @d2 are smalldatetimes

CREATE FUNCTION dbo.udf_XDayInYWeekBetweenDateXAndDateY (@DateX smalldatetime, @DateY smalldatetime, @Wk int, @DoW int)
RETURNS @Dates TABLE (@date smalldatetime)

SELECT * FROM dbo.udf_XDayInYWeekBetweenDateXAndDateY ('2006-01-01', '2006-03-01', 1, 4)

would return

'2006-01-04 00:00:00'
'2006-02-01 00:00:00'

I'm currently working on a solution myself, but I thought if anyone had anything already done I could already have it done.

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-12-05 : 14:18:56

Without writing the logic to handle the dates between @d1 and @d2, here is a single statement to calculate the first Wednesday of a month, given the 1st of the month:

DECLARE @date datetime
SET @date = CONVERT(datetime, '2006-01-01')

SELECT DATEADD(wk, DATEDIFF(wk, 2, CASE WHEN DATEPART(dw, @date) IN (5, 6, 7) THEN DATEADD(w, 1, @date) ELSE @date END), 2)

djseng
Starting Member

25 Posts

Posted - 2005-12-05 : 14:51:26

Thank you much sir,

You saved me an undetermined amount of hours of work!


DECLARE
	@date smalldatetime,
	@wom int,
	@dow int

SELECT 
	@date = '2006-04-01 00:00:00',
	@wom = 1, -- Week Of Month
	@dow = 7 -- Day Of Week

SELECT 
	DATEADD(
		wk, 
		DATEDIFF(
			wk, 
			@dow - 2, 
			CASE 
				WHEN DATEPART(dw, @date) BETWEEN @dow + 1 AND 7 
					THEN DATEADD(wk, @wom, @date) 
					ELSE DATEADD(wk, @wom - 1, @date) 
			END), 
		@dow - 2)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources