Search code examples
sqloracle-databasedateoracle12ctoad

Oracle - to_date() parse of 00:00:00

I am trying to format into Date from Date string by following the way,

select to_date('11/19/2019 00:00:00', 'MM/DD/YYYY HH24:MI:SS') as currentdate from dual;

The output should be,

11/19/2019 12:00:00 AM

But, I am getting the output as,

11/19/2019

enter image description here

When I execute following, 

select to_date('11/19/2019 00:00:01', 'MM/DD/YYYY HH24:MI:SS') as currentdate from dual;

The correct output I am getting,

11/19/2019 12:00:01 AM

enter image description here

My nls_date_format is DD-MON-RR.

enter image description here

My nls_time_format is HH.MI.SSXFF AM.

enter image description here

I want output in the mentioned format. For all other values except than 00:00:00 is working fine.

Why 00:00:00 is not converting into the required format? Is there any way to achieve this?

Solution

  • If you want the AM format you will need this:

    select to_char(to_date('19/11/2019 12:00:00 AM','dd/mm/yyyy hh:mi:ss AM'),'dd/mm/yyyy hh:mi:ss AM') 
from dual;

    If you run it like this: 

    select to_char(to_date('19/11/2019 00:00:00','dd/mm/yyyy hh:mi:ss AM'),'dd/mm/yyyy hh:mi:ss AM') 
from dual;

    You will get an error. When your date format is not 24 hours(hh24) then your value for hour needs to be between 1 and 12.

    Here is the DEMO

    After some research I have realised what OP wants(from his comments and after he entered some more details). Solution for OP is:

    Step 1:

    ALTER session SET NLS_DATE_FORMAT = 'DD-MON-RR HH.MI.SS AM';

    Step 2 now this will work: 

    select to_date('11/19/2019 00:00:00', 'MM/DD/YYYY HH24:MI:SS') as currentdate from dual;