This MSDN page, CAST and CONVERT (T-SQL), specifically the section on DATETIME conversion formats, is easily one of my most frequently visited links.
It really shouldn’t be.
SQL Server is very good at storing and manipulating Date/Time values. There are dedicated data-types for all flavors — DATETIME, DATE, TIME, the newer DATETIME2, and the less common SMALLDATETIME and DATETIMEOFFSET. Then there are the functions & operators that let you do all sorts of fun stuff with them — DATEDIFF, DATEADD, DATEPART, GETDATE, ISDATE, and even some newer ones like EOMONTH and DATEFROMPARTS. These are really powerful tools in the hands of a DB-Developer or DBA.
But you know what SQL Server is not so great at? Reading your mind. Oh, wait, that goes for most applications & systems. Let me rephrase. SQL is not the best platform for knowing how end-users will want their Dates/Times displayed in a contextually/culturally sensitive manner, and executing said preferences.
That’s what we have UX/UI layers for!
While it’s true that the underlying data store (SQL, in this case) needs to be aware of localization & globalization requirements, it shouldn’t be asked to serve-up, say, a Sales-Order-Date in 5 different flavors just because Report X wants it in typical USA fashion (mm/dd/yyyy), User B wants it in “long-form” because they’re reading it like prose (“Jan 13 2016 08:32pm”) in an email, and SSIS Package FooBar needs it in “ISO” format (yyyymmdd) because it’s using the date in a filename! Actually, of those 3 examples, the latter is the most “legit” — or at least, the most justifiable use-case. The other two should have been handled by the overlaying application or middleware — SSRS in the first case, or whatever automation app produced User B’s email in the second.
But surely there’s a good reason that the T-SQL gods included the CAST/CONVERT functionality with all those special date-format arguments, right? Obviously. There are always valid use-cases; or, more proverbially, “There’s a tool for every job.” Just stop abusing the tool!
A typical DBA or DB-Dev is often asked to write ad-hoc queries or build one-off reports to meet some business request, and he/she often doesn’t have the time or the resources to offload the nitty-gritty formatting details to the appropriate layer of abstraction. So yes, that’s why these conversion options exist (among other reasons). And there’s nothing wrong with that, in and of itself. But like anything, if it becomes a bad habit and a hindrance to overall productivity, it’s time to take a step back and re-examine the situation. Ask the hard questions, like “Why am I being asked to create these one-off reports all the time, which sound so similar to each other, yet inevitably are always a bit different?”, or “Have my business users developed unrealistic expectations about what can/should be done by me vs. by other teams/contributors in the organization?”
This isn’t about passing the buck — I’ve already established that’s not my style. It’s about working smarter, bringing more value to the organization by leveraging better technologies and techniques than obsolete habits and old-guard mentality would otherwise allow.
So, dear reader, take the time to learn about SQL’s Date/Time types & functions, including the myriad formatting options of CONVERT. But do yourself a favor and consider, when you find yourself using & abusing them, whether the task at-hand is truly best suited for the database layer, or if it really belongs somewhere else.
Thanks for reading!
One thought on “Dates, Times, and Datetimes, Oh My!”