How do select previous 18 months of data?

Question

Post reply

How do select previous 18 months of data?

montgomery johnson

Default port

Points: 1466
More actions
May 24, 2006 at 12:14 pm

#366269

I need to do rolling date select of the previous 18 months of data, not including the current month. For example: 11/30/2004 through 04/30/2006. Any help would be appreciated.
Thank you

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

mimorr SSCarpal Tunnel Points: 4850 More actions · Answer 1

Take a look at the datediff function in Books Online...I think that will do what you need.

Michelle

montgomery johnson Default port Points: 1466 More actions · Answer 2

I was hoping for a detailed example. I would think this is common for automated jobs.

Jules Bonnot SSCertifiable Points: 5488 More actions · Answer 3

select col

from table

where date between dateadd('mm',getdate(),1) and dateadd('mm',getdate(),18)

www.sql-library.com[/url]

montgomery johnson Default port Points: 1466 More actions · Answer 4

This helps, but I don't see how this gets me the data for only the previous 18 full months not including the current month. In other words 11/01/2004 through 04/30/2006. I meant 11/01, not 11/30.

Thank you

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 5

There may be an easier way, but this works....

select

dateadd( day, 0, cast( datepart ( month, dateadd( month, -18, getdate() ) ) as varchar(2) ) +

'/01/' + cast( datepart( year, getdate() ) as varchar(4) ) ),

dateadd( day, -1, cast( datepart ( month, getdate() ) as varchar(2) ) +

'/01/' + cast( datepart ( year, getdate() ) as varchar(4) ) )

/* if the date you are comparing against includes time, you'll need to

subtract one second from the current month */

select

convert( datetime, cast( datepart ( month, dateadd( month, -18, getdate() ) ) as varchar(2) ) +

'/01/' + cast( datepart( year, getdate() ) as varchar(4) ), 101),

dateadd ( second, -1, convert( datetime, cast( datepart ( month, getdate() ) as varchar(2) ) +

'/01/' + cast( datepart( year, getdate() ) as varchar(4) ), 101) )

I would suggest using a function.

Hope this helps.

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

[KH] SSCommitted Points: 1908 More actions · Answer 6

where datecol >= dateadd(month, datediff(month, 0, getdate()) - 18, 0)

and datecol < dateadd(month, datediff(month, 0, getdate()), 0)

Jules Bonnot SSCertifiable Points: 5488 More actions · Answer 7