CODEWITHSUNDEEP
sqloracle-database
Akhil Sharma
Akhil Sharma

Reputation: 11

What is the difference between two queries?

here the table name is employees and hire_date is a column of date type.Both the queries should give same output but the output coming id different. 

select hire_date from employees 
where to_date(hire_date,'dd-mm-yyyy') < to_date('01-01-2007','dd-mm-yyyy')

and

select hire_date from employees 
where hire_date < to_date('01-01-2007','dd-mm-yyyy')

Upvotes: 0

Views: 158

Answers (3)

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

Too long to be a comment.

Find your default NLS_DATE_FORMAT using the query below.

SELECT value
FROM   nls_session_parameters
WHERE  parameter = 'NLS_DATE_FORMAT'

When you use, TO_DATE() over a date column, what happens is, Oracle renders hire_date as a date string matching the NLS_DATE_FORMAT, and only then applies your format mdel specified.

Say, your NLS_DATE_FORMAT is 'MM-DD-YYYY'...

TO_DATE(hire_date,'DD-MM-YYYY') will be executed as , TO_DATE(TO_CHAR(hire_date,'MM-DD-YYYY'),'DD-MM-YYYY')

You can see the difference in the format MM-DD to DD-MM.. Which is a possibility for your data conflicts.

Most of the times, it would end up with a exception, ORA-01843: not a valid month. Which depends on the data underneath.

As a standard practice, never use TO_DATE() over the date column.

Upvotes: 2

Malcolm Vaz
Malcolm Vaz

Reputation: 131

In the first query, you the column hire_date is not a DATE field. It is probably a VARCHAR field so you are converting it to a DATE field using the to_date function. 'dd-mm-yyyy' is the date format in which the VARCHAR field is.

In the second query, hire_date is a DATE field and hence conversion is not necessary

Upvotes: 0

venky513
venky513

Reputation: 321

i think this will help you know the difference . you are specifiying the format u need in the second query . by default hire_date will give the format as below 

SQL> select first_name, last_name, hire_date
  2    from employee
  3  order by hire_date desc, last_name, first_name;

FIRST_NAME       LAST_NAME        HIRE_DATE
———————————————  ———————————————  —————————————
Theresa          Wong             27-FEB-10
Thomas           Jeffrey          27-FEB-10
mark             leblanc          06-MAR-09
michael          peterson         03-NOV-08
Roger            Friedli          16-MAY-07
Betsy            James            16-MAY-07
Matthew          Michaels         16-MAY-07
Donald           Newton           24-SEP-06
Frances          Newton           14-SEP-05
Emily            Eckhardt         07-JUL-04



SQL> select first_name, last_name, TO_CHAR(hire_date, 'YYYY-MM-DD') hire_date
  2    from employee
  3  order by hire_date desc, last_name, first_name;

FIRST_NAME       LAST_NAME        HIRE_DATE
———————————————  ———————————————  —————————————
Thomas           Jeffrey          2010-02-27
Theresa          Wong             2010-02-27
mark             leblanc          2009-03-06
michael          peterson         2008-11-03
Roger            Friedli          2007-05-16
Betsy            James            2007-05-16
Matthew          Michaels         2007-05-16
Donald           Newton           2006-09-24
Frances          Newton           2005-09-14
Emily            Eckhardt         2004-07-07

Upvotes: 0

Related Questions