Searching by a date range... - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

Searching by a date range...

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-02-22 : 09:50:21

Ken Dignan writes "I have been attempting for nearly a week now to query a SQL 2000 database for records that fit a specific range of dates. This is the query statement I have been trying to use and the query returns no results.

SELECT *
FROM OrderInfo
WHERE InServiceDate > '05/31/2001' AND InServiceDate < '06/01/2003'

If I just search for the first part of the Where, I get a result it's when I enter the ending date that it fails. Please help if you can.

Thanks,
Ken"

chadmat
The Chadinator

1974 Posts

Posted - 2002-02-22 : 11:56:27

Try:

SELECT *
FROM OrderInfo
WHERE InServiceDate between '06/01/2001' AND '05/31/2003'

Do the results you get with the first part come before 6/1/2003?

-Chad

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-02-22 : 12:08:09

remember date/time fields include minutes + seconds...(may be set to 00:00...but there is a value there!!!)

also while 05/31/2002 is obviously 31st May 2002....
but what date is '06/01/2002'....2 choices....1st June 2002 or 6th jan 2002....(if interpreted as dd/mm/yy - UK format!!!)

this has come up a lot recently here....search the site for 'yyyymmdd' and you will see some relevent advice.

vladimir_grigoro
Yak Posting Veteran

62 Posts

Posted - 2002-02-24 : 02:14:58

Hi Ken,

I am not sure that I understand your question correctly but anyway:

SELECT *
FROM OrderInfo
WHERE InServiceDate >= '05/31/2001' AND InServiceDate =< '06/01/2003'

I that way you will receive all records from '05/31/2001 00:00:00' to '06/01/2003 00:00:00'. If you are more specific what type of result you expected I'll be better.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-02-24 : 05:54:29

would prefer
SELECT *
FROM OrderInfo
WHERE InServiceDate > '20010531' AND InServiceDate < '20030601'
in that way you will never have problems with date formats.

What are you expecting?
If you have values in the middle of these dates then I am surprised that you get nothing with the second inequality.
If the format was incorrect then the first should have given an error.

What happens if you use the second inequality
SELECT *
FROM OrderInfo
WHERE InServiceDate < '20030601'

I am assuming the dates are actually stored in datetime fields - if they are character then you will have to
SELECT *
FROM OrderInfo
WHERE convert(datetime,InServiceDate,101) > '20010531' AND InServiceDate < '20030601'

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources