how to delete data from table that has date prior to 2006

Question

I want to delete all data from a table that has a date prior to 01/01/2006

I am trying this:

delete from table_a 
where to_char(last_update_date,'MM/DD/YYYY') < to_char('01/01/2006', 'MM/DD/YYYY')

But this is not working.

What is the best way to compare date's in sql?

Answer 1

So close, so close... it's like you can almost feel that they were right on the brink of a major breakthrough...

Here, try this:

delete from table_a where last_update_date < '01/01/2006'

Answer 2

Use the TO_DATE function to convert a string into an Oracle DATE (includes time):

DELETE FROM TABLE_A
 WHERE last_update_date < TO_DATE('01/01/2006', 'MM/DD/YYYY')

...to delete records with a last_update_date value of December 31, 2005 at 11:59:59.9999 or earlier.

Answer 3

IF you use T-SQL, you can make use of DATEDIFF (datepart ,startdate ,enddate ). as follows you would have make use of it!

delete from table_a where WHERE DateDiff(dd, last_update_date, '01/01/2006') > 0

nevertheless, if you think about Oracle. you can make use of To_Date(date||string,format)

***delete from table_a where WHERE To_date(last_update_date,'dd/mm/yyyy') > to_date('01/01/2006','dd/mm/yyyy')***