SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 delete data based on varchar based date
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cplusplus
Aged Yak Warrior

540 Posts

Posted - 08/27/2013 :  09:59:09  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3707 Posts

Posted - 08/27/2013 :  11:41:50  Show Profile  Reply with Quote
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.
Go to Top of Page

djj55
Constraint Violating Yak Guru

USA
337 Posts

Posted - 08/27/2013 :  12:57:51  Show Profile  Reply with Quote
You could try:
DELETE FROM Table_programs WHERE YEAR(CAST(post_date AS DATE)) = 2013


djj
Go to Top of Page

lazerath
Constraint Violating Yak Guru

USA
329 Posts

Posted - 08/27/2013 :  13:04:02  Show Profile  Reply with Quote
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.


Edited by - lazerath on 08/27/2013 13:04:19
Go to Top of Page

sivadss2007
Starting Member

India
18 Posts

Posted - 08/28/2013 :  04:38:15  Show Profile  Reply with Quote
DELETE FROM Table_programs WHERE DATENAME(YYYY,CAST(post_date AS DATETIME)) = 2013

P.Siva
Go to Top of Page

kameswararao polireddy
Starting Member

India
19 Posts

Posted - 08/28/2013 :  05:39:08  Show Profile  Reply with Quote
This would work fine and fast too.

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

P.Kameswara rao
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3707 Posts

Posted - 08/28/2013 :  08:50:24  Show Profile  Reply with Quote
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.
Go to Top of Page

djj55
Constraint Violating Yak Guru

USA
337 Posts

Posted - 08/28/2013 :  15:30:16  Show Profile  Reply with Quote
I will second James K. The only problem would be if the date is entered differently.

djj
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000