November 22, 2012 at 3:32 am
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.
Dh g gjhfvghhgfdfg
November 22, 2012 at 4:07 am
it will consider "older than the 22nd October(today's date) to be 'minus one month'".
November 22, 2012 at 4:11 am
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[/url]
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
November 22, 2012 at 4:25 am
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?
Dh g gjhfvghhgfdfg
November 22, 2012 at 9:23 am
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.
Dh g gjhfvghhgfdfg
November 28, 2012 at 11:46 am
Couldn't you do a DELETE with the OUTPUT clause? This will allow you to get the exact records that were deleted.
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply