Friday, September 20, 2013

First and Last day of Previous Month

In the accounting world I so often need to limit my selection criteria to the first and last day of the previous month. This is certainly not a secret formula, but I now have a place where I can look it up. Sadly, I have not memorized this formula

First Day of Previous Month:

DATEADD (m,-1, DATEADD(d,1-DATEPART(d,GETDATE()),GETDATE()))

Last Day of Previous Month:

DATEADD(d,-DATEPART(d,GETDATE()),GETDATE())

Example:

SELECT * FROM SOP30200
WHERE  

VOIDSTTS = 0 and DOCDATE between
DATEADD (m,-1, DATEADD(d,1-DATEPART(d,GETDATE()),GETDATE())) and
DATEADD(d,-DATEPART(d,GETDATE()),GETDATE())

Until next post!

2 comments:

Anonymous said...

Hi Leslie,

It's not about memorizing it, it's about knowing where to find it! :-) You might find some of the other formulas here useful:
http://victoriayudin.com/2010/09/13/coding-specific-dates-in-sql-server/

-Victoria

Dynamics Confessor said...

Wow Victoria!

This is great stuff, I'll learn one day to look at your site first!

Leslie