Deleting records that are more than a month old

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 2005 Forums

Transact-SQL (2005)

Deleting records that are more than a month old

Author

Topic

Rupa
Posting Yak Master

123 Posts

Posted - 2010-05-21 : 07:39:18

Hello all,

I would like to delete records that are more than a month old. I have written the following select query to view what it will delete before I actually delete them:

SELECT Badge.Badge,PC_AllData.LastUpdate
FROM Badge, PC_AllData
WHERE ((CAST(Badge.Badge AS VARCHAR))=(Right(Magstripe,9))OR (CAST(Badge.Badge AS VARCHAR))=(Right(Magstripe,7)) OR (CAST(Badge.Badge AS VARCHAR))=(Right(Magstripe,6)) OR ((CAST(Badge.Badge AS VARCHAR))=PC_AllData.MSerialShort)) AND ((PC_AllData.Status='L') OR (PC_AllData.Status='S')) AND ((PC_AllData.LastUpdate) < (CAST(dateadd(day, datediff(day, 0, getdate()), -30) AS VARCHAR)))

I am getting the following results:

126027556 2010-05-21 05:30:42.373000000

Not sure if this is because the LastUpdate field contains time as well?

Any help will be highly appreciated.

Many thanks,

Rupa

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-21 : 07:54:23

[quote}
(PC_AllData.Status='S')) AND ((PC_AllData.LastUpdate) < (CAST(dateadd(day, datediff(day, 0, getdate()), -30) AS VARCHAR)))

[/quote]

you are converting the datetime field to varchar. In that case it cannot be used for calculating days difference.

If PC_AllData.LastUpdate is of datetime datatype then you can directly use it as
dateadd(day, datediff(day, 0,PC_AllData.LastUpdate),0) < dateadd(day, datediff(day, 0, getdate()), -30)

In case it is stored as varchar which i know is wrong but its seen commonly in our practical experience then you can try the
below way..

convert(datetime,left(PC_AllData.LastUpdate,10),120) < dateadd(day, datediff(day, 0, getdate()), -30)

Please let us know if you are still getting wrong result.

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

Rupa
Posting Yak Master

123 Posts

Posted - 2010-05-21 : 08:57:08

It is stored as varchar unfortunately.

That worked like a charm. Thank you so much.

Best wishes,

Rupa

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-21 : 09:04:21

Glad to help..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources