SQL Server vs .NET DateTime Leap Year Arithmetic

I recently refactored some code that was performing date arithmetic in SQL Server. The code was attempting to retrieve the "same day next year" by adding 365 days to the date provided. I was refactoring this logic out of SQL Server and in to .NET, and I was concerned that .NET's DateTime arithmetic wouldn't match SQL Server. I was pleasantly surprised that the results were the same. However, I should call out that perhaps in your situation adding 365 days to a given date is not appropriate for getting the "same day next year" value. I also compared this logic when preforming a "Add years + 1" and the results were also the same. (However, to be clear, adding 365 days differed from adding 1 year, but both SQL and .NET preformed the same given the method used.)

source code: https://gist.github.com/aaronhoffman/3d997390dab7f69e6597

results table:

MethodValueStartDate.NET ResultSQL Result
AddDays3652/27/20112/27/20122/27/2012
AddYears12/27/20112/27/20122/27/2012
AddDays3652/28/20112/28/20122/28/2012
AddYears12/28/20112/28/20122/28/2012
AddDays3653/1/20112/29/20122/29/2012
AddYears13/1/20113/1/20123/1/2012
AddDays-3652/27/20122/27/20112/27/2011
AddYears-12/27/20122/27/20112/27/2011
AddDays3652/27/20122/26/20132/26/2013
AddYears12/27/20122/27/20132/27/2013
AddDays-3652/28/20122/28/20112/28/2011
AddYears-12/28/20122/28/20112/28/2011
AddDays3652/28/20122/27/20132/27/2013
AddYears12/28/20122/28/20132/28/2013
AddDays-3652/29/20123/1/20113/1/2011
AddYears-12/29/20122/28/20112/28/2011
AddDays3652/29/20122/28/20132/28/2013
AddYears12/29/20122/28/20132/28/2013
AddDays-3653/1/20123/2/20113/2/2011
AddYears-13/1/20123/1/20113/1/2011
AddDays3653/1/20123/1/20133/1/2013
AddYears13/1/20123/1/20133/1/2013
AddDays-3652/27/20132/28/20122/28/2012
AddYears-12/27/20132/27/20122/27/2012
AddDays-3652/28/20132/29/20122/29/2012
AddYears-12/28/20132/28/20122/28/2012
AddDays-3653/1/20133/1/20123/1/2012
AddYears-13/1/20133/1/20123/1/2012


Hope this helps,
Aaron


Comments

Post a Comment

Popular posts from this blog

Search iPhone Text Messages with SQLite SQL Query

Image
While you're here, check out some of our interactive visualizations: Would you like to visualize your text messages? ------------------------------------------------------------------------------- In my experience, the iPhone text message search functionality is usually pretty awful. Especially if you're trying to find a text from a few years ago. Luckily, if you backup your iPhone using iTunes, your text messages are exported/stored in a SQLite database, and it is fairly easy to query. Here are the steps to SQL Query your Text Messages.   1. Backup your iPhone using iTunes.   2. Find the SQLite file that contains your text messages .     - in ~/Library/Application Support/MobileSync/Backup/* with a filename of 3d0d7e5fb2ce288813306e4d4636395e047a3d28   3. Ensure you have a SQLite Query tool ( SQLiteBrowser is pretty good ).   4. Open your SQLite Text Message DB File using your favorite SQLite query tool.   5. Execute this query to see all your mess
Read more

How to Turn Off Microsoft Arc Touch Mouse Scroll Sound Vibration

Image
I recently purchased a Microsoft Arc Touch Mouse and wanted to turn off the middle button wheel scroll sound vibration. It is possible, however perhaps not obvious.  First you need to download the "Mouse and Keyboard Center" executable available on this page https://www.microsoft.com/accessories/en-us/products/mice/arc-touch-mouse/rvf-00052#techspecs-connect . (direct link Win10 x64: https://go.microsoft.com/fwlink/?linkid=849754  ) Run the executable and install the software.  After the software is installed, turn off "Vibration." See image below: Hope this helps, Aaron p.s.  Want to make that next report really pop ? Check out this new tool I'm working on : drag-and-drop interactive data visualization
Read more

Configure SonarAnalyzer.CSharp with .editorconfig, no need for SonarCloud or SonarQube

Our goal was to use  SonarSource / SonarQube  static code analysis with the most "minimal" install/configuration footprint. We wanted the static code analysis to break/fail the build on the local developer machines as well as our CI/CD Azure DevOps environment if a rule was violated. SonarSource products usually rely on some external source,  SonarCloud /SonarQube to determine which rules to apply on the local dev machine, and to store the results. We wanted to eliminate these dependencies. SonarSource also provides a Visual Studio extension called  SonarLint  to help check the static code analysis rules within the Visual Studio IDE without needing an external source for the rules. The static code analysis rules SonarLint enforces are also defined in the  SonarAnalyzer.CSharp  nuget package. This nuget package is a set of  Roslyn Code Analyzers . In .NET Core, Roslyn Code Analyzers are triggered during a build if the nuget package is referenced by the project. Also in .NET Co
Read more