Microsoft’s SQL Server Integration Services is an ETL tool that attempts to mix visual programming (for designing data flows) with the reality that at some point, you’re just going to need to write some code. Your typical SSIS package starts as a straightforward process that quickly turns into a sprawling mix of spaghetti-fied .NET code, T-SQL stored procedures, and developer tears.

TJ L. inherited an SSIS package. This particular package contained a step where a C# sub-module needed to pass a date (but not a date-time) to the database. Now, this could be done easily by using C#’s date-handling objects, or even in the database by simply using the DATE type, instead of the DATETIME type.

Instead, TJ’s predecessor took this route instead:

CREATE PROC [dbo].[SetAsOfDate]
        @Date datetime = NULL
AS
SELECT @Date = CASE WHEN YEAR(@DATE) < 1950 THEN GETDATE()
                                        WHEN @Date IS NULL THEN GETDATE()
                                        ELSE @Date
                                END;

SELECT CAST(FLOOR(CAST(@Date AS FLOAT)) AS DATETIME) AS CurrentDate

The good about this code is that it checks its input parameters. That’s defensive programming. The ugly is the less-than 1950 check, which I can only assume is a relic of some Y2K bugfixes. The bad is the `CAST(FLOOR(CAST(@Date AS FLOAT)) as DATETIME).

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!