Search code examples
sqldatabaseoraclesqldatatypes

get rows which have a higher date than a specific one in MM/DD/YYYY format

sorry for asking a really amateurish question, but I am only beginning to learn oracle.

I need to extract rows based on a date column which is in MM/DD/YYYY format , but I can't formulate a proper query. I used to_date function too but no fun.

I need to extract rows from XTABLE where XDATE is higher than 10/21/2011 (MM/DD/YYYY)

select * from XTABLE where XDATE >= '10/21/2011'

doesn't work, neither does

select * from XTABLE where to_date(XDATE,'MM/DD/YYYY') > to_date('28/10/2011','MM/DD/YYYY')
Solution

  • Columns of type DATE aren't stored in MM/DD/YYYY format: It's just presentation according to your current NLS settings.

    Your query should be:

    select * from XTABLE where XDATE >= date '2011-10-21'

    See http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements003.htm#BABGIGCJ for information on date and other 'datetime' literals.