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 5 (of 5 total)

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

Jeff Moden SSC Guru Points: 1003853 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: 246686 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

...

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Jonathan AC Roberts SSCoach Points: 18257 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: 287024 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: 18257 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.