Query Help

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))
Who is Participating?
Patrick MatthewsCommented:
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)

FROM foo
WHERE MyDate >= @start AND MyDate < @end

Open in new window

rocky050371Author Commented:
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?
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.
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.
listen to matthewspatrick, he is indeed correct. I can confirm that
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.