Help Needed - SQL Server Forums

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

SQL Server Development (2000)

Help Needed

Author

Topic

tharik
Starting Member

2 Posts

Posted - 2004-11-22 : 09:29:45

Eg if day is 22-11-2004 , i need 4 week of the month.
Eg if day is 01-11-2004 , i need 1st week of the month.

Thanks in Advance

nr
SQLTeam MVY

12543 Posts

Posted - 2004-11-22 : 09:32:19

??? what do you mean?

You want the start and end date of the week of the month that the date is in?
What is a week? 7 days from start of month? Sun to Sat where first week of month can be a part week?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tharik
Starting Member

2 Posts

Posted - 2004-11-22 : 09:38:20

i need the week of the month.
either 1st week or second or third week or fourth week.

quote:
Originally posted by nr

??? what do you mean?

You want the start and end date of the week of the month that the date is in?
What is a week? 7 days from start of month? Sun to Sat where first week of month can be a part week?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-11-22 : 09:42:38

DECLARE @dt datetime
SET @dt='11/22/2004'
SELECT DateDiff(wk, DateAdd(month, DateDiff(month, 0, @dt), 0), @dt)+1 AS WeekOfMonth

That should do it.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-22 : 09:44:24

would this work?


declare @date1 datetime, @date2 datetime
select @date1 = '20041130', @date2 = '20041101'

select 	datepart(wk, @date1)%month(@date1),
	datepart(wk, @date2)%month(@date2)

Go with the flow & have fun! Else fight the flow

nr
SQLTeam MVY

12543 Posts

Posted - 2004-11-22 : 09:49:34

maybe
datepart(dd,@d) / 7 + 1

is 31 Aug 2004 the 5th week or 6th week

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-22 : 09:51:57

well mine doesn't work...

Go with the flow & have fun! Else fight the flow

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-22 : 10:03:29

nr: 6th week

Go with the flow & have fun! Else fight the flow

nr
SQLTeam MVY

12543 Posts

Posted - 2004-11-22 : 10:45:19

Then like Rob's or
select datediff(ww,dateadd(dd,datepart(dd,@d)*-1+1,@d), @d)+1

Subtract the odd 1 for week start day's.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources