# 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

-- last day of next month
###### Who is Participating?

Commented:
To get the first day of the month:

To get the last day of the month:

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
``````
0

Author 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?
0

Commented:
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
, 'next'
, 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
``````
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.
0

Commented:
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.
0

Commented:
listen to matthewspatrick, he is indeed correct. I can confirm that
0
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.