Oracle last_ddl_time format

Question

I have to query all_objects table where last_ddl_time='01 jan 2010' but it refuses the date format...

Any body give me the exact format to query?

Answer 1

You can use the to_date function to format your date. If you enter a literal string, Oracle will attempt to convert that string using to_date with a default format 'DD-MON-YY', so your date would look like "01-JAN-10". As Oracle will be using this same function, you might want to put it in yourself and enjoy the finer granularity that custom formatting can provide.

It would be good to note that the dates stored in that column most likely have more precise dates, including hours and minutes, etc. Though you will be taking a bit of a performance hit, you might be better served using trunc(last_ddl_time) if you are testing with =.

There is some good info on Dates in Oracle at this link.

Answer 2

As AKF said, you should be using Trunc unless you know the exact time the DDL was modified. Your query you added in the comments is looking for any objects where the DDL changed at 1/1/2010 00:00:00. Try:

SELECT * 
  FROM all_objects
 WHERE trunc(last_ddl_time) = to_date('01-01-2010','dd-mm-yyyy');

Answer 3

I suggest you to use de date literal:

where trunc(last_ddl_time) = date '2010-01-01'