Determine Nearest Day

Hi,
Users of an application can retrieve sales between 2 dates that they pick in
the front end (DateFrom & DateTo)
I have sales data that these values will be used to query, the data is
always summarised to the Sunday of each w.
Before my stored procedure attempts to query, it needs to work out the
nearest Sunday for each of the dates. Eg;
DateFrom = 14th December 2005
This date needs to be converted to look 'backwards' for the nearest Sunday.
It needs to find 11th December.
DateTo = 29th December 2005
This needs to be converted forwards to look to the nearest Sunday. It needs
to find 01 Jan 2006.
Is there any TSQL that can be used to work this out, or is it a case of
using a lookup table (which I do have available)
Thanks
DylanHi
DECLARE @.Today datetime
SET @.Today = '20060105'
SELECT DATEADD(day, DATEDIFF(day, '1900', @.Today)/7*7+6 , '1900')
"DylanM" <DylanM@.discussions.microsoft.com> wrote in message
news:031DC14C-596B-411C-B15D-88D17743AB7E@.microsoft.com...
> Hi,
> Users of an application can retrieve sales between 2 dates that they pick
> in
> the front end (DateFrom & DateTo)
> I have sales data that these values will be used to query, the data is
> always summarised to the Sunday of each w.
> Before my stored procedure attempts to query, it needs to work out the
> nearest Sunday for each of the dates. Eg;
> DateFrom = 14th December 2005
> This date needs to be converted to look 'backwards' for the nearest
> Sunday.
> It needs to find 11th December.
> DateTo = 29th December 2005
> This needs to be converted forwards to look to the nearest Sunday. It
> needs
> to find 01 Jan 2006.
> Is there any TSQL that can be used to work this out, or is it a case of
> using a lookup table (which I do have available)
>
> Thanks
> Dylan
>|||Excellent, thanks Uri.
And to convert backwards, I thinks it's just as follows...' (-1 instead of
+6)
SELECT DATEADD(day, DATEDIFF(day, '1900', @.Today)/7*7-1 , '1900')
Thanks again.
"Uri Dimant" wrote:

> Hi
> DECLARE @.Today datetime
> SET @.Today = '20060105'
> SELECT DATEADD(day, DATEDIFF(day, '1900', @.Today)/7*7+6 , '1900')
>
>|||This rounds it up to the nearest Sunday. It prints DateTo, the day or the
w dateto is, and then the rounded up date, and the day of the w the
rounded up day is.
I suspect you can use the modulus operator to make this perform better, but
I'm not SK.
create table salesData
(pk int not null identity primary key,
DateTo datetime,
DateFrom Datetime)
GO
insert into salesData values (getdate()-101,getdate()-103)
insert into salesData values (getdate()-105,getdate()-108)
GO
select case when DATENAME ( dw , DateTo )='Sunday' then dateto
when DATENAME ( dw , DateTo )='Monday' then dateto-1
when DATENAME ( dw , DateTo )='Tuesday' then dateto-2
when DATENAME ( dw , DateTo )='Wednesday' then dateto-3
when DATENAME ( dw , DateTo )='Thursday' then dateto-4
when DATENAME ( dw , DateTo )='Friday' then dateto-5
when DATENAME ( dw , DateTo )='Saturday' then dateto-6
end,
dateto, datename(wday,dateto),
case when DATENAME ( dw , DateTo )='Sunday' then datename(wday, dateto)
when DATENAME ( dw , DateTo )='Monday' then datename(wday, dateto-1)
when DATENAME ( dw , DateTo )='Tuesday' then datename(wday, dateto-2)
when DATENAME ( dw , DateTo )='Wednesday' then datename(wday, dateto-3)
when DATENAME ( dw , DateTo )='Thursday' then datename(wday, dateto-4)
when DATENAME ( dw , DateTo )='Friday' then datename(wday, dateto-5)
when DATENAME ( dw , DateTo )='Saturday' then datename(wday, dateto-6)
end
from salesdata
go
drop table salesdata
go
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"DylanM" <DylanM@.discussions.microsoft.com> wrote in message
news:031DC14C-596B-411C-B15D-88D17743AB7E@.microsoft.com...
> Hi,
> Users of an application can retrieve sales between 2 dates that they pick
> in
> the front end (DateFrom & DateTo)
> I have sales data that these values will be used to query, the data is
> always summarised to the Sunday of each w.
> Before my stored procedure attempts to query, it needs to work out the
> nearest Sunday for each of the dates. Eg;
> DateFrom = 14th December 2005
> This date needs to be converted to look 'backwards' for the nearest
> Sunday.
> It needs to find 11th December.
> DateTo = 29th December 2005
> This needs to be converted forwards to look to the nearest Sunday. It
> needs
> to find 01 Jan 2006.
> Is there any TSQL that can be used to work this out, or is it a case of
> using a lookup table (which I do have available)
>
> Thanks
> Dylan
>|||To do it in one shot, try this:
SELECT DATEADD(day, DATEDIFF(day, '18991231', @.Today+3)/7*7 , '18991231')
It will find "next" Sunday, if today is Thursday, Friday, or Saturday,
it will find "This"
Sunday if today is Sunday, and it will find "last" Sunday if today is
Monday, Tuesday, or Wednesday.
Steve Kass
Drew Unviersity
DylanM wrote:

>Hi,
>Users of an application can retrieve sales between 2 dates that they pick i
n
>the front end (DateFrom & DateTo)
>I have sales data that these values will be used to query, the data is
>always summarised to the Sunday of each w.
>Before my stored procedure attempts to query, it needs to work out the
>nearest Sunday for each of the dates. Eg;
>DateFrom = 14th December 2005
>This date needs to be converted to look 'backwards' for the nearest Sunday.
>It needs to find 11th December.
>DateTo = 29th December 2005
>This needs to be converted forwards to look to the nearest Sunday. It needs
>to find 01 Jan 2006.
>Is there any TSQL that can be used to work this out, or is it a case of
>using a lookup table (which I do have available)
>
>Thanks
>Dylan
>
>sql