Basic Help on AND query 1

[arvarr]

Hi
Hoping someone can help out.

p.expenditure_ending_date >= 2010-06-10' AND p.expenditure_ending_date <= '2010-06-10'


I would like to know what data range the above would pick up.

Wouldn't the AND pick up anything that is >= 2010-06-10 as well as anything that is <= 2010-06-10. Which means that it is picking up everything?

Thanks.

 

[r937]

it will pick up all datetime values for june 10th only

2010-06-10 00:00
2010-06-10 01:11
2010-06-10 05:55
2010-06-10 09:37
2010-06-10 21:21
2010-06-10 23:59





r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[bborissov]

No it will get only
2010-06-10 00:00:00.000
Every other will be filtered.
Check it:

[COLOR=blue]DECLARE[/color] @Test [COLOR=blue]TABLE[/color] (Fld1 [COLOR=#FF00FF]DateTime[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color]([COLOR=red]'2010-06-10 00:00:00.000'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color]([COLOR=red]'2010-06-10 00:00:00.103'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color]([COLOR=red]'2010-06-10 01:11'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color]([COLOR=red]'2010-06-10 05:55'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color]([COLOR=red]'2010-06-10 09:37'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color]([COLOR=red]'2010-06-10 21:21'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color]([COLOR=red]'2010-06-10 23:59'[/color])

[COLOR=blue]SELECT[/color] * [COLOR=blue]FROM[/color] @Test 

[COLOR=blue]SELECT[/color] * [COLOR=blue]FROM[/color] @Test [COLOR=blue]WHERE[/color] Fld1 >= [COLOR=red]'20100610'[/color] AND Fld1 <= [COLOR=red]'20100610'[/color]

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[r937]

omg, how silly of me

when i read the original post, i somehow (mis)understood it to be

p.expenditure_ending_date >= '2010-06-10'
AND p.expenditure_ending_date <= [red]'2010-06-11'[/red]

of course, the way it actually stands, it would return only the midnight time

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[arvarr]

Hi
Thanks for the reply.
I will find out a bit more to see if this is the case.

 

[gmmastros]

Sometimes it is helpful to think of dates as though they were numbers.

If you wrote a query like this...

Select * from table where number >= 7 and number <=7

you would expect to get just the row from the table where the number is exactly equal to 7. If your column allowed for fractional number, the query would still only return the values exactly equal to 7. 7.1 is greater than 7, so it would be filtered out and not returned by the query.

Dates can be thought of similar to numbers. The whole number part would represent the number of days that have elapsed since Jan 1, 1900. The fractional part of the number represents time. So, .5 would be noon. .25 would be 6 am,and .75 would be 6pm.

If your date/time column stores dates with a time component, you will only get rows where the time component is exactly midnight (with no hours, minutes, seconds, or milliseconds).

Make sense?


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Khanson82]

Couldn't you also just do:?

convert(varchar,p.expenditure_ending_date,101)
between 2010-06-10' AND '2010-06-11'

 

[bborissov]

You could,
but that will be not optimizable if you have index based on expenditure_ending_date.

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[gmmastros]

Khanson82,

Yes. You can do it that way, but it's not the best way to do it. Under certain circumstances, your method will be dramatically slower than the methods described here. The reason has everything to do with sargability. Specifically, if there is an index on the expenditure_ending_date column, your method would not use the index to speed up the query, but the other methods would.

I encourage you to do some reading about [google]SQL Server sargable[/google]. Then... test it for yourself...

Select * from LargeTable Where PrimaryKeyColumn = 1

Select * From LargeTable Where Convert(BigInt, PrimaryKeyColumn) = 1

Before running the code above, make sure you change the table name and column name. Then, hold the CTRL key and tap the M key. Finally, run the code by pressing F5. After it is finished, you will see an execution plan tab. Click on the execution plan tab and you will see that the first query runs a lot faster than the second one.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom