Solved

# Query Help

Posted on 2012-09-06
Medium Priority
313 Views
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
0
Question by:rocky050371

Author Comment

ID: 38372720
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

LVL 25

Expert Comment

ID: 38372773
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

LVL 13

Expert Comment

ID: 38372815
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

LVL 93

Accepted Solution

Patrick Matthews earned 2000 total points
ID: 38372930
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

LVL 1

Expert Comment

ID: 38372968
listen to matthewspatrick, he is indeed correct. I can confirm that
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
###### Suggested Courses
Course of the Month12 days, 19 hours left to enroll