Deleting records older than a certain date

[ccampbell]

Hello,
I have a field in a database table that stores the date at mm/dd/yy (form 101). I need to be able to delete any records in that table that are older than 6 months old. Does anyone have a suggestion on how to do this?

 

[AWithers]

What is the underlying datatype of your field, is the field a date field (1) or is it actually a text field (2)

for case 1
delete from yourtable where yourdate <getdate()-180--i.e 180 days old or more

or case 2
delete from yourtable where
convert(datetime,yourdate) <getdate()-180

Hope this helps

Andy

 

[ccampbell]

Thanks,
I imagine that &quot;yourdate&quot; would have to be in the same format as the date that is returned by the getdate() function. What format does getdate() return the date in?

 

[mythili]

U can try getting the current date and format it like

format(getdate(),&quot;mm/dd/yy&quot

now u can go ahead as suggested above

delete from yourtable where yourdate <getdate()-180

or

delete from yourtable where
convert(datetime,yourdate) <getdate()-180

 

[tlbroadbent]

Format won't work in T-SQL.

Date are not stored in a format or style. They are stored as numbers - so many days before or since Jan 1, 1900. Time is stored as milliseconds since midnight. SQL Server handles datetime to character comparison implicitly if the character format is non-ambiguous - yyyy-mm-dd, mmm dd yyyy, etc.. mm/dd/yyyy is also usable because SQL accepts the American default format.

Check out my article on Handling Dates in SQL Server.

http://tlbroadbent.home.attbi.com/sql_and_access/sql_server_datetime.htm

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[aalmeida]

if you are not sire of the yourdate format you can test it with the isdate function, also atention to the fact 1/10/2002 is not a date on T-SQL the correct format would be 01/10/2002. AL Almeida
NT/DB Admin
&quot;May all those that come behind us, find us faithfull&quot;