T-SQL Date function with Integer values as date parameter

Question

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

Note that W3School pages are often inaccurate. Always refer to official documentation — Cid
w3school is w3school - a site with entry level HTML and Javascript tutorials with some SQL databases thrown in. It's not SQL Server's documentation. It's not Mozilla's MDN either - don't expect it to have better docs on the Fetch or File API than MDN. It may be a nice way to find out about the existence of a function, but the actual docs are far better and quite often easier to use. Even google returns the doc sites before W3School — Panagiotis Kanavos
This manual page on W3School is wrong : "Return the difference between two date values, in years:" This is wrong. This returns an int that is the difference between 2 dates, but in the format defined by the first argument. DATEDIFF(MONTH, ...) returns the number of monthes, not years. — Cid

Damien_The_Unbeliever Damien_The_Unbeliever · Accepted Answer · 2019-04-11T08:10:22

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 allowed¹.

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).

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

T-SQL Date function with Integer values as date parameter

1 Answers