24hr datetime format

shajju · #1 (**permalink**) 07-01-09, 07:42

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

flyboy · #2 (**permalink**) 07-01-09, 08:14

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.

shajju · #3 (**permalink**) 07-02-09, 08:58

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

shajju · #4 (**permalink**) 07-02-09, 09:05

NLS_DATE_FORMAT is DD/MM/RR

anacedent · #5 (**permalink**) 07-02-09, 10:34

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:MI

S';

flyboy · #6 (**permalink**) 07-02-09, 11:11

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).

shajju · #7 (**permalink**) 07-03-09, 07:58

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.

flyboy · #8 (**permalink**) 07-03-09, 10:12

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]

shajju · #9 (**permalink**) 07-07-09, 06:15

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').

flyboy · #10 (**permalink**) 07-07-09, 21:45

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.

shajju · #11 (**permalink**) 07-08-09, 08:11

Hi

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

Regards

flyboy · #12 (**permalink**) 07-08-09, 08:35

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>

Internet Services

Web Development

Digital Marketing

Consumer Tech