date range

[rsx]

hello

i am querying a table for data with a specified date range.

eg.
date between '12-1-2004' and '12-31-2004'

i am not picking up data on the 31st with this filter.

however if i use:

date between '12-1-2004' and '1-1-2005'

i get data on the 31st and nothing on the 1st of January.

Can someone tell me why please?

Thanks very much!

 

[dbomrrsm]

depends how your data is stored - if it is stored as datetime and has a time your first query is saying between
'12-1-2004 00:00:00' and '12-31-2004 00:00:00' so it wont pick up '12-31-2004 00:00:01'


[blue]DBomrrsm[/blue]

 

[jhall01]

If you are not concerned with the time then try the following:

convert(varchar(10),date,101) between '12/1/2004' and '12/31/2004'

This will convert the date to midnight of that date and should capture all your dates.

Jon

 

[rsx]

Thanks guys! Very helpful

 

[SQLSister]

And using the first day of the next month will return any values of '1/1/2005 00:00:00' As between is inclusive of both ends. I prefer to use >= and <= to define my where clasue instead as I have more control over exactly what I want as a result and the person maintaining my code later knows exactly what I intended (as what you intended with between is not clear, only what result you got).

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief