Problem / Solution
Testing T-SQL application code that has time dependencies can be a very difficult and error prone task. By borrowing a technique from Test-Driven-Development and using a mock-time instead of the real time it becomes easy to test how a stored procedure or function would behave at some other time in the past or the future. This is done by replacing calls to getdate() from within application code with a function that returns the mock-time when run during a test.
Key Learning Items
- How to create your own current date/time using a date in the past or future
- Examples using DATEPART, DATEFROMPARTS, DATEADD
- Learn about CONTEXT_INFO()
Read Companion Tip
Andy Novick is a SQL Server Developer in the Boston area with 25 years of database and application development experience. His consulting practice focuses on building applications, including software products that use SQL Server’s capabilities to the maximum. He has developed both OLTP and data warehouse databases with multi-terabytes scale. He has particular expertise in automating data management for such large databases. Other recent projects have included ETL, Security and SQL Injection prevention. Andy is the originator of the popular “SQL Server Loadfest” event held in Waltham, MA.
- MSSQLTips Awards: Trendsetter (25+ tips) – 2014
