Pages

▼

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!

at

2 comments:

  1. AnonymousSeptember 23, 2013 at 12:33 PM

    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

    ReplyDelete
  2. Dynamics ConfessorSeptember 26, 2013 at 12:35 PM

    Wow Victoria!

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

    Leslie

    ReplyDelete