asked on

query between dates

Hi,

I have a table with date of birth (31-Dec-2007)...how do i query users from a certain range of years...like 1950 to 1984
thanks

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jwahl

if you're using oracle (this question is also posted in pl/sql topic) i'd recommend to use always format masks:

SELECT *
FROM your_table
WHERE date_of_birth BETWEEN TO_DATE('1950/01/01, 'YYYY/MM/DD') AND TO_DATE('1984/01/01, 'YYYY/MM/DD');

* you don't have to worry about NLS settings (different date formats)
* if column date_of_birth is indexed, the optimizer can use it

Guy Hengel [angelIII / a3]

I think I have do disagree there....
select * from
urTable
WHERE DateOfBirth BETWEEN '1950-01-01' AND '1985-01-01'
AND Month(dateOfBirth) = 12
AND Day(DateOfBirth) = 31

or
select * from
urTable
WHERE CONVERT(VARCHAR(5),DateOfBirth,101) = '12/31'
AND Year(DateOfBirth) BETWEEN 1950 AND 1984

SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jaxrpc

ASKER

hi,

my solution was
select * from
urTable WHERE DateOfBirth BETWEEN '1950-01-01' AND '1984-12-31' too.

are there any better ways than this? angelII i think your queries work only in SQL server?

jwahl

ok, angelIII is right of course. i'd use

select * from
urTable WHERE DateOfBirth BETWEEN TO_DATE('1950-01-01' , 'YYYY-MM-DD') AND TO_DATE('1984-12-31' , 'YYYY-MM-DD');

Aneesh

>my solution was

did u accept the riht solution ?

jaxrpc

ASKER

ahh...i chose the wrong 1
aneeshattingal and angelIII actually
moderator could you please change this for me.

I will email the moderator about this..

thanks for the answers

Aneesh

Don't worry, Angel will do it (He is a moderator)