36

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.

enter image description here

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';
user2246725
  • 470
  • 1
  • 6
  • 9

4 Answers4

70

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'

  • @user2246725: then you are not telling us everything. Please create a simple example on http://sqlfiddle.com that shows your problem. –  Jul 04 '13 at 21:59
  • I am not getting the data for exact date here it is :- `Where B.Timelog = to_date('14-Apr-17','DD-MM-YY')` – Nad Apr 18 '17 at 11:36
10

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

abhishek ringsia
  • 1,970
  • 2
  • 20
  • 28
3

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';
bilal Tashfeen
  • 59
  • 5
0

In my case, when I used it as '01-NOV-2022', a character other than numeric characters was found error. So I did it this way.

select * from table
where TO_DATE(es_date) >= '01-11-22'
MrAlbino
  • 308
  • 2
  • 10