TO_DATE - get 12:00:00 AM

This function

Code:

---

SELECT  TO_DATE( '08/16/2004 01:00:00 A.M.',  'MM/Dd/YYYY HH:MI:SS A.M.' )  from dual

---

gives me "8/16/2004 1:00:00 AM"

wheras

Code:

---

SELECT  TO_DATE( '08/16/2004 12:00:00 A.M.',  'MM/Dd/YYYY HH:MI:SS A.M.' )  from dual

---

gives me just "8/16/2004"

I want the datetime stamp to be at 12:00:00 AM - What syntax should I use to get "8/16/2004 12:00:00 AM" in my result ?

Thanks.

Uh?

With nls_date_fomat = DD-MON-YYYY HH24:MI:SS

Code:

---

  1* SELECT  TO_DATE( '08/16/2004 01:00:00 AM',  'MM/DD/YYYY HH:MI:SS AM' )  from dual

TO_DATE('08/16/20040
--------------------
16-AUG-2004 01:00:00

  1* SELECT  TO_DATE( '08/16/2004 12:00:00 AM',  'MM/DD/YYYY HH:MI:SS AM' )  from dual

TO_DATE('08/16/20041
--------------------
16-AUG-2004 00:00:00

---

Code:

---

SQL> alter session set nls_date_format = 'MM/DD/YYYY HH:MI:SS AM' ;

SQL> SELECT  TO_DATE( '08/16/2004 12:00:00 AM',  'MM/DD/YYYY HH:MI:SS AM' )  from dual;

TO_DATE('08/16/200412:
----------------------
08/16/2004 12:00:00 AM

---

Thanks DaPi. I guess it was just a display issue.