CODEWITHSUNDEEP
sqloracle-databasedateoracle12ctoad
Kaustubh Khare
Kaustubh Khare

Reputation: 3510

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?

Upvotes: 1

Views: 3606

Answers (2)

VBoka
VBoka

Reputation: 9083

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;

Upvotes: 1

GMB
GMB

Reputation: 222582

You are getting the correct output. Only it is displayed in the default date format of your session (or database), which seems to be mm/dd/yyyy.

You control the default date format of your session with paramter nls_date_format:

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

Alternatively, you can use to_char() to format your date to a specific format:

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

Upvotes: 4

Related Questions