CODEWITHSUNDEEP
sqloracle-database
Jestem_z_Kozanowa
Jestem_z_Kozanowa

Reputation: 637

Removing certain rows from table on condition plus converting varchar to date

I understand that this question might have a trivial answer. I would like to remove certain rows from a huge table when certain condition is met. The condition is on drdate (a varchar field), which holds date information so it needs to be converted. The table is huge and ideally I do not want to spend too much time for the query to run.

delete from products 
where (to_date('drdate','mm/dd/yyyy')) > (to_date('01/01/2011','mm/dd/yyyy'))

This returns:

SQL Error: ORA-01858: a non-numeric character was found where a numeric was expected 01858. 00000

Upvotes: 0

Views: 107

Answers (1)

SchmitzIT
SchmitzIT

Reputation: 9572

Try this:

delete from products 
where (to_date(drdate,'mm/dd/yyyy')) > (to_date('01/01/2011','mm/dd/yyyy'))

As stated in the comment, the quotes cause the function to evaluate the string value 'drdate' instead of the values in the drdate column.

Upvotes: 2

Related Questions