CODEWITHSUNDEEP
sqlsql-server
Crabster
Crabster

Reputation: 145

T-Sql Confusion regarding third argument of dateadd function

Can someone please help me understand how the following two queries give the exact same result?

SELECT DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) + 1, -1) 
SELECT DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) + 1, 0)- 1

I understand how the query written below works

 SELECT DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) + 1, 0)

I suppose I am not able to wrap my head around the "-1" in the two queries.

Upvotes: 1

Views: 537

Answers (2)

Iztoksson
Iztoksson

Reputation: 990

Both statements return last day of current month, this is the flow:

SELECT DATEDIFF(MM, 0, GETDATE()) /* Number of months from year 0=1900-01-01 (1389) */
SELECT DATEDIFF(MM, 0, GETDATE()) + 1 /* Adds 1 month to above int (1390) */
SELECT DATEADD(MM, 1390, 0) /* Adds 1390 back to year 0 (2015-11-01) */
-1 simply takes one day off this date and you get 2015-10-31

Key is GetDate()+1 where it adds 1 day(!) but this only works on old date and time formats, if you try this it will fail:

SELECT CAST(0 As DateTime2) -1

while this works:

SELECT CAST(0 As DateTime) -1

To clarify: GetDate() returns DateTime type and this is ok to use +i, but if you ever try to do a +1 to DateTime2 you will get and error.

Upvotes: 2

Dhaval
Dhaval

Reputation: 2379

let's Break Down the query...

select DATEDIFF(MM, 0, GETDATE()) + 1 //it will give 1390 as OutPut

now put this output on you query..

 SELECT DATEADD(MM, 1390, -1) // it will give 2015-10-31 00:00:00.000
 SELECT DATEADD(MM, 1390, 0) // it will give 2015-11-01 00:00:00.000

Now put all thin in our original query

SELECT DATEADD(MM, 1390, -1) //it will give 2015-10-31 00:00:00.000
SELECT DATEADD(MM, 1390, 0)- 1 //first dateadd function give this 2015-11-01 00:00:00.000 date then we subtract it by 1 day. hence it will give same op as above query.

Upvotes: 1

Related Questions