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
 General SQL Server Forums
 New to SQL Server Administration
 convert varchar to int

Author  Topic 

laddu
Constraint Violating Yak Guru

332 Posts

Posted - 2014-08-15 : 14:10:51
I have a table with date column and need to delete the data more than 30 days old.

I tried this script but getting error

DELETE table WHERE MsgDate < Datediff(dd, 30, Getdate())

Conversion failed when converting the varchar value '2014-08-15' to data type int.

how do i convert this varchar to int? thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-15 : 14:19:04
You need to use DATEADD, not DATEDIFF. And use -30 instead of 30.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2014-08-18 : 03:46:27
Also it looks like MsgDate is of varchar datatype. You need to CAST it to a DATETIME datatype.

DELETE table WHERE cast(MsgDate as datetime) < Dateadd(day, -30, Getdate())

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -