I always get confused with date format in ORACLE SQL query and spend minutes together to google, Can someone explain me the simplest way to tackle when we have different format of date in database table ?
for instance i have a date column as ES_DATE, holds data as 27-APR-12 11.52.48.294030000 AM of Data type TIMESTAMP(6) WITH LOCAL TIME ZONE.
I wrote simple select query to fetch data for that particular day and it returns me nothing. Can someone explain me ?
select * from table
where es_date=TO_DATE('27-APR-12','dd-MON-yy')
or
select * from table where es_date = '27-APR-12';
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';
Source: Stackoverflow.com