Date Function to Return Weekly Date Range

Question

Date Function to Return Weekly Date Range

ian.proffer

SSC Rookie

Points: 39
More actions
January 7, 2010 at 10:04 pm

#156565

I am trying to write a function that will evaluate a datetime value and then assign a weekly date range to it. For example, my desired output would look something like this:
WeekData
1/3 – 1/9123
1/10-1/16456
1/17-1/23789

Viewing 9 posts - 1 through 8 (of 8 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Well, you showed us what the output should look like, but what are the inputs to the function?

What about the table structure (CREATE TABLE statement(s)), sample date (as a series of INSERT INTO statements) for the table(s), what code you have tried so far? Al that would help us help you.

ian.proffer SSC Rookie Points: 39 More actions · Answer 2

The input as I mentioned would be a datetime value. For each datetime value I want to determine what week range it belongs to, where a week starts on a Sunday. So for further example (I've cut off the time portion to keep it simple, since we don't care about it):

ServiceDate WeekRange

2010-01-06 1/3-1/9

2010-01-11 1/10-1/16

2010-01-05 1/3-1/9

2010-01-01 12/27-1/2

I just need to understand what combination of date functions or other coding to use to assign a WeekRange value to a ServiceDate datetime.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Sorry, still don't understand. What are the inputs used to generate the output desired. At this point I only have half the problem. Can you provide me what I asked for in my previous post?

Grinja Hall of Fame Points: 3590 More actions · Answer 4

You can use the DatePart function to divide portions of the date but I don't believe you will be able to get the range from any built in function.

http://msdn.microsoft.com/en-us/library/ms174420.aspx

José.Cruz Right there with Babe Points: 722 More actions · Answer 5

Hi,

Here is a "brut force" solution that i think produces the required output:

declare @Date Datetime

set @Date = '2010-01-04'

select @Date, min (limit), max(limit)

from (

select dateadd(d, offset, @Date) as limit

from (

select 0 as offset union all select 1 union all select 2 union all

select 3 union all select 4 union all select 5 union all select 6

) offset

union

select dateadd(d, -offset, @Date) as limit

from (

select 0 as offset union all select 1 union all select 2 union all

select 3 union all select 4 union all select 5 union all select 6

) offset

) a

where datepart(week, @Date) = datepart(week, limit)

José Cruz

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 6

Use a calendar table

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

Clear Sky SQL
My Blog[/url]

ArkWare Mr or Mrs. 500 Points: 525 More actions · Answer 7

This should do it...

DECLARE @date as datetime

set @date = '01/01/2010'

SELECT CONVERT(varchar(11), @date, 101) as Today

, CAST(DATEPART(month,d.firstday) as varchar(2)) + '\' + CAST(DATEPART(day,d.firstday) as VARCHAR(2)) +

' - ' + CAST(DATEPART(month,d.lastday) as varchar(2)) + '\' + CAST(DATEPART(day,d.lastday) as VARCHAR(2)) as DateRange

FROM

(Select

-- Return first day of the week for any date...(Sunday)

DATEADD(Week, DATEDIFF(Week, 6,@date), 6) as firstday

-- Return last day of the week for any date...(Saturday)

,DATEADD(Week, DATEDIFF(Week, 5,@date), 5) as lastday ) d

ian.proffer SSC Rookie Points: 39 More actions · Answer 8

Thanks to everyone who responded. Arkware's solution is exactly what I was looking for.