Gathering data between two date ranges

[Slippenos]

I am trying to create a SELECT query where I gather all numbers between one date range and numbers that equal 0 between another date range (and combine it into 1 query):

SELECT * FROM tblTracker WHERE DateOne BETWEEN 2003-12-12 
AND 2005-03-06

[red]AND[/red]

DateTwo BETWEEN 2003-12-12 AND 2006-01-04
AND Assigned_Number = 0

Any thoughts??

[blue]Go to work to learn. Don't go to work to earn.[/blue]

 

[cranebill]

SELECT *
FROM tblTracker
WHERE trunc(DateOne)
BETWEEN to_date('2003/12/12', 'yyyy/mm/dd')
AND to_date('2005/03/06', 'yyyy/mm/dd')
AND
trunc(DateTwo)
BETWEEN to_date('2003/12/12', 'yyyy/mm/dd')
AND to_date('2006/01/04', 'yyyy/mm/dd')
AND Assigned_Number = 0

 

[Slippenos]

I'll test it out - thank you.

[blue]Go to work to learn. Don't go to work to earn.[/blue]

 

[r937]

hmmm, what is this trunc?

i think the simple solution is to use proper date strings

2003-12-12 is an arithmetic expression equalling 1979

'2003-12-12' is a date string

change this --

WHERE DateOne BETWEEN 2003-12-12 AND 2005-03-06

to this --

WHERE DateOne BETWEEN '2003-12-12' AND '2005-03-06'




r937.com | rudy.ca

 

[dnoeth]

Trunc is for those DBMSes without a genuine DATE datatype ;-)

e.g. MS (DateTime) or Oracle (Date), which are Timestamps.


Btw, for pure ANSI SQL you have to write:

WHERE DateOne BETWEEN date '2003-12-12' AND date '2005-03-06'

Dieter

 

[Slippenos]

Thank you very much- it worked great.
I did, however, leave out the date formatting.

My DBMS is Access, and I have only had luck with 'yyyy-mm-dd'.

As far as the single quotes go, I accidentally forgot to put those in.

Thanks again.

[blue]Go to work to learn. Don't go to work to earn.[/blue]

 

[r937]

as you must have suspected by now, Access does not support ANSI SQL

for Access, try --

WHERE DateOne BETWEEN #2003-12-12# AND #2005-03-06#

for your next question, you may get faster results by posting in one of the Access forums (e.g. forum701)

r937.com | rudy.ca