farblog

I was digging through some old blogmaterial recently, and ran across an ancient blog post that I’d written but never posted (partly because I hadn’t got around to checking what my then-current employer’s policy was on blogging, and partly because at the time I hadn’t yet yak-shaved the static site generator that I used back then into existence).

By way of background: when I wrote this, in July 2005, I was working at a ‘Business Intelligence’ consultancy implementing data warehouses using Microsoft SQL Server (2005, I think), and I was very much not a professional programmer: I’d only done a small amount of C# and Visual Basic development in my career to date, and a bunch of C (and some C++) at home.

Still, I think it’s interesting that, over eight years later, I’m still trying to solve problems with dates and times.

(A word of warning: all I’ve done with the below is to read it through and convert the text from a custom XML schema (!) to Markdown for posting; I haven’t been able to check the behaviour described.)

One of these things is not the same:

select datediff(minute, '2005-07-11 18:02', '2005-07-12 12:48')

-----------
42886

select datediff(minute, '2005-05-21 07:00', '2005-05-21 21:56')

Server: Msg 242, Level 16, State 3, Line 2
The conversion of a char data type to a datetime data type resulted
in an out-of-range datetime value.

Boggle. Can you see the difference?

So, above, I’m trying to find out the number of minutes between two different points in time. It looks like it’s working for one case, but not for the other. What’s up?

Well, what’s up is another example of Microsoft SQL Server’s ‘unexpected’ behaviour. This one is to do with the implicit conversion of string literals to datetime types.

It’s pretty convenient to be able to enter a literal datetime value in SQL scripts, rather than litter the code with cast() operators, and, although T-SQL doesn’t provide that ability in the same way as, say, Visual Basic, it does allow for run-time coercion from strings to dates, and unlike Visual Basic, you don’t have to use the horrible-looking (and US-oriented) #mm/dd/yy# syntax.

Except in this case, there’s a catch. Let’s see what SQL Server’s default output format is:

select getdate()

------------------------------------------------------
2005-07-12 14:17:18.680

Okay. Now let’s convert that back into a date.

select cast('2005-07-12 14:17:18.680' as datetime)

------------------------------------------------------
2005-12-07 14:17:18.680

Oops. In my locale (British English), Microsoft SQL Server’s default output format is something compatible with ISO 8601 (it’s actually the ‘ODBC canonical with milliseconds’ style), but the default input format is something entirely different, interpreting the date as the utterly bizarre yyyy-dd-mm.

What we need to do instead is to make sure we use the SQL Server ‘ISO’ format. This is exactly the same as the format we tried to use above, but without the hyphens:

select cast('20050712 14:17:18.680' as datetime)

------------------------------------------------------
2005-07-12 14:17:18.680

With that change, the original example now works, and we can also see that the misinterpretation of the original dates (December 7^th instead of July 12^th) meant that our original result was wrong.

select datediff(minute, '20050711 18:02', '20050712 12:48')

-----------
1126

select datediff(minute, '20050521 07:00', '20050521 21:56')

-----------
896

We could also have run set dateformat ymd to change the default date format for the session, but depending upon non-default session-specific behaviour is probably not a good idea in the long run.

Today’s lesson: Microsoft SQL Server does not follow the principle of least surprise. Paradoxically, this should not come as a surprise to anyone.