aspose file tools*
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 The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "DATE and sysdate" Watch "DATE and sysdate" New topic
Author

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 ?

Thanks.
Chris Hendy
Ranch Hand

Joined: Mar 04, 2006
Posts: 98
Try

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;

SYSDATE
---------
21-NOV-07

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

Session altered.

SQL> select sysdate from dual;

SYSDATE
--------------------
21-NOV-2007 02:56:49

SQL>
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2497
    
    8

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
youtube channel
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: DATE and sysdate