Dates? Weekly range for any given date.

Question

Post reply

Dates? Weekly range for any given date.

John Kress

SSC-Addicted

Points: 400
More actions
May 21, 2004 at 12:11 pm

#84422

I'm attempting to find the date range of any given week on a calendar. I need the first date and last date of a week, based on a date, which could be any date value.
Knowing that the first day of the week is Sunday, is there any function that exisits to accomplish this task?
Any help would be appreciated!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

SQLBill SSC Guru Points: 51440 More actions · Answer 1

Try this:

DECLARE @mydate VARCHAR(10)

SET @mydate = '2004-05-21'

SELECT GETDATE() - (DATEPART(DW, @mydate) -1) AS Sunday,

GETDATE() + (7 - (DATEPART(DW, @mydate))) AS Saturday

-SQLBill

John Kress SSC-Addicted Points: 400 More actions · Answer 2

That got me on the right track!

I modified it to the following:

DECLARE @mydate VARCHAR(10)

SET @mydate = '8/1/2003'

SELECT convert(dateTime,@mydate) - (DATEPART(DW, @mydate) -1) AS Sunday,

convert(dateTime,@mydate) + (7 - (DATEPART(DW, @mydate))) AS Saturday

The intent is to make the date @mydate any given date. The previous would always return the week range for the current date, because of the GETDATE() function.

Thanks SQLBill!

SQLBill SSC Guru Points: 51440 More actions · Answer 3

DOH! I can't believe I missed that. Thanks for catching and figuring it out.

I'm glad I was able to point you in the right direction.

-SQLBill

Max Kash SSC Rookie Points: 28 More actions · Answer 4

Sorry if this is redundant but here goes:

I would like to use a query like this to return all rows where date field falls into "Last full week".. i.e. if the query runs today through saturday, it will return all rows from 1/23 to 1/29; next sunday, the query would return rows from 1/30 to 2/5.

Thanks in advance!

~Max

Max Kash SSC Rookie Points: 28 More actions · Answer 5

Please disregard, I found my answer on this site:

http://www.sqlservercentral.com/scripts/contributions/865.asp

Thanks!