Results 1 to 3 of 3
  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    First day of previous month (SQL)

    I have a scheduled report that needs to be run on a monthly basis. During any particular month I want to run it from the first day of the previous month to the last day of the previous month. I have mmanaged to calculate the last day of the previous month, i just cannot get the first day of the previous month. Any ideas?

    If I run this I get today - 1 month to the end of the month:

    dtinsertdate between DATEADD(mm,-1,getdate()) and dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate() ), 0))

    emboldened characters show the bit I am stuck on.
    Jerry

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: First day of previous month (SQL)

    The last day of the previous month = Date() - Day( Date() )

    The 1st day of the previous month is calculated by subtracting one month from the 1st day of the current month, like this:
    DateAdd("m",-1, Date() - Day( Date() ) +1)
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: First day of previous month (SQL)

    Thanks Mark, perfect. I have adjusted the syntax to fit my SQL 2000 script , thus:

    DateAdd("m",-1, getDate() - Day( getDate() ) +1)
    Jerry

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •