Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2008
    Posts
    464

    Unanswered: Datetime in oracle 9i

    Hi

    I'm using:

    Code:
    select trunc(datetime,'HH24') datetime
    and get:

    Code:
    DATETIME
    
    10/5/2009
    10/5/2009 1:00:00 AM
    10/5/2009 2:00:00 AM
    10/5/2009 3:00:00 AM
    10/5/2009 4:00:00 AM
    10/5/2009 5:00:00 AM
    10/5/2009 6:00:00 AM
    10/5/2009 7:00:00 AM
    10/5/2009 8:00:00 AM
    10/5/2009 9:00:00 AM
    10/5/2009 10:00:00 AM
    10/5/2009 11:00:00 AM
    10/5/2009 12:00:00 PM
    10/5/2009 1:00:00 PM
    10/5/2009 2:00:00 PM
    10/5/2009 3:00:00 PM
    10/5/2009 4:00:00 PM
    10/5/2009 5:00:00 PM
    10/5/2009 6:00:00 PM
    10/5/2009 7:00:00 PM
    10/5/2009 8:00:00 PM
    10/5/2009 9:00:00 PM
    10/5/2009 10:00:00 PM
    10/5/2009 11:00:00 PM
    But as you can see the time format is not HH24. Also, the time 00:00 is not showing. Could someone please advise? Thanks

    Regards
    Shajju

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    What is your NLS_DATE_FORMAT set to?
    Code:
    SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';
    
    Session altered.
    
    SQL> select trunc(to_date('30.09.2009 18:22', 'dd.mm.yyyy hh24:mi'), 'hh24')
      2  from dual;
    
    TRUNC(TO_DATE('30.0
    -------------------
    30.09.2009 18:00:00
    
    SQL> select trunc(to_date('30.09.2009 10:22', 'dd.mm.yyyy hh24:mi'), 'hh24')
      2  from dual;
    
    TRUNC(TO_DATE('30.0
    -------------------
    30.09.2009 10:00:00
    Code:
    SQL> alter session set nls_date_format = 'dd.mm.yyyy hh:mi:ss am';
    
    Session altered.
    
    SQL> select trunc(to_date('30.09.2009 18:22', 'dd.mm.yyyy hh24:mi'), 'hh24')
      2  from dual;
    
    TRUNC(TO_DATE('30.09.2
    ----------------------
    30.09.2009 06:00:00 PM
    
    SQL> select trunc(to_date('30.09.2009 10:22', 'dd.mm.yyyy hh24:mi'), 'hh24')
      2  from dual;
    
    TRUNC(TO_DATE('30.09.2
    ----------------------
    30.09.2009 10:00:00 AM
    
    SQL>

  3. #3
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by shajju
    But as you can see the time format is not HH24. Also, the time 00:00 is not showing. Could someone please advise? Thanks
    When using Oracle function, it is always good practice to determine its behavior from documentation. TRUNC (as all Oracle SQL functions) is described in SQL Reference book, available with many other useful books e.g. online on http://tahiti.oracle.com/.

    Please, read it, as you were already told here: http://www.dbforums.com/oracle/16476...ml#post6422215
    Your question makes no sense - TRUNC function returns DATE. As it has no format, it is displayed according to NLS_DATE_FORMAT setting.

  4. #4
    Join Date
    Aug 2008
    Posts
    464
    Thanks for your replies guys. Oracle is running on a unix server and my pc has windows installed.

    I used the below command to see the nls params on my machine:
    Code:
    select * from v$nls_parameters
    and got
    PARAMETER VALUE

    Code:
    NLS_DATE_FORMAT	   DD/MM/RR
    NLS_DATE_LANGUAGE	   AMERICAN
    NLS_CHARACTERSET	   WE8MSWIN1252
    NLS_SORT	BINARY
    NLS_TIME_FORMAT	   HH12:MI:SSXFF PM
    NLS_TIMESTAMP_FORMAT	   DD/MM/RR HH12:MI:SSXFF PM
    NLS_TIME_TZ_FORMAT	   HH12:MI:SSXFF PM TZR
    NLS_TIMESTAMP_TZ_FORMAT  DD/MM/RR HH12:MI:SSXFF PM TZR
    So does this mean I can't select time to be returned in 24hr format?

  5. #5
    Join Date
    Aug 2008
    Posts
    464
    Furthermore, I tried using substr to just select the time, using:

    substr(datetime,11), and the whole datetime string disappeared. When I tried substr(datetime,1), the whole time portion vanished. Any ideas?

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    a date in oracle is NOT stored as a string but as a special 7 byte binary value. when you ask to see a date by using a select it converts the date to characters to make it human readable. you can manually do this by using to_char and format masks. for example, try

    to_char(datetime,'HH24:mi.ss')

    in your select to display just the time portion (hh24 - hours in military time, mi - minutes, ss - seconds)
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Aug 2008
    Posts
    464

    Thanks

    Many Many thanks for the explanation....I don't know why I find human explanation far better than book explanation.

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I don't know why I find human explanation far better than book.
    Please provide URL of books you attempted to find answer & explain/describe why they lacked details.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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