CODEWITHSUNDEEP
sqloracle-databaseoracle9i
Victor
Victor

Reputation: 17107

Oracle query not displaying date properly

In Oracle, there is a column(dateColumn) in a table with column type = DATE and the value for a particular record is showing as '10/03/2010' when I do a select * from table. Now, when I do:

SELECT * 
  FROM table 
 WHERE dateColumn < '01-JAN-11'

Nothing shows up. When I do:

SELECT * 
  FROM table 
 WHERE dateColumn > '01-JAN-11'

The record shows up. Why is this behaving this way? "10/03/2010" is 10th MArch 2010 so clearly that is < 01 Jan 2011.

Upvotes: 1

Views: 368

Answers (2)

triclosan
triclosan

Reputation: 5724

Try to use to_date:

select * from table where dateColumn < to_date('01-JAN-11', 'dd-MON-YY')

Upvotes: 1

OMG Ponies
OMG Ponies

Reputation: 332661

There is no definitive date format -- it can be different by region or even business. That's the reality without even considering SQL...

In Oracle, the means of evaluating a string as a date requires you to use the TO_DATE function:

SELECT * 
  FROM table 
 WHERE dateColumn > TO_DATE('01-JAN-11', 'DD-MON-YY')

You need to supply the format mask to instruct Oracle how to interpret the date you're supplying it.

The function is often different on other databases, but the experience is otherwise the same. Most databases accept 'YYYY-MM-DD' for implicit conversion of a string into a date. Explicit conversion is when you use a function, like TO_DATE to explicitly change the data type.

Upvotes: 7

Related Questions