Next Friday

I have a table called Order, within which there is a field called orderdate,
I can I program so that I can always retrieve order with an order date as
next Friday when I am running this script anytime this w.
Thanks,
QjleeHi
You can work this out using a calendar table see
http://www.aspfaq.com/show.asp?id=2519 or use the dataadd and datepart
functions (see books online).
John
"qjlee" wrote:

> I have a table called Order, within which there is a field called orderdat
e,
> I can I program so that I can always retrieve order with an order date as
> next Friday when I am running this script anytime this w.
> Thanks,
> Qjlee|||You can solve it by using the DATEPART() function like this:
declare @.today smalldatetime
set @.today = '20051102'
create table #tmp
(
ID int identity(1,1) primary key clustered,
OrderDate smalldatetime null
)
set nocount on
insert into #tmp (OrderDate) values ('20051101')
insert into #tmp (OrderDate) values ('20051101')
insert into #tmp (OrderDate) values ('20051102')
insert into #tmp (OrderDate) values ('20051103')
insert into #tmp (OrderDate) values ('20051103')
insert into #tmp (OrderDate) values ('20051104')
insert into #tmp (OrderDate) values ('20051104')
insert into #tmp (OrderDate) values ('20051104')
insert into #tmp (OrderDate) values ('20051107')
insert into #tmp (OrderDate) values ('20051108')
insert into #tmp (OrderDate) values ('20051108')
insert into #tmp (OrderDate) values ('20051109')
insert into #tmp (OrderDate) values ('20051110')
insert into #tmp (OrderDate) values ('20051110')
insert into #tmp (OrderDate) values ('20051111')
insert into #tmp (OrderDate) values ('20051111')
insert into #tmp (OrderDate) values ('20051111')
set nocount off
select * from #tmp
where OrderDate =
(
select min(OrderDate) from #tmp -- Earliest...
where datepart(dw,OrderDate) = 6 -- Friday...
and OrderDate >= @.today -- on or after "today"
)
drop table #tmp
The DATEPART() function, when used with the 'dw' parameter, returns the
day of the w and is dependant on how you have the SET DATEFIRST
setting configured. For me, Friday = 6. Also, if your OrderDate column
contains time info as well then you'll need to do a little range
checking to make sure the OrderDate is somewhere on that day (and not
just at midnight) - there are heaps of references on how to do that (I'm
sure I've seen an example on http://aspfaq.com).
*mike hodgson*
blog: http://sqlnerd.blogspot.com
qjlee wrote:

>I have a table called Order, within which there is a field called orderdate
,
>I can I program so that I can always retrieve order with an order date as
>next Friday when I am running this script anytime this w.
>Thanks,
>Qjlee
>|||Try this:
SELECT CURRENT_TIMESTAMP + (6-DATEPART(dw,'2005-11-02'))
Mark Graveline
Take The Challenge
http://www.sqlchallenge.com
--|||Qjlee,
This should always give you the Friday in the w following
the current one, assuming your w begins on Monday.
dateadd(d,datediff(d,'19000101',getdate(
))/7*7+11,'19000101')
Steve Kass
Drew University
qjlee wrote:

>I have a table called Order, within which there is a field called orderdate
,
>I can I program so that I can always retrieve order with an order date as
>next Friday when I am running this script anytime this w.
>Thanks,
>Qjlee
>|||Try this instead:
SELECT CURRENT_TIMESTAMP + (6-DATEPART(dw,CURRENT_TIMESTAMP))
Thanks,
M. E. Houston
"SQLChallenge" <sqlchallenge@.saikoconsulting.com> wrote in message
news:1131062936.928610.144390@.g47g2000cwa.googlegroups.com...
> Try this:
> SELECT CURRENT_TIMESTAMP + (6-DATEPART(dw,'2005-11-02'))
> --
> Mark Graveline
> Take The Challenge
> http://www.sqlchallenge.com
> --
>