TO_DATE - get 12:00:00 AM
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: TO_DATE - get 12:00:00 AM

  1. #1
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343

    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.

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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
    Last edited by DaPi; 12-08-2004 at 12:18 PM.

  3. #3
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    Thanks DaPi. I guess it was just a display issue.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width