Oracle SQL query for Date format
if you are using same date format and have select query where date in oracle :
select count(id) from Table_name where TO_DATE(Column_date)='07-OCT-2015';
To_DATE provided by oracle
you can use this command by getting your data. this will extract your data...
select * from employees where to_char(es_date,'dd/mon/yyyy')='17/jun/2003';
to_date()
returns a date at 00:00:00, so you need to "remove" the minutes from the date you are comparing to:
select *
from table
where trunc(es_date) = TO_DATE('27-APR-12','dd-MON-yy')
You probably want to create an index on trunc(es_date)
if that is something you are doing on a regular basis.
The literal '27-APR-12'
can fail very easily if the default date format is changed to anything different. So make sure you you always use to_date()
with a proper format mask (or an ANSI literal: date '2012-04-27'
)
Although you did right in using to_date()
and not relying on implict data type conversion, your usage of to_date() still has a subtle pitfall because of the format 'dd-MON-yy'
.
With a different language setting this might easily fail e.g. TO_DATE('27-MAY-12','dd-MON-yy')
when NLS_LANG is set to german. Avoid anything in the format that might be different in a different language. Using a four digit year and only numbers e.g. 'dd-mm-yyyy'
or 'yyyy-mm-dd'