CODEWITHSUNDEEP
sql-servert-sqldatediff
Sandeep Thomas
Sandeep Thomas

Reputation: 4759

T-SQL Date function with Integer values as date parameter

I was trying to modify a legacy query and come across this code block

select   DATEADD(MONTH, DATEDIFF(MONTH, -1,getdate() )-1, 1)

But in my belief the dateadd and datediff function needs some date parameter to do the calculation. But here the DateDiff and DateAdd has some integer values at the place of Date parameter

For example DATEDIFF(MONTH, -1,getdate() )-1 returns 1431 which is just a decimal value, but the code block according documentation looks for Date parameter.

https://www.w3schools.com/sql/func_sqlserver_datediff.asp

Same in the case of DateAdd as well

Someone please share some explanation

Upvotes: 2

Views: 1080

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239784

Unfortunately, DATEADD and DATEDIFF are specified to work with datetime (as well as any of the newer datetime datatypes). As such, they inherit a lot of the nastiness of the old type, including that implicit conversions from int to datetime are allowed1.

0 converts to 1900-01-01. -1 converts to 1899-12-31. 1 converts to 1900-01-02. In short, the integer is converted to the day "that many" days after 1900-01-01.

But lets look at your code. It calculates the difference in months from 1899-12-31 and then, having subtracted one, adds that same number of months onto 1900-01-02. The net effect of this is to give you the 2nd of whatever the current month is.

There are simpler ways to write this even keeping the same structure:

select DATEADD(MONTH, DATEDIFF(MONTH, 0, getdate() ), 1)

Or using DATEFROMPARTS, which is preferred because it makes it explicit/obvious what you're doing and uses a more modern datatype (date).

1You're not even allowed explicit conversions from int to datetime2. Because, really, it doesn't make sense.

Upvotes: 6

Related Questions