How do I select a date 18 months in the past?

I need to build an archive job for SQL 2000, sp4. The mechanics of the actual archiving are pretty straightforward, but I need to limit the archiving to data that older than the 1st day of the month, 18 months ago.

Is there a way within T-SQL to select a month 18 months in the past?
PapaStevensAsked:
Who is Participating?
 
chapmandewConnect With a Mentor Commented:
select * from table
where datefield >= dateadd(m, -18, datefield)
0
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
Something like this?
SELECT *
FROM TableName
WHERE DateColumn < DateAdd(mm, -18, DateAdd(dd, -day(getdate())+1, DateDiff(dd, 0, getdate())))

Open in new window

0
 
PapaStevensAuthor Commented:
These are terrific starts, and I think based on chapmandew's response I have an inkling as to how to proceed. I realize, though, that I've not clarified what I needed as well as I should have - what I need is to select the first day of the month, from the month that is 18 months prior to today's date.

0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
chapmandewCommented:
maybe this?


select * from table
where cast(month(datefield) as varchar(2)) + '/1/' + cast(year(datefield) as varchar(4)) >= dateadd(m, -18, cast(month(datefield) as varchar(2)) + '/1/' + cast(year(datefield) as varchar(4)))
0
 
PapaStevensAuthor Commented:
How would I modify that to only select the date from 18 months ago? In other words, I just need to generate the date, not necessarily select from the table, etc. Apologies for not being clear, it's difficult for me to put into words ;)
0
 
chapmandewCommented:
try this:

select cast(month(getdate()) as varchar(2)) + '/1/' + cast(year(getdate() as varchar(4))
0
 
Kevin CrossChief Technology OfficerCommented:
Did my suggestion not work -- http:#22869863?

You said archive, so I gave you all the dates that were less than the first day of the month 18 months ago which is 2007-05-01 00:00:00.000.  If you want to include that day, then that is an easy change.
0
 
Kevin CrossChief Technology OfficerCommented:
To select just the date as I did it:
SELECT DateAdd(mm, -18, DateAdd(dd, -day(getdate())+1, DateDiff(dd, 0, getdate())))

Open in new window

0
 
PapaStevensAuthor Commented:
Many thanks, folks - I'll be able to use a combination of solutions from both mwvisa1 and chapmandew, so I'm upping the points and splitting them among both of you. I hope that's okay, and I thank you both for your patience and willingness to work with me ;)
0
 
Kevin CrossChief Technology OfficerCommented:
Glad to help.  ;)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.