January 7, 2010 at 10:04 pm
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:
Week Data
1/3 – 1/9 123
1/10-1/16 456
1/17-1/23 789
January 7, 2010 at 10:12 pm
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.
January 7, 2010 at 10:28 pm
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.
January 7, 2010 at 10:39 pm
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?
January 8, 2010 at 1:10 am
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.
January 8, 2010 at 3:49 am
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
January 8, 2010 at 3:57 am
Use a calendar table
January 8, 2010 at 1:55 pm
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
January 8, 2010 at 2:31 pm
Thanks to everyone who responded. Arkware's solution is exactly what I was looking for.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply