Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DATEADD minus 1 month - Questions? Expand / Collapse
Author
Message
Posted Thursday, November 22, 2012 3:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 18, 2013 9:05 AM
Points: 8, Visits: 67
Hi, hopefully a bit of an obvious question, but I need to run the following on a daily basis to insert data older than 1 month from today into a different table.

INSERT INTO Table1 (Column1, Column2)
SELECT (Column1, Column2)
FROM Table 2
WHERE [DATE] < DATEADD(mm, -1, GETDATE())

The question is; does this pull back data from older than the beginning of this month or from today's date? By that I mean, if I ran it today would it consider anything older than the 22nd October to be 'minus one month' or would it look at anything older than 1st November?

Also does it also take into consideration shorter months? I.e what does it do on the 1st March for the data from 29th, 30th, 31st Jan?

I have thought of the other option of just doing a WHERE GETDATE() -31, but then for some months I'll end up with a month and a bit....

Hope that makes sense and thanks in advance.
Post #1387807
Posted Thursday, November 22, 2012 4:07 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, January 31, 2014 4:06 AM
Points: 83, Visits: 296
it will consider "older than the 22nd October(today's date) to be 'minus one month'".
Post #1387824
Posted Thursday, November 22, 2012 4:11 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, October 31, 2014 10:27 AM
Points: 708, Visits: 3,293
trowsell (11/22/2012)
Hi, hopefully a bit of an obvious question, but I need to run the following on a daily basis to insert data older than 1 month from today into a different table.

INSERT INTO Table1 (Column1, Column2)
SELECT (Column1, Column2)
FROM Table 2
WHERE [DATE] < DATEADD(mm, -1, GETDATE())

The question is; does this pull back data from older than the beginning of this month or from today's date? By that I mean, if I ran it today would it consider anything older than the 22nd October to be 'minus one month' or would it look at anything older than 1st November?

Also does it also take into consideration shorter months? I.e what does it do on the 1st March for the data from 29th, 30th, 31st Jan?

I have thought of the other option of just doing a WHERE GETDATE() -31, but then for some months I'll end up with a month and a bit....

Hope that makes sense and thanks in advance.


If your date column has a timestamp then it could affect the result. As it stands, if I was to run your query from my PC, this would bring back all records with a date less than 22nd October 11:11AM

Also, I believe dateadd is smart enough to work out an a month. For example,
dateadd(mm, -1,'2012-12-31')

would return 30th November 2012.


---------------------------------------------------------


It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
Post #1387826
Posted Thursday, November 22, 2012 4:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 18, 2013 9:05 AM
Points: 8, Visits: 67
Thanks, much appreciated answers.

I've also just realised another problem that I'm going to come up against.

The next stage in this script I'm writing is that it is going to clear out the data from Table2 that I have just inserted into Table1.
However, it is a high turnover table and in the time it takes to insert the data and gets round to deleting data in the next statement, new data will have been inserted into Table2.

So how would I write a GETDATE minus 1 month from 17:00 that day so I can use exactly the same WHERE clause for the next statement?


Post #1387832
Posted Thursday, November 22, 2012 9:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 18, 2013 9:05 AM
Points: 8, Visits: 67
Ignore me... I'm over complicating everything in my head.... I'll just declare a variable with a select from a temp table where I've inserted a date.
Post #1387935
Posted Wednesday, November 28, 2012 11:46 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 20, 2014 9:30 AM
Points: 132, Visits: 588
Couldn't you do a DELETE with the OUTPUT clause? This will allow you to get the exact records that were deleted.
BOL for OUTPUT clause.

If capturing the deleted record is something that should always be done, maybe you should look into putting a DELETE trigger on the table that will handle pushing deleted records into the secondary table.
Post #1390025
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse