This week's book giveaway is in the Design forum.
We're giving away four copies of Building Microservices and have Sam Newman on-line!
See this thread for details.
The moose likes Oracle/OAS and the fly likes DATE and sysdate Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Building Microservices this week in the Design forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "DATE and sysdate" Watch "DATE and sysdate" New topic

DATE and sysdate

Raj Ohadi
Ranch Hand

Joined: Jun 30, 2006
Posts: 316
I have a column "log_time" with type "DATE". When I insert rows, I always use "sysdate" for this column. From Oracle book I find "DATE" type can include up to second precision. So I tried to enter some rows, with several minutes apart for any two consecutitive inserts. However, when I do

select * from Table order by log_time desc;

I found they are NOT sorted by "log_time" because all of the log_time values shows the same (Today). So it seems Oracle doesn't distinguish "sysdate" to narrow down to second level. Is there anything I miss here ?

Chris Hendy
Ranch Hand

Joined: Mar 04, 2006
Posts: 98

select to_char(t.log_time,'DD-MON-YYYY HH24:MI:SS') log_time_full, t.*
from Table t
order by t.log_time desc;

and see what happens
Chris Hendy
Ranch Hand

Joined: Mar 04, 2006
Posts: 98
The date datatype is stored in a proprietory internal format, but displayed according to the database initialization parameter NLS_DATE_FORMAT (which at least for me defaults to 'DD-MON-YY' and so chops off the hours/minutes/seconds). You can alter this either for your session using ALTER SESSION (see below), or system wide by changing the init paramter - but this requires a database bounce.

SQL> select sysdate from dual;


SQL> alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

Session altered.

SQL> select sysdate from dual;

21-NOV-2007 02:56:49

Jan Cumps

Joined: Dec 20, 2006
Posts: 2543

I always use "sysdate" for this column
Can you show us how you do that (via sql literal, via java PreparedStatement.setDate() or via via java PreparedStatement.setTime())?

So it seems Oracle doesn't distinguish "sysdate" to narrow down to second level.
It does. When using ORDER BY LOG_TIME DESC, Oracle will use the date and the time to sort.
To help us, can you please show some results for this query where the sorting order is returned incorrect:
Regards, Jan
[ November 21, 2007: Message edited by: Jan Cumps ]

OCUP UML fundamental and ITIL foundation
Have you checked out Aspose?
subject: DATE and sysdate
It's not a secret anymore!