Need help with BETWEEN condition

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need help with BETWEEN condition

Author

Topic

engrx2
Starting Member

4 Posts

Posted - 2010-06-03 : 16:22:13

I need to retrieve records between two dates. The following statement works as long as the start and end years are same.

SELECT * FROM Tests WHERE (Type='Dynamic Test' AND TestDate BETWEEN '06/01/2009' AND '12/31/2009')ORDER BY Testname ASC

If the start and end years are different then I dont get any records.

SELECT * FROM Tests WHERE (Type='Dynamic Test' AND TestDate BETWEEN '06/01/2009' AND '01/01/2010')ORDER BY Testname ASC

I am wondering if someone can make a suggestion on how to fix this problem. Thanks

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-03 : 17:02:32

Use yyyyMMdd format while querying for dates. What does this give you?

SELECT * FROM Tests WHERE 
Type='Dynamic Test' 
AND TestDate BETWEEN '20090601' AND '20100101' 
ORDER BY Testname ASC

engrx2
Starting Member

4 Posts

Posted - 2010-06-07 : 10:19:52

yyyyMMdd format did not return any results. I also tried MMddyyyy. Same issue with this format it returned results as long as the year was same.

The dates are stored as strings "06/07/2010". Is it possible that this problem has something to do with how the dates are stored?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-06-07 : 10:27:21

You're treating the string as a date. Convert testdate to a datetime and see what happens

TestDate BETWEEN convert(datetime,'20090601') AND convert(datetime,'20100101')

Jim

Everyday I learn something that somebody else already knew

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-07 : 10:27:34

quote:
Originally posted by engrx2

yyyyMMdd format did not return any results. I also tried MMddyyyy. Same issue with this format it returned results as long as the year was same.

The dates are stored as strings "06/07/2010". Is it possible that this problem has something to do with how the dates are stored?

1 Always use proper DATETIME datatype to store dates
2 Try this

SELECT * FROM Tests WHERE (Type='Dynamic Test' AND cast(TestDate as datetime) BETWEEN '20090106' AND '20100101')ORDER BY Testname ASC

3 Refer this to know more about Datetime column
http://beyondrelational.com/blogs/madhivanan/archive/2010/05/25/understanding-datetime-column-part-i.aspx

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-07 : 10:28:02

Yes.
You should use the yyyymmdd format like suggested AND do a convert(datetime,TestDate).

In best way you should change that column to a datetime datatype in your table if possible.

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-07 : 10:29:26

quote:
Originally posted by webfred

Yes.
You should use the yyyymmdd format like suggested AND do a convert(datetime,TestDate).

In best way you should change that column to a datetime datatype in your table if possible.

No, you're never too old to Yak'n'Roll if you're too young to die.

You are too slow

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-07 : 10:42:14

quote:
Originally posted by madhivanan

quote:
Originally posted by webfred

Yes.
You should use the yyyymmdd format like suggested AND do a convert(datetime,TestDate).

In best way you should change that column to a datetime datatype in your table if possible.

No, you're never too old to Yak'n'Roll if you're too young to die.

You are too slow

Madhivanan

Failing to plan is Planning to fail

This old rocker is already a grandpa!
So it is ok if I am a bit too slow

No, you're never too old to Yak'n'Roll if you're too young to die.

engrx2
Starting Member

4 Posts

Posted - 2010-06-08 : 10:02:56

Thanks for the help. Casting TestDate to date worked.

SELECT * FROM Tests WHERE (Type='Dynamic Test' AND CAST(TestDate as date) BETWEEN '12/01/2009' AND '06/08/2010')ORDER BY Testname ASC

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-08 : 10:34:43

quote:
Originally posted by engrx2

Thanks for the help. Casting TestDate to date worked.

SELECT * FROM Tests WHERE (Type='Dynamic Test' AND CAST(TestDate as date) BETWEEN '12/01/2009' AND '06/08/2010')ORDER BY Testname ASC

Have you seen my previous reply?
You should use YYYYMMDD format

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-08 : 10:38:17

...AND CAST(TestDate as date) BETWEEN '20091201' AND '20100608') ORDER BY Testname ASC

No, you're never too old to Yak'n'Roll if you're too young to die.

engrx2
Starting Member

4 Posts

Posted - 2010-06-08 : 16:34:01

Yes, I tried the YYYYMMDD format and it gave an error "Type mismatch in expression.".

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-09 : 02:50:01

quote:
Originally posted by engrx2

Yes, I tried the YYYYMMDD format and it gave an error "Type mismatch in expression.".

Where did you execute the code?

Madhivanan

Failing to plan is Planning to fail

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2010-06-09 : 13:16:34

quote:
Originally posted by engrx2

Thanks for the help. Casting TestDate to date worked.

SELECT * FROM Tests WHERE (Type='Dynamic Test' AND CAST(TestDate as date) BETWEEN '12/01/2009' AND '06/08/2010')ORDER BY Testname ASC

This shows that you have several issues you need to address.

First, your TestDate column is not a datetime column. If it is storing datetime data it should be defined with the datetime data type. That would simplify your queries and prevent these kinds of issues.

You should also be aware that converting the column to a datetime will prevent usage of any indexing on that column.

Second, you are using BETWEEN with datetime data. If there is a time component to the TestDate you will end up with incorrect results because of this. For example, if the TestDate column has a value of '20100608 00:00:01.000' (one second after midnight) - the result will not be included, but '20100608 00:00:00.000' would be included.

The recommend approach when dealing with datetime data is to use an open-interval range check, as in:

WHERE TestDate >= '20091201 00:00:00.000'
AND TestDate < '20100609 00:00:00.000'

The above will include everything for '20100608', but not include anything on the 9th.

Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources