CodeSOD: A Leap to SQL

When I was a baby programmer, I was taught that part of the power of SQL was that we had a generic, abstract language which meant we could easily change database engines out under our code without having to think about it. In short, I was taught a horrible pack of lies.

For all that SQL has a standard, every database vendor has non-standard features, especially around various built-in functions. The end result is that, if you adopt SQL Server, you're going to be on SQL Server for the life of the application. If you adopt Oracle, you will suffer that choice for the remainder of your existence on this plane and perhaps the next.

What this means is that it behooves you to learn the standard functions of your chosen RDBMS. For example, in T-SQL, SQL Server's dialect of SQL, one of the built-in functions is EOMONTH. Given a date, it tells you the last day of the month, useful for a lot of business processes.

You could use that, or you could do what Darrin's co-worker did:

SET @EDate = (SELECT CAST(CAST(CASE WHEN MONTH(GETDATE())-1 = 0 THEN 12 ELSE MONTH(GETDATE())-1 END AS NVARCHAR) + '/'+CASE WHEN MONTH(GETDATE())-1 = 2 THEN '28'WHEN MONTH(GETDATE())-1 = 4 THEN '30'WHEN MONTH(GETDATE())-1 = 6 THEN '30'WHEN MONTH(GETDATE())-1 = 9 THEN '30'WHEN MONTH(GETDATE())-1 = 11 THEN '30'ELSE '31'END+'/' + CAST(CASE WHEN MONTH(GETDATE())-1 = 0 THEN YEAR(GETDATE())-1 ELSE YEAR(GETDATE()) END AS NVARCHAR(50)) AS DATETIME))

There's a lot going on here, and honestly, the most frustrating thing to see isn't the incorrect date logic, but the string concatenation only to convert it back to a DATETIME at the end. String concatenation in SQL to build a date is some ugly stuff.

But the meat of the WTF, of course, is their "last day of month" logic, specifically around February. It's correct most of the time. Most.

[Advertisement] ProGet can centralize your organization's software applications and components to provide uniform access to developers and servers. Check it out!