SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DATEADD minus 1 month - Questions?


DATEADD minus 1 month - Questions?

Author
Message
trowsell
trowsell
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 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.
BriPan
BriPan
SSC-Addicted
SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)

Group: General Forum Members
Points: 421 Visits: 296
it will consider "older than the 22nd October(today's date) to be 'minus one month'".
Abu Dina
Abu Dina
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2953 Visits: 3325
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
trowsell
trowsell
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 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?
trowsell
trowsell
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 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.
richykong
richykong
Old Hand
Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)Old Hand (358 reputation)

Group: General Forum Members
Points: 358 Visits: 621
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search