My ISO 8601-Compliant Signature

My family recently just 'officially' announced that we’re in the process of adopting a child from South Africa. As a family we're quite excited.

There is, of course, a ton of paperwork to do—along with the need for gobs of signatures.

Which got me thinking about my (particularly nerdy) signature—or, more specifically, about how I format dates whenever they're required with my signature. Because, since about 2003 (I think) I've been formatting dates accompanying my signature in ISO-8601 format (i.e., YYYY-MM-DD).

Granted, that's pretty nerdy. But I did it for two primary reasons. First, having lived in Europe for a while, I found that I much preferred their dd/mm/yyyy formatting of dates—compared to the 'lame' way that we format dates in the United States (mm/dd/yyyy).

(To me it makes much more logical sense to either start with the largest unit (year) and work to months then days (the smallest unit), or work from the smallest unit (days) on through months and then into years – as Europeans do it. But, in the US, we 'jump around' by starting ‘in the middle’ with months, jumping to days, and then back up to years.)

So, in this regard, having lived in Europe for a while always caused me confusion after I'd been back in the States for a while and had to write out a date—as I could never remember which format to use (especially since I was always tempted to assume that the most ‘logical’ format would make the most sense). As such, one big reason I started using ISO formatted dates was to make things easier on myself.

The other reason I started using ISO formatted dates was because of my love and passion for SQL Server—and because ISO Formatted Dates are the correct way to format dates within SQL Server.

Is there Such a Thing as a Correct Date Format?

In many environments, 'correct' is a loaded term that ends up being subject to context and perspective. In other situations, 'correct' ends up being an immutable fact.

Date formatting in SQL Server falls somewhere between those two ends of the spectrum. And, in many ways, I suppose it's a bit like the notion of 'correct' within language—or speech. Because, along those lines, I always get cranky when people try to assert that there’s a 'correct' way to speak. There isn't. Instead, anything you can do to get your point across is considered viable. And I ain't going to argue that point. There is, however, also the fact that you ARE judged on how you speak—meaning people will make assumptions about your intelligence based upon how you speak and communicate. It's also worth pointing out that that there are more efficient ways of communicating than others.

Along those lines, while it's possible for users to format their dates in SQL Server pretty much however they'd like, I'd argue that the most efficient way to do so is via ISO-8601 format. Or, as xkcd points out, the purpose of ISO 8601 was to define a globally-accepted standard to describing dates (and other details) in a non-confusing manner.

Consequently, while SQL Server does provide the SET DATEFORMAT setting (which I’ve never, actually, ever seen used in ANY production code – anywhere), Books Online makes it clear that the default format for dates uses an ISO 8601 format in the form of YYYY-MM-DD (and the remarks also clearly call out ANSI and ISO 8601 compliance).

Moreover, getting in the habit of expressing all of your dates in ISO format within SQL Server will pay off great dividends, as certain aspects of SQL Server just 'expect' that you'll use ISO formatted dates under certain scenarios—like Partitioning (where the boundary_value calls out that date boundaries should be declared using ISO formatted dates to ensure that they're valid in all environments).

So, in other words, feel free to keep formatting dates in output or reports as best suits your users' needs (I'm not arguing that you should get in the habit of trying to force nerdy ISO compliance upon your end-users). But, if you want to do things the 'right way' when communicating with SQL Server in your own code and when doing your own modifications, then ISO 8601 is the right way. (And whether or not you want to be a nerd like me and ISO format dates outside of SQL Server is entirely up to you.)

Discuss this Blog Entry 2

on May 10, 2013

Just FYI (and we discussed this on twitter), YYYY-MM-DD is *not* a safr date format in SQL Server. For example:

SET LANGUAGE FRENCH;
SELECT CONVERT(DATETIME, '2012-05-09'); -- yields September 5
SELECT CONVERT(DATETIME, '2012-05-13'); -- yields error

The only truly safe formats across all date-related data types and irrespective of language, dateformat and regional settings are:

YYYYMMDD (no dashes)
YYYY-MM-DDThh:mm[:ss[:ms]] (the T is important)

on May 13, 2013

Thanks for the info!

Please or Register to post comments.

What's Practical SQL Server?

Practical advice, insight, and help for core SQL Server considerations.

Contributors

Michael K. Campbell

Michael K. Campbell is a contributing editor for SQL Server Pro and Dev Pro and is an ASPInsider. Michael is the president of OverAchiever Productions, a consultancy dedicated to technical evangelism...
Blog Archive

Sponsored Introduction Continue on to (or wait seconds) ×