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))
rocky050371Asked:
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)

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

Open in new window

0
 
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?
0
 
lwadwellCommented:
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.
0
 
LIONKINGCommented:
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
 
LonestarTechExpertCommented:
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.

All Courses

From novice to tech pro — start learning today.