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
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

Ness
Starting Member

1 Posts

Posted - 07/30/2010 :  08:17:41  Show Profile  Reply with Quote
Not sure if this will help anyone but I use this to get the last day of the week of a given date:

--Replace the Getdate() with your date
SET DATEFIRST 1
select
CASE datepart(weekday,getdate())
WHEN 1 THEN CONVERT(VARCHAR(20),(GETDATE()+6),103)
WHEN 2 THEN CONVERT(VARCHAR(20),(GETDATE()+5),103)
WHEN 3 THEN CONVERT(VARCHAR(20),(GETDATE()+4),103)
WHEN 4 THEN CONVERT(VARCHAR(20),(GETDATE()+3),103)
WHEN 5 THEN CONVERT(VARCHAR(20),(GETDATE()+2),103)
WHEN 6 THEN CONVERT(VARCHAR(20),(GETDATE()+1),103)
WHEN 7 THEN CONVERT(VARCHAR(20),(GETDATE()),103)
END [End Date]
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30188 Posts

Posted - 07/30/2010 :  14:57:16  Show Profile  Visit SwePeso's Homepage  Reply with Quote
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) / 7 * 7, 6)


N 56°04'39.26"
E 12°55'05.63"

Edited by - SwePeso on 07/30/2010 14:58:36
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 05/06/2011 :  09:21:21  Show Profile  Reply with Quote
quote:
Originally posted by jesuraja

SELECT DATEADD(DAY,-(DATEPART(WEEKDAY,GETDATE()))+@@DATEFIRST,GETDATE()) AS WeekStartDate,
DATEADD(DAY,7-(DATEPART(WEEKDAY,GETDATE())),GETDATE()) AS WeekEndDate

Output
======

WeekStartDate WeekEndDate
----------------------- -----------------------
2011-05-07 16:30:41.310 2011-05-07 16:30:41.310


Jesuraja I



Your week starts and ends on the same date.

If you're going to bump an old thread, at least post a correct answer.





CODO ERGO SUM
Go to Top of Page

jesuraja
Starting Member

India
3 Posts

Posted - 05/07/2011 :  05:16:26  Show Profile  Reply with Quote
SET DATEFIRST 1 -- Set First Weekday as Monday
DECLARE @Date DATETIME
SET @Date = GETDATE()
-- First Weekday is 'Monday' and last Weekday is 'Sunday'
SELECT @Date AS ActualDate,
DATEADD(DAY,@@DATEFIRST-DATEPART(WEEKDAY,@Date),@Date) AS WeekStartDate,
DATEADD(DAY,7-DATEPART(WEEKDAY,@Date),@Date) AS WeekEndDate

Output
======

ActualDate WeekStartDate WeekEndDate
----------------------- ----------------------- -----------------------
2011-05-07 11:43:38.890 2011-05-02 11:43:38.890 2011-05-08 11:43:38.890


Jesuraja I

Edited by - jesuraja on 05/07/2011 07:51:18
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30188 Posts

Posted - 05/08/2011 :  04:43:04  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Set datefirst will force a recompile when your query runs.
Are you sure you want that?


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

Reporter
Starting Member

Russia
48 Posts

Posted - 09/30/2011 :  02:13:18  Show Profile  Reply with Quote
sql server 2008

select datename(weekday,'5/13/2005')
Go to Top of Page

Fromper
Starting Member

USA
30 Posts

Posted - 02/02/2012 :  15:22:07  Show Profile  Reply with Quote
I just wanted to say thanks for the useful thread. I found what I needed here.
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous 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.09 seconds. Powered By: Snitz Forums 2000