Date in where clause help

[rkrause]

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.

 

[boblarson]

Criteria would be

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

 

[rkrause]

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?

 

[boblarson]

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())

 

[rkrause]

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?

 

[rkrause]

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

 

[boblarson]

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

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

 

[rkrause]

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.

 

[boblarson]

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:

[COLOR=#0000ff]where[/COLOR][SIZE=2] bol[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]ldhshipdate [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]between [COLOR=#ff00ff]dateadd[/COLOR]([COLOR=black]"d", -1,[/COLOR] [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]getdate[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]()) [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]and [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]dateadd[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]"d"[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]6[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]getdate[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]())[/COLOR][/SIZE][/COLOR][/SIZE]
[COLOR=#808080]

[/COLOR]

 

[boblarson]

Or like this:

[COLOR=#0000ff]where[/COLOR][SIZE=2] Format(bol[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]shipdate, "mm/dd/yyyy") [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]between[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] Format(getdate[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080](), "mm/dd/yyyy") [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]and Format([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]dateadd[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]"d"[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]6[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]date[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]()),"mm/dd/yyyy")

[/COLOR][/SIZE][/COLOR][/SIZE]

 

[pbaldy]

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)