Query Help

Posted on 2012-09-06
Last Modified: 2012-10-02
I am trying to understand the following SQL statements for calculating the first and last day of the next month. Can someone break it down for me

-- first day of next month
SELECT dateadd(mm,0,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))
-- last day of next month
SELECT dateadd(dd,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0))
Question by:rocky050371

    Author Comment

    As an addition to this, surely you just need

    DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0) in the first case.

    Why does it default to the 1st?
    LVL 25

    Expert Comment

    Break it down
    SELECT dateadd(mm,0,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)) first_next_month
         , DATEDIFF(m,0,GETDATE())    as months_between_now_and_year_0
         , DATEDIFF(m,0,GETDATE())+1  as plus_one_month
         , DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0) as add_above_months_to_year_0
         , dateadd(mm,0,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)) as add_another_0_months  -- superflous
         , 'next'
         , dateadd(dd,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0)) as first_this_month
         , DATEDIFF(m,0,GETDATE())    as same_as_above
         , DATEDIFF(m,0,GETDATE())+1  as plus_two_months
         , DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0) as add_above_months_to_year_0  -- gives 1st of month after next
         , dateadd(dd,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0)) as less_one_day  -- gives last day of prior month

    Open in new window

    Why the first ... by year 0 it is the 1st of January too.  So when you a whole number of months, it still is the first of the month.
    LVL 13

    Expert Comment

    In the first case you're getting the total amount of months since date 0 (01-01-1900) until today and adding one. Then you convert to a full date, which would be the first of the month.

    In the second case you get the total amount of months since date 0 until today (as in the first case), but this time you add two (one more month). Then you subtract one day, thus giving you the last day of next month.
    LVL 92

    Accepted Solution

    To get the first day of the month:

    DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)

    To get the last day of the month:

    DATEADD(day, -1, DATEADD(month, DATEDIFF(month, 0, GETDATE()) + 1, 0))

    However, if what you really want is "give me everything with a date happening this month", if there is any possibility that your dates will have a time portion, you are better off using the first day of the next month:

    DATEADD(month, DATEDIFF(month, 0, GETDATE()) + 1, 0)

    For example:

    DECLARE @start datetime = DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0),
        @end datetime = DATEADD(month, DATEDIFF(month, 0, GETDATE()) + 1, 0)
    SELECT *
    FROM foo
    WHERE MyDate >= @start AND MyDate < @end

    Open in new window

    LVL 1

    Expert Comment

    listen to matthewspatrick, he is indeed correct. I can confirm that

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    best way to upgrade 3 29
    How to query LOCK_ESCALATION 4 24
    Complex Update with SubQuery 4 0
    Query question 4 0
    Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
    How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now