In T-SQL, what does negative one mean when passed in as a date in the DATEDIFF method?-CodePudding

I'm trying to understand the following T-SQL code:

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

What does -1 indicate when passing into DATEDIFF? According to this, it should be a date parameter.

CodePudding user response：

Here, the -1 for the 2nd parameter for DATEDIFF, and the 3rd parameter for DATEADD will be implicitly converted to a datetime. You can find what the value would be with a simple expression:

SELECT CONVERT(datetime, -1);

Which gives 1899-12-31 00:00:00.000. For the "old" date and time data types ((small)datetime), they allow conversion from numerical data type (such as an int or decimal). 0 represents 1900-01-01 00:00:00 and each full integer value represents a day. So -1 is 1899-12-31 00:00:00.000, 2 would be 1900-01-03 00:00:00.000 and 5.5 would be 1900-01-06 12:00:00.000 (as .5 represents 12 hours).

For the new date and time data types, this conversion does not exist.

In truth, the above could likely be much more easily written as the following though:

SELECT EOMONTH(GETDATE(),-1);

Here the -1 means the end of the month 1 month prior to the date of the first parameter (in this case the current date). The second parameter for EOMONTH is optional, so EOMONTH(GETDATE()) would return the last day of the current month, and EOMONTH(GETDATE(),2) would return the last day of the month in 2 months time (2023-03-31 at time of writing).