Date math and leap years
Some of you may have experience using R and here we note that leap year date math can be tricky with R and the lubridate package. lubridate has two types of functions: duration and period.
lubridate::ymd(20120229) - lubridate::dyears(4) --> 2008-03-01, which is wrong.
lubridate::ymd(20120229) - lubridate::dyears(1) --> 2011-03-01, which is correct.
lubridate::ymd(20120229) - lubridate::years(4) --> 2008-02-29, which is correct.
lubridate::ymd(20120229) - lubridate::years(1) --> NA, which is unexpected behavior.
We can use the DATEADD() and DATEDIFF() functions to see how SQL Server deals with leap years to see if it has any of the same eccentricities.
Este ejercicio forma parte del curso
Time Series Analysis in SQL Server
Ejercicio interactivo práctico
Prueba este ejercicio y completa el código de muestra.
DECLARE
@LeapDay DATETIME2(7) = '2012-02-29 18:00:00';
-- Fill in the date parts and intervals as needed
SELECT
DATEADD(___, ___, @LeapDay) AS PriorDay,
DATEADD(___, ___, @LeapDay) AS NextDay,
-- For leap years, we need to move 4 years, not just 1
DATEADD(YEAR, ___, @LeapDay) AS PriorLeapYear,
DATEADD(YEAR, ___, @LeapDay) AS NextLeapYear,
DATEADD(___, -1, @LeapDay) AS PriorYear;