How is the better way to use filter date in a query?

hidrau
hidrau used Ask the Experts™
on
Hello Guys,

I have 1 million of row and I have a field that keep datetime value.

I have an index for this field and I want to filter rows between two dates.

The problem is that this field also keeps time information and I don't want to filter with

time. If I use the convert to take off the time, my query doesn't use the index date.

What can I do?

I tried this:

SELECT * FROM MOVIMENT
WHERE CONVERT(VARCHAR(10), DATEMOV,  112) >='20120101'
    AND CONVERT(VARCHAR(10), DATEMOV,  112) <='20120416'

This query solves my problem, but it takes a long time because it put appart the index.

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Daniel ReynoldsSoftware Applications Developer / Integrator

Commented:
You should still be able to do a between '2012-01-01' and '2012-04-16' and get the appropriate rows without worrying about your time values.

I can see where there might be an issue if you didn't want to use time values in your query yet return only those after 12pm for a date in the table.

Have you tried ?

SELECT * from MOVIMENT
WHERE DATEMOV BETWEEN '2012-01-01' and '2012-04-16'

Author

Commented:
Hi xDJR1875

I did with Between, let me give you the results:

with this filter I had a result of 762 rows
AND A.NFI_DEMI >= '2012-01-01 00:00:00'
AND A.NFI_DEMI <= '2012-01-30 23:59:00'


with this filter I had a result of 696 rows
AND A.NFI_DEMI >= '2012-01-01' AND A.NFI_DEMI <= '2012-01-30'

with this filter I had a result of 696 rows
AND A.NFI_DEMI between '2012-01-01' and '2012-01-30'

with this filter I had a result of 762 rows
And convert(char(10), A.NFI_DEMI, 112)>='20120101'
And convert(char(10), A.NFI_DEMI, 112)<='20120130'


as you can see, there is a big difference, the correct is 762 rows
:((
Try:
 WHERE DATEMOV >= '20120101'  AND DateMov < '20120417'

If you do a BETWeEN 20120101 AND 20120416 it does up till 20120416 but not for example 20120416 00:01
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Daniel ReynoldsSoftware Applications Developer / Integrator

Commented:
What is the difference in the records that are the exceptions?
Daniel ReynoldsSoftware Applications Developer / Integrator

Commented:
So you would have to add the day on the end of the between like so
AND A.NFI_DEMI between '2012-01-01' and '2012-01-31'

Author

Commented:
it didn't accept the syntax > after between
Daniel ReynoldsSoftware Applications Developer / Integrator

Commented:
you can't mix between and >
BETWEEN returns TRUE if the value of test_expression is greater than or equal to the value of begin_expression and less than or equal to the value of end_expression.

These all returns the same count.

SELECT COUNT(*) FROM MOVIMENT
      WHERE CONVERT(VARCHAR(10), DATEMOV,  112) >='20120101' AND CONVERT(VARCHAR(10), DATEMOV,  112) <='20120416'

SELECT COUNT(*) FROM MOVIMENT
      WHERE DATEMOV >= '20120101'  AND DateMov < '20120417'

SELECT COUNT(*) FROM MOVIMENT
      WHERE DATEMOV BETWEEN '20120101' AND '20120417' AND DATEMOV < '20120417'
      
SELECT COUNT(*) FROM MOVIMENT
      WHERE DATEMOV BETWEEN '20120101' AND '20120416 23:59:59'

Author

Commented:
thanks very much

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial