DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) Can someone explain me this

Question

DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) 

Can someone explain me this.

Answer 1

this will give you the first of the month for a given date

inner select select DATEDIFF(MONTH, 0, GETDATE()) will give the number of months from 1900-01-01

here it is 1350

this will be add to 1900-01-01 , but only the months

select DATEADD(MONTH,1350,0) will give 2012-07-01 00:00:00.000

which is the start of the current month.

I think this is the most efficient way to find the starting of a month for any given date.

Answer 2

The DateDiff function returns how many seconds, months, years - whatever interval you specify between the first date (here 0) and the second date (here the current date).

     DATEDIFF(MONTH, 0, '2-14-2015')  --returns month. 0 is for 1/1/1900, and getdate is the current date       --(i used a set date bc dates will change as this post gets older).      result: 1381 

In my workings, I used the date of 2-14-2015 and it gave me 1381 number of months since 1/1/1900. I put 1381 into the dateadd function like so...

     select Dateadd(MONTH, 1381, 0)      result: 2015-02-01 00:00:00.000 

Basically, it gets the first day of the whatever month is specified in the date of the innermost formula.

The code i was having to figure out went a step farther and subtracted 1 second from that result to get the last day of the month at 11:59:59 like so...

     select DATEADD(s, -1, '2015-02-01 00:00:00.000') 

The Formula all put together looks like this:

     DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,getdate())+1,0)) 

Hope this helps someone. :)