SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 get first and last day of a week
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

clboren
Starting Member

1 Posts

Posted - 06/02/2005 :  18:35:02  Show Profile  Reply with Quote
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

USA
35940 Posts

Posted - 06/02/2005 :  18:59:54  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Edited by - tkizer on 06/02/2005 19:12:43
Go to Top of Page

nosepicker
Constraint Violating Yak Guru

USA
366 Posts

Posted - 06/02/2005 :  23:59:10  Show Profile  Reply with Quote
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)

Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 06/03/2005 :  00:04:23  Show Profile  Reply with Quote
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
Go to Top of Page

walterlee78
Starting Member

4 Posts

Posted - 07/25/2005 :  10:53:28  Show Profile  Reply with Quote
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?
Go to Top of Page

chiragkhabaria
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 07/25/2005 :  10:59:03  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message  Reply with Quote
Is there any way we can also get the last date of the month?


Complicated things can be done by simple thinking
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 07/25/2005 :  11:13:33  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
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."
Go to Top of Page

nosepicker
Constraint Violating Yak Guru

USA
366 Posts

Posted - 07/25/2005 :  11:49:00  Show Profile  Reply with Quote
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)
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 07/25/2005 :  13:11:01  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
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."
Go to Top of Page

nosepicker
Constraint Violating Yak Guru

USA
366 Posts

Posted - 07/25/2005 :  17:23:45  Show Profile  Reply with Quote
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.
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 07/25/2005 :  17:34:51  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
*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."
Go to Top of Page

nosepicker
Constraint Violating Yak Guru

USA
366 Posts

Posted - 07/25/2005 :  18:45:07  Show Profile  Reply with Quote
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.
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 07/25/2005 :  23:27:48  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
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."
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 07/26/2005 :  02:19:08  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
No doubt. Corey is always helpful in T-SQL

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

chiragkhabaria
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 07/26/2005 :  03:34:16  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message  Reply with Quote
I agree to Madhivanan

Thanks Corey

Complicated things can be done by simple thinking
Go to Top of Page

danol21
Starting Member

2 Posts

Posted - 11/01/2006 :  07:18:30  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 11/01/2006 :  07:21:37  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Edited by - SwePeso on 11/01/2006 07:22:07
Go to Top of Page

danol21
Starting Member

2 Posts

Posted - 11/01/2006 :  08:26:52  Show Profile  Reply with Quote
Spot on, works like a dream, thanks to one and all :-)

danol
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 11/01/2006 :  10:15:09  Show Profile  Reply with Quote
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



Go to Top of Page

djha
Starting Member

India
1 Posts

Posted - 04/01/2010 :  07:25:22  Show Profile  Reply with Quote
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.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 04/01/2010 :  16:30:35  Show Profile  Reply with Quote
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

Edited by - Michael Valentine Jones on 04/01/2010 16:31:47
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000