SQL query to kee last 7 days daya and delete rest.

Hi All,



Can anyone give me the SQL query to delete all the data in my table except the last 7 days data.

I have a column for date to validate this.

How to achieve this..can anyone pelase?

Regards,

Dhiraj

To delete all rows older than 7 days, you can use the following syntax -

delete from yourTableName
   where datediff(now(), yourTableName.yourDateColumnName) > 7;

Mahesh Manchala wrote:

To delete all rows older than 7 days, you can use the following syntax -

delete from yourTableName
   where datediff(now(), yourTableName.yourDateColumnName) > 7;

 Thanks mahesh for quick reply.

This didn't work :(

 

Can you try this way once:

delete from yourTableName where yourColumnName NOT IN(select yourColumnName from yourTableName where datediff(now(), yourTableName.yourDateColumnName) > 7)


Mahesh Manchala wrote:

Can you try this way once:

delete from yourTableName where yourColumnName NOT IN(select yourColumnName from yourTableName where datediff(now(), yourTableName.yourDateColumnName) > 7)


 Hi Mahesh,


The error actually is that now() for current date is not a valid function,

also datediff function requires 3 input params.



Thanks.

your right but can we achieve datediff like below:

DATEDIFF ( datepart , startdate , enddate )   

SELECT DATEDIFF(day, CurrDate()-7, CurrDate()) AS DateDiff;

SELECT DATEDIFF(day, '2020/07/24', '2020/07/31') AS DateDiff;



        

I meant to say use CurrDate()  instead of now():

delete from yourTableName where yourColumnName NOT IN(select yourColumnName from yourTableName where datediff(day, CurrDate()-7,CurrDate())


Hi Dhiraj,

Try out the below mentioned SQL Query

DELETE FROM {Entity}
WHERE {Entity}.[Date] < DATEADD(DAY, -7, GETDATE())

Hope this helps you!


Regards,

Benjith Sam

Hi All, 

Thanks for concern,

Got the query.

Ref :- 

DELETE FROM {Table}
WHERE {Table}.[Column] < @LastWeekDate;

 I/P param :- LastWeekDate :- AddDays(CurrDate(), -7)


Thanks,

DM


Glad to hear that we got a solution 

Dhiraj Manwani wrote:

Hi All, 

Thanks for concern,

Got the query.

Ref :- 

DELETE FROM {Table}
WHERE {Table}.[Column] < @LastWeekDate;

 I/P param :- LastWeekDate :- AddDays(CurrDate(), -7)


Thanks,

DM

You're welcome, Dhiraj :)

Happy that you got it solved... :)

Yes, the shared solution will also help you to achieve the use-case but personally I think that introducing an additional SQL input parameter for this would be not a good idea i.e additional security concern may be.. (Just a suggestion - I may be wrong with this thought)


Regards,

Benjith Sam

Benjith Sam wrote:

Dhiraj Manwani wrote:

Hi All, 

Thanks for concern,

Got the query.

Ref :- 

DELETE FROM {Table}
WHERE {Table}.[Column] < @LastWeekDate;

 I/P param :- LastWeekDate :- AddDays(CurrDate(), -7)


Thanks,

DM

You're welcome, Dhiraj :)

Happy that you got it solved... :)

Yes, the shared solution will also help you to achieve the use-case but personally I think that introducing an additional SQL input parameter for this would be not a good idea i.e additional security concern may be.. (Just a suggestion - I may be wrong with this thought)


Regards,

Benjith Sam

 

 Hi Benjit,

when you are using input paratmer as Expan inline yes or inject sql that time Security concern is right but in this case it is straight forward condition. there is no concern of inject sql.


Regards

Rahul

Rahul Sahu wrote:

 Hi Bemjit,

when you are using input paratmer as Expan inline yes or inject sql that time Security concern is right but in this case it is straight forward condition. there is no concern of inject sql.


Regards

Rahul

Thank you Rahul, for the clarification :)


Regards,

Benjith Sam