Date of First Day of Week - 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 2005 Forums

Transact-SQL (2005)

Date of First Day of Week

Author

Topic

osupratt
Posting Yak Master

238 Posts

Posted - 2008-05-26 : 09:55:36

I'm wanting to see if there is a simpler way to accomplish the retrival of the first day of the week Date. What I currently have is:

CASE
WHEN DATEPART(DW, GETDATE()) = 1 THEN DATEADD([D], - 7, GETDATE())
WHEN DATEPART(DW, GETDATE()) = 2 THEN DATEADD([D], - 8, GETDATE())
WHEN DATEPART(DW, GETDATE()) = 3 THEN DATEADD([D], - 9, GETDATE())
WHEN DATEPART(DW, GETDATE()) = 4 THEN DATEADD([D], - 10, GETDATE())
WHEN DATEPART(DW, GETDATE()) = 5 THEN DATEADD([D], - 11, GETDATE())
WHEN DATEPART(DW, GETDATE()) = 6 THEN DATEADD([D], - 12, GETDATE())
WHEN DATEPART(DW, GETDATE()) = 7 THEN DATEADD([D], - 13, GETDATE()) END AS StartWeek1

I'm trying to figure if there might be an easier way to code this. Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-26 : 09:59:34

SELECT DATEADD(dd,-1* (DATEPART(dw,GETDATE())+1),GETDATE())

osupratt
Posting Yak Master

238 Posts

Posted - 2008-05-26 : 10:13:53

OK. Thanks. Looks good. Would you know how to transform this code and put it in Visual Studio 2005? I also need to use this in a column header. I get an error when I use this and change out GETDATE() with NOW().

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-26 : 10:16:29

SELECT DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000101')

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-26 : 10:21:25

quote:
Originally posted by osupratt

OK. Thanks. Looks good. Would you know how to transform this code and put it in Visual Studio 2005? I also need to use this in a column header. I get an error when I use this and change out GETDATE() with NOW().

Did you mean reporting services? then use like this

=DATEADD("d",-1* (DATEPART("dw",NOW()+1),NOW())

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-26 : 10:26:15

Try this and you will notice that almost everything related to WEEK in SQL Server is relying on your current regional setting which may differ from what other people are having.

DECLARE	@DF INT

-- Get current datefirst setting
SET	@DF = @@DATEFIRST

-- Try different regional setting
SET DATEFIRST 3

SELECT	DATEADD(dd,-1* (DATEPART(dw,GETDATE())+1),GETDATE()) AS Visakh16,
	DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000101') AS Peso

-- Try different regional setting
SET DATEFIRST 5

SELECT	DATEADD(dd,-1* (DATEPART(dw,GETDATE())+1),GETDATE()) AS Visakh16,
	DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000101') AS Peso

-- Restore current datefirst setting
SET DATEFIRST @DF

E 12°55'05.25"
N 56°04'39.16"

osupratt
Posting Yak Master

238 Posts

Posted - 2008-05-26 : 10:35:22

One of these codes gives me a date of 5/23/08 and the other gives me the date of 5/26/08.

I need this code to return the first day of the week (Sunday) for whatever the date the query is run. So if it is run on Friday (5/23/08), I need the code to bring back the date of Sunday (5/18/08).

Thanks for your help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-26 : 10:38:16

[code]DECLARE @DF INT

-- Get current datefirst setting
SET @DF = @@DATEFIRST

-- Try different regional setting
SET DATEFIRST 3

SELECT DATEADD(dd,-1* (DATEPART(dw,GETDATE())+1),GETDATE()) AS Visakh16,
DATEADD(DAY, DATEDIFF(DAY, '18991231', GETDATE()) / 7 * 7, '18991231') AS Peso

-- Try different regional setting
SET DATEFIRST 5

SELECT DATEADD(dd,-1* (DATEPART(dw,GETDATE())+1),GETDATE()) AS Visakh16,
DATEADD(DAY, DATEDIFF(DAY, '18991231', GETDATE()) / 7 * 7, '18991231') AS Peso

-- Restore current datefirst setting
SET DATEFIRST @DF[/code]

E 12°55'05.25"
N 56°04'39.16"

osupratt
Posting Yak Master

238 Posts

Posted - 2008-05-26 : 10:42:47

Thanks Peso. You have a way of making things easy to understand for someone who is trying to learn. I appreciate it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources