compare dates

[Sweetie]

Hi

Is it possible to compare dates in WHERE clause? For example find out who was hired after December 1999 in a Employee table...

[sambavan]

Sure, as long as you keep up with the format.

Sam

[Sweetie]

Hi

I wrote

select ename from emp
where hiredate > 17/02/99 but I got errors? Whad did I do wrong?

[Oracledba8]

Check the Date Format stored in Database and Date is always encloed in single Quotes.

Usually Date is stored as DD-MON-YY Even if you have any other Format but need to use single Quotes.

Thanks

[Sweetie]

How do I find out the date format?

[Oracledba8]

Easy way.
Select Sysdate from Dual;

You will be able to see how the date format is in Database, when you want to use date column, you have to give the same format, unless you apply any Function.
Thanks

It may give you like this
SYSDATE
=========
06-MAR-01

[mber]

Do one thing:

select * from nls_session_parameters;

It will give you every parameter formats.

Take care

[jmodic]

The general rule for any string to date (and vice versa) conversion shoul be:

NEVER RELY ON DEFAULT DATE FORMAT (whther on server or on client side)!!!!!!

Always (without exception!) use explicite TO_DATE/TO_CHAR conversion with proper format mask. Oracle has to perform conversion in any case, so it is better that you tell it in advance what format mask it should use.

[chrisrlong]

Yea, verily!!

Well spoken, brother Jurij!!!!

This would make your query:

select ename from emp
where hiredate > TO_DATE('17/02/99', 'DD/MM/YY')

Of course, since you never want to specify only 2-digit years either, might I suggest:

select ename from emp
where hiredate > TO_DATE('17/02/1999', 'DD/MM/YYYY')

- Chris