IM trying to have a where clause look for todays date and pull all the data from todays and all the data within the next 6 days.
i dont want to use any parameters just automatically do it.

Im not sure how i would do this, any help would be great.

Criteria would be

Between Date() And DateAdd("d", 6, Date())

where bol.shipdate between date() and dateadd("d",6,date())


thats what i have, and im getting an error, i think i have my date field in the wrong spot?

Looks right to me except for spaces between things. Hopefully you do have those spaces in there. So this:

where bol.shipdate between date()anddateadd("d",6,date())

really looks like this:

where bol.shipdate between date() and dateadd("d",6,date())

Msg 195, Level 15, State 10, Line 24
'date' is not a recognized built-in function name.


thats the error im getting
i dont think it likes the date() part?

it needs to be getdate() and not date()

it needs to be getdate() and not date()

Yep, I missed that you were asking in the SQL Server category. :o

im using this in my where clause
where bol.ldhshipdate between getdate() and dateadd("d",6,getdate())

my issue is its pulling the next 6 days after today

what i would want is to include today 9/28/2010 and then next 6 days. any help would be great.

Probably because there is a time element for that field. To include today in it for that type of problem you can basically use this:

where bol.ldhshipdate between dateadd("d", -1, getdate()) and dateadd("d",6,getdate())

Or like this:

where Format(bol.shipdate, "mm/dd/yyyy") between Format(getdate(), "mm/dd/yyyy") and Format(dateadd("d",6,date()),"mm/dd/yyyy")

Bob, I think the method in post 9 would bring in records for the day before that had a time after the current time. In post 10, I don't think T-SQL has the Format() function, does it? If not CONVERT may work to isolate the date:

CONVERT(varchar, GETDATE(), 101)