CODEWITHSUNDEEP
sqloracle-databasedatetime
learn_plsql
learn_plsql

Reputation: 1681

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

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?

Upvotes: 2

Views: 3573

Answers (3)

Vanji
Vanji

Reputation: 1704

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')***

Upvotes: 1

Vilx-
Vilx-

Reputation: 106912

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'

Upvotes: 1

OMG Ponies
OMG Ponies

Reputation: 332571

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.

Upvotes: 13

Related Questions