CodeSOD: A Pre-Packaged Date


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]

Otter

,

ProGet

,

BuildMaster

– robust, powerful, scalable, and reliable additions to your existing DevOps toolchain.

http://ift.tt/2uxZn8o

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: