Oracle SQL query for Date format

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] Oracle SQL query for Date format

Examples related to sql

Examples related to database

Examples related to oracle

Examples related to oracle11g