getting range of date in a week for a certain date

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)

getting range of date in a week for a certain date

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-02-09 : 09:11:00

say i have some dat
and i want to get the date range for that date (i mean the range date from sundat to friday - how do i do that?
thnaks i nadvance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-09 : 09:13:55

which range date from sunday to friday ? Can you explain more ? Also post your table structure, sample data & result

----------------------------------
'KH'
everything that has a beginning has an end

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-09 : 09:20:37

Do you need something like this?
http://weblogs.sqlteam.com/brettk/archive/2005/05/12/5139.aspx

Madhivanan

Failing to plan is Planning to fail

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-02-09 : 10:00:02

Is this what u r looking for


Declare @dDate datetime
Set @dDate = getdate()
Declare @mon datetime, @tue  datetime, @wed  datetime, @thu  datetime, @fri  datetime, @wkday int

Set @wkday = datepart(dw, @dDate)

Set @mon =  (Select @dDate - @wkday + 1)
Set @tue =  (Select @dDate - @wkday + 2)
Set @wed =  (Select @dDate - @wkday + 3)
Set @thu =  (Select @dDate - @wkday + 4)
Set @fri =  (Select @dDate - @wkday + 5)

Select	@mon Union All
Select	@tue Union All
Select	@wed Union All
Select	@thu Union All
Select	@fri 

End

Note: This is not working correctly if the date given is a sunday or saturday. Also this may need to be modified according to ur requirement

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-09 : 10:01:13

I think this is what you are asking for.
This generates dates for the previous Sunday and the following Friday based on a given date. The red part is what does the calculation


--make sure datefirst is set to default: 7
set datefirst 7

--display the formatted dates and weekdays
select	convert(varchar,dt,101)			as myDate
	,convert(varchar,startDt,101)		as startDate
	,datename(weekday, startDt)		as startDay
	,convert(varchar,endDt,101)		as endDate
	,datename(weekday, endDt)		as endDay
from	
	(--calculate the start and endDates
	select	dt
		,startDt = dateadd(day,-1*(datepart(weekday,dt)-1),dt)
		,endDt = dateadd(day, @@datefirst-datepart(weekday,dt)-1,dt)
	from	
		(--generate some test dates
		select	dateadd(day, n, getdate()) as dt
		from	(
			select 0 n union select 1 union select 2 union select 3 
			union select 4 union select 5 union select 6 union select 7
			union select 8 union select 9 union select 10 union select 11
			) numbers
		) dates
	) caledDates

Be One with the Optimizer
TG

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-02-09 : 10:39:34

Or using a function as follows

Alter function FindAllDaysOfWeek(@dDate datetime) 
RETURNS @TmpTbl table(rDate datetime) as
Begin

Set @wkday = datepart(dw, @dDate)

Insert Into @TmpTbl
Select	(Select @dDate - @wkday + 1) Union All
Select	(Select @dDate - @wkday + 2) Union All
Select	(Select @dDate - @wkday + 3) Union All
Select	(Select @dDate - @wkday + 4) Union All
Select	(Select @dDate - @wkday + 5) 

return 

End

Usage :

Select * from FindAllDaysOfWeek('02/07/2005')

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-02-09 : 12:14:50

Another variation:


select
	Date = Sunday + offset
from
	(Select Sunday =
	dateadd(dd,(datediff(dd,-53684,getdate())/7)*7,-53684)
	) a
	cross join
	(
	select offset = 0 union all
	select offset = 1 union all
	select offset = 2 union all
	select offset = 3 union all
	select offset = 4 union all
	select offset = 5
	) b


Date                                                   
------------------------------------------------------ 
2006-02-05 00:00:00.000
2006-02-06 00:00:00.000
2006-02-07 00:00:00.000
2006-02-08 00:00:00.000
2006-02-09 00:00:00.000
2006-02-10 00:00:00.000

(6 row(s) affected)

CODO ERGO SUM

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2006-02-09 : 14:38:22

If by "date range", you mean that you just want the beginning and ending dates (Sunday and Friday) of that range, then you can do this:

SELECT DATEADD(wk, DATEDIFF(wk, 6, getdate()), 6)
SELECT DATEADD(wk, DATEDIFF(wk, 4, getdate()), 4)

Result:
2006-02-05 00:00:00.000
2006-02-10 00:00:00.000

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-02-13 : 02:06:09

first thnaks alot all of u!!!
second the code that "nosepicker" gave me is the most simple
and execlly the one i didnt if i idnt explain correctly i appologize in advance)

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-03-05 : 04:10:22

nosepicker :
can u please explain more about the code your did :

DATEADD(wk, DATEDIFF(wk, 6, getdate()), 6)

thnaks i nadvance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2006-03-06 : 12:09:17

Sure. "6" is just the date Jan. 7, 1900, converted to integer. Since that day is a Sunday, when you do this:

DATEADD(wk, DATEDIFF(wk, 6, getdate()), 6)

the inner part "DATEDIFF(wk, 6, getdate())" first calculates the number of weeks between Jan. 7, 1900 and today. The "DATEADD" part then adds this number of weeks to Jan. 7, 1900. Since Jan. 7, 1900 is a Sunday, the result will always be a Sunday. In this case, it will always be the Sunday in the same week as today. I could've picked any other date besides Jan. 7, 1900 that fell on a Sunday, but "6" is quicker to type, and it ensures that occurs before any other date you might want to use besides getdate().

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-10-22 : 03:10:30

nosepicker
hwo can i use your code if i have a given weeknumber in the yaer what chnage do i have to make to do it?
thnaks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-22 : 20:44:52

quote:
Originally posted by pelegk2

nosepicker
hwo can i use your code if i have a given weeknumber in the yaer what chnage do i have to make to do it?
thnaks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

This really doesn't seem to have much to do with this old topic.

I think it would be better if you posted a new topic, and explain exactly what it is that you want to do.

In particular, you need to explain what you mean by week number, because the meaning of that varies from one organization to the next.

CODO ERGO SUM

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-12-17 : 05:06:37

how can i do the same for a given week number
for example if today is week 53 then i should get the range of dates (for sunday to firday : )
31/12/2006->05/12/2006

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-12-17 : 07:22:49

I see you are posting on this topic again, but you still didn't respond to what I asked you 2 months ago:
"you need to explain what you mean by week number, because the meaning of that varies from one organization to the next."

CODO ERGO SUM

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-12-17 : 15:46:04

week number is the week number that you get when you use the function :
DATEPART(wk,GETDATE())

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources