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 week.

Thanks,

Qjlee

Hi

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

Show quoteHide quote

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 week 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).

Show quoteHide quote

Try this:

SELECT CURRENT_TIMESTAMP + (6-DATEPART(dw,'2005-11-02'))

---
Mark Graveline
Take The Challenge
http://www.sqlchallenge.com
---

Try this instead:

SELECT CURRENT_TIMESTAMP + (6-DATEPART(dw,CURRENT_TIMESTAMP))

Thanks,
M. E. Houston

Show quoteHide quote

Qjlee,

This should always give you the Friday in the week following
the current one, assuming your week begins on Monday.

  dateadd(d,datediff(d,'19000101',getdate())/7*7+11,'19000101')

Steve Kass
Drew University

qjlee wrote:

Show quoteHide quote

Order problem
Dynamic SQL
SQL Server 2005 & Visual Studio 205 RC
grouping and showing concatenated varchar column?
SQL Count Problem