Query Help

Posted on 2012-09-06
Medium Priority
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

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?
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
     , 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

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.
LVL 93

Accepted Solution

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


Expert Comment

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

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

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

579 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