get first and last day of a week

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)

get first and last day of a week

Author

Topic

clboren
Starting Member

1 Post

Posted - 2005-06-02 : 18:35:02

I am looking for the syntax on how to return the first and last day of the week based on a date. For example, if the date in my table is 5/13/2005, I know I can use DATEPART(week, '5/13/2005') to return the week number (20). How then can I use that week number to return the first and last day of that week (5/08/2005 and 5/14/2005)?

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-02 : 18:59:54

Here's what I came up with:



declare @d datetime
declare @numbers table (n int)

set @d = '5/13/2005'

insert into @numbers(n)
select 0 union all
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select -1 union all
select -2 union all
select -3 union all
select -4 union all
select -5 union all
select -6

select min(d) AS WeekBegin, max(d) AS WeekEnd
from
(
	select dateadd(d, n, @d) as d, datepart(week, dateadd(d, n, @d)) as w
	from @numbers
) t
where datepart(week, @d) = w

You might want to consider making the numbers table a permanent table so you don't have to build it each time.

EDIT: modified the numbers table for the values that we need.

Let me know if you find a date that doesn't work.

Tara

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-06-02 : 23:59:10

Or, you can give this a try too:

SELECT DATEADD(wk, DATEDIFF(wk, 6, '5/13/2005'), 6)
SELECT DATEADD(wk, DATEDIFF(wk, 5, '5/13/2005'), 5)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-06-03 : 00:04:23

You could use the Start of Week Function, F_START_OF_WEEK, in this topic to get the first day of the week:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307

Then add six days to that with the dateadd function to get the last day of the week.


select
	START_DATE = dbo.F_START_OF_WEEK('2005/5/13',1),
	END_DATE   = dateadd(dd,6,dbo.F_START_OF_WEEK('2005/5/13',1))



START_DATE               END_DATE
------------------------ -------------------------
2005-05-08 00:00:00.000  2005-05-14 00:00:00.000

(1 row(s) affected)

CODO ERGO SUM

walterlee78
Starting Member

4 Posts

Posted - 2005-07-25 : 10:53:28

quote:
Originally posted by nosepicker

Or, you can give this a try too:

SELECT DATEADD(wk, DATEDIFF(wk, 6, '5/13/2005'), 6)
SELECT DATEADD(wk, DATEDIFF(wk, 5, '5/13/2005'), 5)

This solution is great. Any ideas on how it works?

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-07-25 : 10:59:03

Is there any way we can also get the last date of the month?

Complicated things can be done by simple thinking

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-07-25 : 11:13:33

last date:

Declare @myDate datetime
Set @myDate = '5/13/2005'

Select dateadd(m,1,@myDate)-day(@myDate)

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-07-25 : 11:49:00

Sorry Corey, but that method doesn't work for some dates (try '1/30/2005' for example). Try this instead:

SELECT DATEADD(m, DATEDIFF(m, 30, @myDate), 30)

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-07-25 : 13:11:01

quote:
Originally posted by nosepicker

Sorry Corey, but that method doesn't work for some dates (try '1/30/2005' for example). Try this instead:

SELECT DATEADD(m, DATEDIFF(m, 30, @myDate), 30)

oops... wrong order...

Declare @myDate datetime
Set @myDate = '5/13/2005'

Select dateadd(m,1,@myDate-day(@myDate))

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-07-25 : 17:23:45

Unfortunately, I believe your revised SQL will only work if the current month has fewer days than the previous month. For example, it will work if @myDate is in April, but it won't work if it is in May.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-07-25 : 17:34:51

*sigh*

rough day apparently...


Declare @myDate datetime

Set @myDate = '1/30/2005'
Select dateadd(m,1,@myDate)-day(dateadd(m,1,@myDate))

Set @myDate = '4/13/2005'
Select dateadd(m,1,@myDate)-day(dateadd(m,1,@myDate))

Set @myDate = '5/13/2005'
Select dateadd(m,1,@myDate)-day(dateadd(m,1,@myDate))

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-07-25 : 18:45:07

That looks pretty good now

Sorry, didn't mean to cause you so much grief on a Monday.
I've learned more from you on this forum than probably anybody else, so I certainly don't want to show any lack of respect.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-07-25 : 23:27:48

quote:
Originally posted by nosepicker

That looks pretty good now
Sorry, didn't mean to cause you so much grief on a Monday.
I've learned more from you on this forum than probably anybody else, so I certainly don't want to show any lack of respect.

... I'm embarrassed

Glad to hear I've been of help

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-26 : 02:19:08

No doubt. Corey is always helpful in T-SQL

Madhivanan

Failing to plan is Planning to fail

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-07-26 : 03:34:16

I agree to Madhivanan

Thanks Corey

Complicated things can be done by simple thinking

danol21
Starting Member

2 Posts

Posted - 2006-11-01 : 07:18:30

Hi all,

i need a slight variation on the orginal question at top.

I need to enter a week number and it to return the start date and end date of that week.

Is this possible? If so . . .how???

thanks in advance :-)

danol

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-01 : 07:21:37

Make use of Michael Valentine Jones excellent function F_TABLE_DATE found here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519
But first you have to decide "what is a week".

Peter Larsson
Helsingborg, Sweden

danol21
Starting Member

2 Posts

Posted - 2006-11-01 : 08:26:52

Spot on, works like a dream, thanks to one and all :-)

danol

X002548
Not Just a Number

15586 Posts

Posted - 2006-11-01 : 10:15:09

Hey, give this a read

http://weblogs.sqlteam.com/brettk/archive/2005/06/02/5528.aspx

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

djha
Starting Member

1 Post

Posted - 2010-04-01 : 07:25:22

to get the first day of the week you can use the below simple statement.

select dateadd(d,2-datepart(dw,getdate()),getdate())

I have assumed monday to be the first day. It will always return the monday of anydate used in place of getdate(). For the last day of the week you can add + 6 to the above date found.

If you want to use another day as your first, you can use other digit in place of 2 above. try the permutations, and you will get it.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-04-01 : 16:30:35

quote:
Originally posted by djha

to get the first day of the week you can use the below simple statement.

select dateadd(d,2-datepart(dw,getdate()),getdate())

I have assumed monday to be the first day. It will always return the monday of anydate used in place of getdate(). For the last day of the week you can add + 6 to the above date found.

If you want to use another day as your first, you can use other digit in place of 2 above. try the permutations, and you will get it.

That statement will return different results, depending on the setting of datefirst. Also, it includes the time portion of the datetime. See the results below.


set nocount on
set datefirst 1
select x1=dateadd(d,2-datepart(dw,getdate()),getdate())
set datefirst 2
select x2=dateadd(d,2-datepart(dw,getdate()),getdate())
set datefirst 3
select x3=dateadd(d,2-datepart(dw,getdate()),getdate())
set datefirst 4
select x4=dateadd(d,2-datepart(dw,getdate()),getdate())
set datefirst 5
select x5=dateadd(d,2-datepart(dw,getdate()),getdate())
set datefirst 6
select x6=dateadd(d,2-datepart(dw,getdate()),getdate())
set datefirst 7
select x7=dateadd(d,2-datepart(dw,getdate()),getdate())

Results:


x1
-----------------------
2010-03-30 16:26:04.343

x2
-----------------------
2010-03-31 16:26:04.343

x3
----------------------- 
2010-04-01 16:26:04.343

x4
----------------------- 
2010-04-02 16:26:04.343

x5
-----------------------
2010-03-27 16:26:04.343

x6
----------------------- 
2010-03-28 16:26:04.343

x7
-----------------------
2010-03-29 16:26:04.343

CODO ERGO SUM

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources