TO_DATE - get 12:00:00 AM

[rshivagami]

This function

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

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.

[DaPi]

Uh?

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

  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

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

[rshivagami]

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