Get last Sundays and next Saturdays 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

General SQL Server Forums

New to SQL Server Programming

Get last Sundays and next Saturdays date

Author

Topic

rjackman1959
Yak Posting Veteran

60 Posts

Posted - 2009-01-26 : 20:55:38

Is there a way I can take todays date and then get the dates for last Sunday and next Saturday ?

I want to run a report that will look at orders in this date range, but I do not want to input the dates. If today is 2009-01-26 I want 2009-01-25 and 2009-01-31 as @date1 and @date2

declare @date1 as char(10), @date2 as char(10)
set @date1 = ?

select * from orders where orderdate between @date1 and @date2

Thanks in advance for any help

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-01-26 : 23:02:16

[code]set @date1=dateadd(day,datediff(day,0,getdate())-(datepart(dw,getdate())+@@datefirst-1)%7,0); -- sunday
set @date2=dateadd(day,datediff(day,0,getdate())-(datepart(dw,getdate())+@@datefirst-1)%7,6); -- saturday[/code]

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-26 : 23:06:23

SELECT DATEADD(DD,-(DATEPART(DW,GETDATE())-1),GETDATE())

SELECT DATEADD(DD,-(DATEPART(DW,GETDATE())-7),GETDATE())

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-26 : 23:28:27

[code]
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)-1 AS WeekStart,
DATEADD(wk,DATEDIFF(wk,0,GETDATE())+1,0)-2 AS WeekEnd[/code]
provided your datefirst setting is us default

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-26 : 23:48:14

select dateadd(d,-datepart(dw,getdate())+1,getdate()) as 'weekstart',
dateadd(d,-datepart(dw,getdate()),dateadd(ww,1,getdate())) as 'weekend'

Jai Krishna

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-27 : 00:05:54

Somewhat shorter and not dependant on server settings either

SELECT	GETDATE() AS [Now],
	DATEADD(DAY, DATEDIFF(DAY, -1, GETDATE()) / 7 * 7, -1) AS [Last sunday],
	DATEADD(DAY, DATEDIFF(DAY, -1, GETDATE()) / 7 * 7, 5) AS [Next saturday]

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

rjackman1959
Yak Posting Veteran

60 Posts

Posted - 2009-01-27 : 08:38:54

Thanks to all
This is close to what I need, but I need it in a regular timestamp format so I can use it in a between statement comapring it to another date.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-27 : 11:10:28

What is "regular timestamp" format?

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

rjackman1959
Yak Posting Veteran

60 Posts

Posted - 2009-01-27 : 11:26:00

2009-01-26 14:31:00.000

rjackman1959
Yak Posting Veteran

60 Posts

Posted - 2009-01-27 : 11:28:01

I think this might work
DECLARE @day INT
DECLARE @today SMALLDATETIME
SET @today = CAST(CONVERT(VARCHAR(10), GETDATE(), 101) AS SMALLDATETIME)
SET @day = DATEPART(dw, @today)

between DATEADD(dd, 1 - @day, @today) and DATEADD(dd, 7 - @day, @today)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-27 : 12:48:19

Will not work.
Have you even tried the suggested methods?

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-27 : 12:49:24

quote:
Originally posted by rjackman1959

I think this might work
DECLARE @day INT
DECLARE @today SMALLDATETIME
SET @today = CAST(CONVERT(VARCHAR(10), GETDATE(), 101) AS SMALLDATETIME)
SET @day = DATEPART(dw, @today)

between DATEADD(dd, 1 - @day, @today) and DATEADD(dd, 7 - @day, @today)

why casting to varchar? whats the problem with peso's suggestion?

rjackman1959
Yak Posting Veteran

60 Posts

Posted - 2009-01-27 : 13:25:49

Actually it did work I was just confused on how to use it. Sorry Peso I did not mean any disrespect you are always a big help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources