Moving data older than 30 days

Question

Moving data older than 30 days

regrahc

SSC Veteran

Points: 250
More actions
January 10, 2016 at 6:51 pm

#399545

Hello,
SQL Newbie here - need to move data to another table after it's 30 days old - not getting an error message, but it isn't moving data that I know should be moved. Any ideas?
Stored Procedure:
INSERT INTO [dbo].[2016_Expireds]
SELECT *
FROM [dbo].[Expireds_New]
WHERE CAST(LEFT(OMD, 8) as datetime) + 30 < GETDATE();
DELETE FROM [dbo].[Expireds_New]
WHERE CAST(LEFT(OMD, 8) as datetime) + 30 < GETDATE();
Side note: have also tried the following WHERE clause with the same effect:
WHERE convert(varchar,[Expireds_New].[OMD],101) <= DateAdd(d,-30,GETDate());
Tables (Expireds_New and 2016_Expireds both formatted the same):
CREATE TABLE [dbo].[Expireds_New](
[Address] [varchar](50) NOT NULL,
[City] [varchar](50) NULL,
[State] [text] NULL,
[Zip] [nvarchar](15) NOT NULL,
[MLS#] [nchar](10) NULL,
[Status] [varchar](20) NULL,
[OMD] [varchar](10) NULL,
[Followup_Date] [varchar](10) NULL,
[First_Owner] [varchar](50) NULL,
[Second_Owner] [varchar](50) NULL,
[Phone1] [nvarchar](15) NULL,
[Phone2] [nvarchar](15) NULL,
[Phone3] [nvarchar](15) NULL,
[Email] [varchar](50) NULL,
[Remarks] [varchar](max) NULL
Thanks in advance for any and all help!!

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 1

regrahc (1/10/2016)
Hello,
SQL Newbie here - need to move data to another table after it's 30 days old - not getting an error message, but it isn't moving data that I know should be moved. Any ideas?
Stored Procedure:
INSERT INTO [dbo].[2016_Expireds]
SELECT *
FROM [dbo].[Expireds_New]
WHERE CAST(LEFT(OMD, 8) as datetime) + 30 < GETDATE();
DELETE FROM [dbo].[Expireds_New]
WHERE CAST(LEFT(OMD, 8) as datetime) + 30 < GETDATE();
Side note: have also tried the following WHERE clause with the same effect:
WHERE convert(varchar,[Expireds_New].[OMD],101) <= DateAdd(d,-30,GETDate());
Tables (Expireds_New and 2016_Expireds both formatted the same):
CREATE TABLE [dbo].[Expireds_New](
[Address] [varchar](50) NOT NULL,
[City] [varchar](50) NULL,
[State] [text] NULL,
[Zip] [nvarchar](15) NOT NULL,
[MLS#] [nchar](10) NULL,
[Status] [varchar](20) NULL,
[OMD] [varchar](10) NULL,
[Followup_Date] [varchar](10) NULL,
[First_Owner] [varchar](50) NULL,
[Second_Owner] [varchar](50) NULL,
[Phone1] [nvarchar](15) NULL,
[Phone2] [nvarchar](15) NULL,
[Phone3] [nvarchar](15) NULL,
[Email] [varchar](50) NULL,
[Remarks] [varchar](max) NULL
Thanks in advance for any and all help!!

What do you get when you run the following?

SELECT TOP 10 LEFT(OMD, 8), OMD

FROM [dbo].[Expireds_New]

ORDER BY OMD

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Phil Parkin SSC Guru Points: 247180 More actions · Answer 2

regrahc (1/10/2016)
Hello,
SQL Newbie here - need to move data to another table after it's 30 days old - not getting an error message, but it isn't moving data that I know should be moved. Any ideas?
Stored Procedure:
INSERT INTO [dbo].[2016_Expireds]
SELECT *
FROM [dbo].[Expireds_New]
WHERE CAST(LEFT(OMD, 8) as datetime) + 30 < GETDATE();
DELETE FROM [dbo].[Expireds_New]
WHERE CAST(LEFT(OMD, 8) as datetime) + 30 < GETDATE();
Side note: have also tried the following WHERE clause with the same effect:
WHERE convert(varchar,[Expireds_New].[OMD],101) <= DateAdd(d,-30,GETDate());
Tables (Expireds_New and 2016_Expireds both formatted the same):
CREATE TABLE [dbo].[Expireds_New](
[Address] [varchar](50) NOT NULL,
[City] [varchar](50) NULL,
[State] [text] NULL,
[Zip] [nvarchar](15) NOT NULL,
[MLS#] [nchar](10) NULL,
[Status] [varchar](20) NULL,
[OMD] [varchar](10) NULL,
[Followup_Date] [varchar](10) NULL,
[First_Owner] [varchar](50) NULL,
[Second_Owner] [varchar](50) NULL,
[Phone1] [nvarchar](15) NULL,
[Phone2] [nvarchar](15) NULL,
[Phone3] [nvarchar](15) NULL,
[Email] [varchar](50) NULL,
[Remarks] [varchar](max) NULL
Thanks in advance for any and all help!!

Having a date stored as a Varchar() is generally agreed to be bad practice. Do you have any scope for introducing a DATE column instead? This could be indexed & would make everything run more smoothly.

If you ever add more columns to [dbo].[Expireds_New], or remove any columns, your

INSERT INTO

SELECT *

Syntax will mean that you get an error. Takes longer to write, but safer syntax is

INSERT INTO tbl (col1, col2)

SELECT col1, col2

...

Jonathan AC Roberts SSCoach Points: 18407 More actions · Answer 3

CAST(LEFT(OMD, 8) as datetime) + 30 < GETDATE();

This is not good practice, I'm not sure it will even work in versions of SQL Server later than 2005.

You should be using the DATEADD function, i.e.:

DATEADD(dd,30,CONVERT(datetime,LEFT(OMD, 8)) < GETDATE();

Ed Wagner SSC Guru Points: 287026 More actions · Answer 4

Jonathan AC Roberts (1/31/2016)
CAST(LEFT(OMD, 8) as datetime) + 30 < GETDATE();
This is not good practice, I'm not sure it will even work in versions of SQL Server later than 2005.
You should be using the DATEADD function, i.e.:
DATEADD(dd,30,CONVERT(datetime,LEFT(OMD, 8)) < GETDATE();

The storage of a date is never a good practice. A good idea would be to storage dates in a date/datetime column.

The conversion of a string to a datetime can cause errors if the string contains invalid date values. Even if it doesn't, it'll still be much slower than it would be using a date data type. Doing a calculation on a column will also be slower than it needs to be because the engine has to perform the calculation on every row in the table and then perform the comparison.

A good way of doing the comparison would be to compare a date column against a date that's 30 days ago.

Example: WHERE some_date < DATEADD(day, -30, GETDATE())

Integer addition and subtraction against a datetime data type does work after SQL 2005.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Jonathan AC Roberts SSCoach Points: 18407 More actions · Answer 5

Ed Wagner (1/31/2016)
Jonathan AC Roberts (1/31/2016)
CAST(LEFT(OMD, 8) as datetime) + 30 < GETDATE();
This is not good practice, I'm not sure it will even work in versions of SQL Server later than 2005.
You should be using the DATEADD function, i.e.:
DATEADD(dd,30,CONVERT(datetime,LEFT(OMD, 8)) < GETDATE();
The storage of a date is never a good practice. A good idea would be to storage dates in a date/datetime column.
The conversion of a string to a datetime can cause errors if the string contains invalid date values. Even if it doesn't, it'll still be much slower than it would be using a date data type. Doing a calculation on a column will also be slower than it needs to be because the engine has to perform the calculation on every row in the table and then perform the comparison.
A good way of doing the comparison would be to compare a date column against a date that's 30 days ago.
Example: WHERE some_date < DATEADD(day, -30, GETDATE())
Integer addition and subtraction against a datetime data type does work after SQL 2005.

I agree, I didn't mention changing the type of the column to datetime as I just assumed he was just trying to get the job done on a badly implemented design.

Also, putting the DATEADD(day, -30, GETDATE()) on the right hand side, as you did, makes the query sargable should indexes ever bee added to the date column.