Dates, Times, and Datetimes, Oh My!

There’s a tool for every job. Just stop abusing the tool!

Advertisements

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.

i just want to use dates
Is that so much to ask?  Courtesy of this guy’s blog, which sounds like a great place to learn iOS programming if one was interested in such things…

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.

i can has string to store dates
Because once wasn’t enough…

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!

broken-hammer-pulling-nail
Abused tools can fail you…

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.

calvin-and-susie-arguing
“Why are you making me write these horrible queries!?” .. “Because you’re the DBA!” .. “Fine, but give me the resources to automate this for the future.”

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!

Author: natethedba

I'm a SQL Server DBA, family man, and all-around computer geek.

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