Hi
Is it possible to compare dates in WHERE clause? For example find out who was hired after December 1999 in a Employee table...
Printable View
Hi
Is it possible to compare dates in WHERE clause? For example find out who was hired after December 1999 in a Employee table...
Sure, as long as you keep up with the format.
Sam
Hi
I wrote
select ename from emp
where hiredate > 17/02/99 but I got errors? Whad did I do wrong?
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
How do I find out the date format?
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
Do one thing:
select * from nls_session_parameters;
It will give you every parameter formats.
Take care
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.
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