DATETIME Question

[Anddmx]

I have sp that takes for example today's date and run it through some variables with finds other dates based upon today's date. Everything is off my 1 week and I am trying to figure out how to solve this problem.


Below is current code.

Date is passed to this parameter
@dteRequest = '10/04/2007'

This part of the code is causing the problem:
SET @dteRequest_Start = DATEADD(dd, -(DATEPART(ww, @dteRequest) * 7) + 1, @dteRequest)

I have tired to change how hes looking for 01/01/2007 but then throughs everything all out of wack. So I am asking for some advise on which code changes would solve this problem.


CODE:
--Set week starting day as monday
SET DATEFIRST 1
SET @dteRequest_Start = DATEADD(dd, -(DATEPART(ww, @dteRequest) * 7) + 1, @dteRequest)
SET @dteRequest_End = DATEADD(dd, 1, @dteRequest)

--Get the start/End date for the prior year
SET @dteCompare_End = DATEADD(yy, -1, @dteRequest_End)
--If day is not sunday then add the appropriate number of days to it to get sunday
IF DATEPART(dw, @dteCompare_End) <> 7
BEGIN
SET @dteCompare_End = DATEADD(dd, 7 - DATEPART(dw, @dteCompare_End), @dteCompare_End)
END
--If week is not same week of year as Start then add or subtract week as appropriate
IF DATEPART(ww, DATEADD(dd, -1, @dteRequest_End)) <> DATEPART(ww, @dteCompare_End)
BEGIN
SET @dteCompare_End = DATEADD(wk, DATEPART(ww, DATEADD(dd, -1, @dteRequest_End)) - DATEPART(ww, @dteCompare_End), @dteCompare_End)
END
SET @dteCompare_Start = DATEADD(dd, -(DATEPART(ww, @dteCompare_End) * 7) + 1, @dteCompare_End)
SET @dteCompare_End = DATEADD(dd, 1, @dteCompare_End)

 

[SQLDenis]

to get Jan 1 2007

declare @dteRequest datetime
select @dteRequest = '10/04/2007'

select dateadd(yy, datediff(yy, 0, @dteRequest)+0, 0)

however I would advice using ISO standard notation for dates YYYYMMDD

declare @dteRequest datetime
select @dteRequest = '20071004'

select dateadd(yy, datediff(yy, 0, @dteRequest)+0, 0)

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[LNBruno]

Just to tag on:

The reason you're getting the wrong date is because of this assumption:

declare @dteRequest smalldatetime
set @dteRequest = '10/04/2007'

select (DATEPART(ww, @dteRequest) * 7) + 1
--281

Not every week has 7 days; yesterday (10/4/2007) was actually the 277th day of the year!

< M!ke >
[small]"Oops" is never a good thing.[/small]

 

[LNBruno]

Not every week has 7 days

I realized how ridiculous that sounded -- AFTER I hit the submit button! Yeah, the vacation starts in 3 minutes and I'm ready for it! ;-)

But, here's the point I was trying to get across:

[COLOR=blue]declare[/color] @dteRequest [COLOR=blue]smalldatetime[/color]
[COLOR=blue]set[/color] @dteRequest = [COLOR=red]'1/1/2007'[/color]

[COLOR=blue]select[/color] [COLOR=#FF00FF]DATEPART[/color](ww, @dteRequest) 
[COLOR=green]--1
[/color][COLOR=blue]select[/color] ([COLOR=#FF00FF]DATEPART[/color](ww, @dteRequest) * 7)
[COLOR=green]--7[/color]

BUT, the first week of 2007 had only 6 days (the year, month, and week started on Monday)

< M!ke >
[small]"Oops" is never a good thing.[/small]

 

[SQLDenis]

I don't get it?

you are doing this

select 1 * 7

select (wk =1) * 7 = 7

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[Anddmx]

Hi Denis,

Thanks for the reply.

I need some more help that sql code help fix half of the report now I need to fix bottom half.

SP gathers data and puts it into temp tables then slices and dices the data then gives that back to report. When sp starts pulling data from the temp tables it gives back WeekNumbers column instead of datetime stamp to make sure it the correct week. ("Atleast I think this is reason")

How can I find Sunday from a WeekNumber?

Example:
WeekNumber 32 of year 2006

Current Code:
WeekEndDate_REQ = DATEADD(dd, -1, DATEADD(ww, T_REQ.WeekOfYear, @dteRequest_Start)),


PARTIAL CODE FROM SP:

INSERT INTO #T_REQ
EXEC dbo.USP_Report_HotelBookCompData @dteRequest_Start, @dteRequest_End, 3, @cityID, @boolIncludeCxl, @intInventory
INSERT INTO #T_COMP
EXEC dbo.USP_Report_HotelBookCompData @dteCompare_Start, @dteCompare_End, 3, @cityID, @boolIncludeCxl, @intInventory

SELECT
T_REQ.WeekOfYear AS WeekOfYear,
WeekEndDate_REQ = DATEADD(dd, -1, DATEADD(ww, T_REQ.WeekOfYear, @dteRequest_Start)),
WeekEndDate_COMP = DATEADD(dd, -1, DATEADD(ww, T_REQ.WeekOfYear, @dteCompare_Start)),

 

[SkipVought]

I was hoping that NEXT week, would be one of those SHORT weeks.

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[SQLDenis]

I have to go (home now, b-coz I am a slacker after all)

but take a look at this

select datename(dw,'20071005')
select datepart(dw,'20071005')

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions