| Between Dates [message #9395] |
Fri, 07 November 2003 18:50  |
Steve
Messages: 190
Registered: September 1999
|
Senior Member |
|
|
I working with Personal Oracle 7 and Oracle University's "Introduction to Oracel:SQL and PL/SQL Student Guide and trying to determine employees hired between 20-Feb-80 and 01-May-80 from the emp table.
When I write the SQL:
Select ename, hiredate
from emp
where hiredate between '10-Feb-80' and '01-May-80';
I get "no rows". They get 4 rows returned
Keep in mind this exercise is before they introduce the "to date" function.
|
|
|
|
| Re: Between Dates [message #9396 is a reply to message #9395] |
Fri, 07 November 2003 23:30  |
ram
Messages: 95
Registered: November 2000
|
Member |
|
|
If not to_date,might be, they have used to_char function to get the o/p
Select ename, hiredate
from emp
where hiredate between TO_CHAR('02-FEB-81') and to_CHAR('23-FEB-81');
Regards,
Ram
|
|
|
|
| Re: Between Dates [message #9397 is a reply to message #9395] |
Fri, 07 November 2003 23:34 |
Jagdish
Messages: 43
Registered: September 2003
|
Member |
|
|
Hi Steve,
In the following statement, Oracle implicitly converts '20-FEB-1981' to a DATE value using the default date format 'DD-MON-YYYY':
SELECT ename
FROM emp
WHERE hiredate = '20-FEB-1981' ;
Hope this is the desired answer you expect.
Regards,
Jagdish
|
|
|
|
| Re: Between Dates [message #9403 is a reply to message #9396] |
Sat, 08 November 2003 08:55 |
Mike
Messages: 417
Registered: September 1998
|
Senior Member |
|
|
|
The to_char function doesn't make any sense at all in this example. Or do you see any advantage of transforming a string of CHAR into a string of CHAR???
|
|
|
|
| Re: Between Dates [message #9404 is a reply to message #9395] |
Sat, 08 November 2003 08:59 |
Mike
Messages: 417
Registered: September 1998
|
Senior Member |
|
|
|
You can tell your teacher that's realy insane to compare a date field with a string without using the to_date() function. What would happen with your code if for any reason the standart date format of the DB/session would change?
|
|
|
|
| Re: Between Dates [message #9406 is a reply to message #9395] |
Sat, 08 November 2003 17:24 |
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
|
Senior Member |
|
|
'10-Feb-80' will only mean '10th February 1980' if NLS_DATE_FORMAT is set to 'DD-MON-RR' (and NLS_LANGUAGE or NLS_DATE_LANGUAGE is set to one of the 'ENGLISH' options). 'DD-MON-YY' will give '10-FEB-2080'; 'DD-MON-YYYY' will give '10-FEB-0080'.
You could avoid TO_DATE by setting NLS_DATE_FORMAT via ALTER SESSION. In current versions of ORACLE, you can also use:
DATE '1980-02-10'
though this probably won't work in Oracle 7. That version's been obsolete and unsupported a long time now btw.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
