Time missing when running query using Oracle sqlplus

Question

I have the following script that I am running using sqlplus

set headsep off
set pagesize 0

spool 'c:\file.csv'
select to_char(trunc(created_date, 'hh')) AS reg_date, count(*)
from ORDERS.PRODUCT WHERE CREATED_DATE > TO_DATE('01/APR/2017','dd/mon/yyyy')
group by trunc(created_date, 'hh') ORDER BY reg_date DESC;
spool off

The output is missing the hours:

13-NOV-17,       400
13-NOV-17,       519
13-NOV-17,       126
13-NOV-17,       119
13-NOV-17,       171
13-NOV-17,       183

If I run the same script using SQL Developer, I get:

11/12/2017 17:00:00        396
11/12/2017 16:00:00        402
11/12/2017 15:00:00        459
11/12/2017 14:00:00        462

Why the difference? And how can I get the the hours when running using sqlplus?

Saqib Ali · Accepted Answer · 2017-11-14 00:18:03Z

1

Adding the following to the top of the script fixed the issue:

 alter session set NLS_DATE_FORMAT = 'mm-dd-yyyy HH24:mi:ss';

answered Nov 14, 2017 at 0:18

Saqib Ali

4,55112 gold badges68 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Time missing when running query using Oracle sqlplus

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related