• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 209
  • Last Modified:

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?
0
PapaStevens
Asked:
PapaStevens
  • 4
  • 3
  • 3
2 Solutions
 
chapmandewCommented:
select * from table
where datefield >= dateadd(m, -18, datefield)
0
 
Kevin CrossChief 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now