Delete SQL Records Based on Derived Column Content

Question

I'm hoping someone can help me with this. I have a MS SQL table with a derived column, for date the records were imported, and need to delete records, based on the content of this column. What I need to do is delete all records from the table with a date of '2011-11-18'. Now this column is a datetime column, so it contains the time info after the date, i.e. 2011-11-18 09:29:38.000, but no matter what command I try for this:

Delete from table where Date_Imported like '2011-11-18%'
Delete from table where Date_Imported like '2011-11-18'
Delete from table where Date_Imported = 2011-11-18

It comes back saying "0 rows affected", even though I know there are records with that date in the table. Any thoughts? I'd appreciate your assistance.

Jesse van Assen · Accepted Answer · 2011-11-18 18:43:23Z

1

If you cast it to a date, your check should work.

DELETE FROM TABLE WHERE CAST(Date_Imported As Date) = '2011-11-18'

answered Nov 18, 2011 at 18:43

Jesse van Assen

2,3002 gold badges16 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Andrew Schulman · Accepted Answer · 2011-11-18 17:44:19Z

Not sure if this is the simplest way, but it should work:

Delete from table where year(Date_Imported) = 2011 and month(Date_Imported) = 11 and day(Date_Imported) = 18

See Date and Time Functions.

Collectives™ on Stack Overflow

Delete SQL Records Based on Derived Column Content

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related