Skip to Content (Press Enter)
2019-10-04 15-01-22
Dhiraj Manwani
134Views
12Comments
SQL query to kee last 7 days daya and delete rest.
Question

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

0
0
31 Jul 2020
Copy Link
2026-04-01 11-40-30
Mahesh Manchala

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

delete from yourTableName
   where datediff(now(), yourTableName.yourDateColumnName) > 7;
0
0
31 Jul 2020
Copy Link
2019-10-04 15-01-22
Dhiraj Manwani

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 :(

 

0
0
31 Jul 2020
Copy Link
2026-04-01 11-40-30
Mahesh Manchala

Can you try this way once:

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


0
0
31 Jul 2020
Copy Link
2019-10-04 15-01-22
Dhiraj Manwani

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.

0
0
31 Jul 2020
Copy Link
2026-04-01 11-40-30
Mahesh Manchala

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;



        

0
0
31 Jul 2020
Copy Link
2026-04-01 11-40-30
Mahesh Manchala

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())


0
0
31 Jul 2020
Copy Link
2021-03-18 21-03-15
Benjith Sam
 
MVP

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

1
0
31 Jul 2020
Copy Link
2019-10-04 15-01-22
Dhiraj Manwani

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


1
0
31 Jul 2020
Copy Link
2021-03-18 21-03-15
Benjith Sam
 
MVP

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

0
0
31 Jul 2020
Copy Link
2026-02-26 06-29-24
Rahul
 
MVP

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

0
0
31 Jul 2020
Copy Link
2021-03-18 21-03-15
Benjith Sam
 
MVP

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

0
0
31 Jul 2020
Copy Link
2026-04-01 11-40-30
Mahesh Manchala

Glad to hear that we got a solution 

1
0
31 Jul 2020
Copy Link
Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.