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

    Unanswered: 24hr datetime format

    Hi

    A few days ago when I ran a query, it returned the datetime in 24hr format.

    I didn't use (datetime,'HH24').

    Now, I've run the same query again and the datetime is returned in 12hr format (i.e., AM and PM), even tried using 'HH24' but no good.

    Please advise on how to check?

    Regards
    Sheraz

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    As you did not post the remarked query, there is only place for guesses.

    My guess is, that the query uses wrong conversion function (e.g. TO_DATE on a date) or that it does not return result with VARCHAR2 datatype.
    In consequence, the value of NLS_DATE_FORMAT is used for DATE/VARCHAR2 conversion. This setting was probably changed between query executions. You may check its current value with this query:
    Code:
    select *
    from v$nls_parameters
    where parameter = 'NLS_DATE_FORMAT';
    I advice you to use proper conversion function and (if the query is used only for displaying purpose) return result with VARCHAR2 data type.

  3. #3
    Join Date
    Aug 2008
    Posts
    464
    The query is:

    select datetime,
    sum(orig_count) "Orig Total",
    sum(term_count) "Term Total",
    sum(orig_count)+ sum(term_count) "SMS Tot",
    round( ( sum(orig_count)+sum(term_count) ) / 3600, 2) avg
    from schema.table
    where datetime between trunc(sysdate)-1 and trunc(sysdate)-1/24
    and caption not in ('mar2','mar1','QO11','QO21','QO31')
    group by datetime

  4. #4
    Join Date
    Aug 2008
    Posts
    464
    NLS_DATE_FORMAT is DD/MM/RR

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Quote Originally Posted by shajju
    NLS_DATE_FORMAT is DD/MM/RR
    In my opinion, "RR" is poor choice.
    Note it does NOT contain any time format, results for Time are indeterminate.

    ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MIS';
    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.

  6. #6
    Join Date
    Mar 2007
    Posts
    623
    Is it the same query you referred in the initial post. I am quite puzzled, as it dos not contain any "using 'HH24'" format mask.

    By the way, what is the data type of DATETIME column? If TIMESTAMP, you may be interested in the value of NLS_TIMESTAMP_FORMAT (as NLS_DATE_FORMAT does not contain any time component information).

  7. #7
    Join Date
    Aug 2008
    Posts
    464
    Yes, it's the same query in this post. The data type for the Datetime col is DATE.

    There no HH24 used in the original query yet it gave a 24hr format a few days ago but now the format it returns is 12hr.

  8. #8
    Join Date
    Mar 2007
    Posts
    623
    If you consider it being a problem, then
    Quote Originally Posted by flyboy
    I advice you to use proper conversion function and (if the query is used only for displaying purpose) return result with VARCHAR2 data type.
    As the query shows column with DATE data type, it is displayed in NLS_DATE_FORMAT format.
    As NLS_DATE_FORMAT setting may be set differently in different sessions I doubt that the value you provided is from the session where the query runs (as it does not contain time part and you state that query shows at least hours).

    Without providing any evidence like the one below, I would say that (any potential) problem is not on Oracle side.
    Code:
    SQL> select *
      2  from v$nls_parameters
      3  where parameter = 'NLS_DATE_FORMAT';
    
    PARAMETER                                                        VALUE
    ---------------------------------------------------------------- -----------------------------------
    NLS_DATE_FORMAT                                                  DD-MON-RR
    
    1 row selected.
    
    SQL> select sysdate from dual;
    
    SYSDATE
    ---------
    03-JUL-09
    
    1 row selected.
    
    SQL> select to_char( sysdate, 'dd.mm.yyyy hh12:mi:ss am' ) from dual;
    
    TO_CHAR(SYSDATE,'DD.MM
    ----------------------
    03.07.2009 04:13:25 pm
    
    1 row selected.
    
    SQL>
    [Edit: added last code example - showing date in given format]

  9. #9
    Join Date
    Aug 2008
    Posts
    464
    Please find the output requested.

    select *
    from v$nls_parameters
    where parameter = 'NLS_DATE_FORMAT';

    NLS_DATE_FORMAT DD/MM/RR

    select sysdate from dual;
    7/7/2009 2:01:27PM

    select to_char( sysdate, 'dd.mm.yyyy hh24:mi:ss' ) from dual;
    07.07.2009 14:03:12

    select to_char( sysdate, 'dd.mm.yyyy hh12:mi:ss am' ) from dual;
    07.07.2009 02:04:17pm

    I am using:

    select trunc(datetime, 'HH24'),moid,fr,f

    from schema.table where
    datetime between trunc(sysdate)-1 and trunc(sysdate)-1/24
    order by moid, trunc(datetime,'HH24')

    I've also tried using only datetime instead of trunc(datetime,'HH24').

  10. #10
    Join Date
    Mar 2007
    Posts
    623
    Seems like you executed the first two queries in different sessions as they return inconsistent results.

    Anyway, the case is clear: both DATETIME and TRUNC( DATETIME, 'HH24' ) have DATE data type. For displaying them, Oracle converts it to string using NLS_DATE_FORMAT parameter.

    To display the DATE in required format, either change the value of NLS_DATE_FORMAT in the session where you run the query, or
    Quote Originally Posted by flyboy
    (if the query is used only for displaying purpose) return result with VARCHAR2 data type.
    using TO_DATE function with required format mask.

  11. #11
    Join Date
    Aug 2008
    Posts
    464
    Hi

    Sorry for being such a nerd but please could you show me what you mean by an example?

    Regards

  12. #12
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by shajju
    Sorry for being such a nerd but please could you show me what you mean by an example?
    What about the queries I posted in my previous post?
    The first one displays the value of NLS_DATE_FORMAT parameter.
    The second one shows how DATE data type is converted to string using the above parameter for displaying.
    The third one shows converting of the DATE data type to string using TO_CHAR function with desired format mask - this is my second recommendation.

    In fact, I have no idea what is the background of that query (reporting tool), so I have no idea how this session setting can be changed there (e.g. in SQL Developer, it is possible from menu). In plain SQL, it is possible by issuing
    Code:
    SQL> alter session set nls_date_format = 'dd.mm.yyyy hh12:mi:ss am';
    
    Session altered.
    
    SQL>

Posting Permissions

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