delete data based on varchar based date

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

delete data based on varchar based date

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2013-08-27 : 09:59:09

i have char(10) based dates in table column.
2013-04-18

I would like to delete all data based on year.
delete from Table_programs where year(post_date) = '2013'

post_date is varchar(10)

Thank you very much for the helpful info.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-27 : 11:41:50

quote:
Originally posted by cplusplus

i have char(10) based dates in table column.
2013-04-18

I would like to delete all data based on year.
delete from Table_programs where year(post_date) = '2013'

post_date is varchar(10)

Thank you very much for the helpful info.

If all of them are in the same format as in your example:

DELETE FROM YourTable WHERE LEFT(post_date,4) = '2013'

When you use VARCHAR for representing dates, it would allow other formats as well and so there is some risk that you may end up deleting rows that should not have been deleted, and vice versa.

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-08-27 : 12:57:51

You could try:

DELETE FROM Table_programs WHERE YEAR(CAST(post_date AS DATE)) = 2013

djj

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2013-08-27 : 13:04:02

Both of the options provided would ignore any indexes on your table because they wrap the column in a function. This is called a non-SARGable condition. You can drastically increase the speed of this query if you have an index on this column and use this method instead:

DELETE FROM Table_programs WHERE post_date LIKE '2013%';

As James K mentioned, you will need to ensure your data is all in the same format for this to be reliable.

sivadss2007
Starting Member

18 Posts

Posted - 2013-08-28 : 04:38:15

DELETE FROM Table_programs WHERE DATENAME(YYYY,CAST(post_date AS DATETIME)) = 2013

P.Siva

kameswararao polireddy
Starting Member

19 Posts

Posted - 2013-08-28 : 05:39:08

This would work fine and fast too.

DELETE FROM Table_programs WHERE LEFT(post_date,4) = 2013

P.Kameswara rao

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-28 : 08:50:24

Of all the various solutions posted I would suggest you go with the solution posted by lazerath - for the reason he mentioned - i.e., if there is an index on post_date, his solution will be able to make use of that index. None of the others, including the brilliant solution

I had posted would do that.

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-08-28 : 15:30:16

I will second James K. The only problem would be if the date is entered differently.

djj

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources