Click here to Skip to main content
15,867,308 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a table with huge data that i want to delete last 15 years old data from the current date.
below is my sample data

Id	Name	Status	LastUpdateDate
1	Name      	Terminated	21-09-2022
2	Name      	Active	22-09-2022
3	Name      	Terminated	23-09-2022
4	Name      	Terminated	24-09-2022
5	Name      	Terminated	25-09-2022
6	Name      	Terminated	26-09-2022
7	Name      	Terminated	27-09-2022
8	Name      	Active	21-09-2022
9	Name      	Terminated	21-09-2022
10	Name      	Terminated	21-09-2006
11	Name      	Terminated	22-09-2006
12	Name      	Terminated	23-09-2006
13	Name      	Terminated	24-09-2006
14	Name      	Terminated	25-09-2006
15	Name      	Terminated	21-09-2022
16	Name      	Terminated	21-09-2022
17	Name      	Terminated	21-09-2022
18	Name      	Terminated	21-09-2022
19	Name      	Terminated	21-09-2022
20	Name      	Terminated	21-09-2022
21	Name      	Terminated	21-09-2022
22	Name      	Terminated	21-09-2022
23	Name      	Terminated	21-09-2022
24	Name      	Terminated	21-09-2022
25	Name      	Terminated	21-09-2022
26	Name      	Terminated	21-09-2022
27	Name      	Terminated	21-09-2022
28	Name      	Terminated	21-09-2022
29	Name      	Terminated	21-09-2022
30	Name      	Terminated	21-09-2022
31	Name      	Terminated	21-09-2022
32	Name      	Terminated	21-09-2022
33	Name      	Terminated	21-09-2022
34	Name      	Terminated	21-09-2022
35	Name      	Terminated	21-09-2022
36	Name      	Terminated	21-09-2022
37	Name      	Terminated	21-09-2022
38	Name      	Terminated	21-09-2022
39	Name      	Terminated	21-09-2022
40	Name      	Terminated	21-09-2022
41	Name      	Terminated	21-09-2022
42	Name      	Terminated	21-09-2022
43	Name      	Terminated	21-09-2022
44	Name      	Terminated	21-09-2022
45	Name      	Terminated	21-09-2022
46	Name      	Terminated	21-09-2022
47	Name      	Terminated	21-09-2022
48	Name      	Terminated	21-09-2022
49	Name      	Terminated	21-09-2022
50	Name      	Terminated	21-09-2022
51	Name      	Terminated	21-09-2022
52	Name      	Terminated	21-09-2022
53	Name      	Terminated	21-09-2022
54	Name      	Terminated	21-09-2022
55	Name      	Terminated	21-09-2022


What I have tried:

I have tried with the below command could you please provide a better approach to achieve this?

I have tried with select command once get proper command i will delete instead of select the below command
SELECT TOP 1000 [Id]
      ,[Name]
      ,[Status]
      ,[LastUpdateDate]
  FROM [sample1].[dbo].[PersonHistory] where LastUpdateDate < dateadd(YEAR,-15,GETDATE())
Posted
Updated 20-Sep-22 11:07am

1 solution

Assuming that the LastUpdateDate column is DATE, DATETIME, or DATETIME2, the DELETE command will be correct:
SQL
DELETE FROM PersonHistory WHERE LastUpdateDate < dateadd(YEAR,-15,GETDATE())


If it's VARCHAR or NVARCHAR then you have a real problem and you should strongly consider changing your DB (and supporting presentation software) to correct that before proceeding any further.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900