SQL Fascination

One of the long time annoyances about the Date functions in SQL is that a number of them do not function as developer would wish, the prime example I’m going to focus on is the DateDiff function. The way it operates is very contrary to what a developer might expect or want from it – the function counts the number of boundaries that crossed for the specific units selected, not the number of whole units between the dates, as an example:

declare @date1 datetime
declare @date2 datetime
set @date1 = '20100101 23:00:00'
set @date2 = '20100102 01:00:00'
select datediff(d, @date1, @date2)

And the result is 1, since the units selected was days, the boundary line is predictably at midnight, so even though the time span is only 2 hours, it would count as 1 day – that is not intuiative. Now this is all documented so we cannot complain or grumble. If you wanted to know whether a full day has passed, you used hourly units instead and made sure you had the logic to deal with this.

All of this leaves you with a pretty bad resolution however, you can get the hours difference, but minutes and seconds are not available – so you have to datediff on those units and do some maths. It really makes for a ham-fisted way of getting a duration.

So people work around the problem by converting the date to the numeric equivalent and manipulating that directly.

declare @date1 datetime
set @date1 = '20100101 12:15:30'
select convert(float,@date1) 40177.5107638889

The decimal part represents the percentage through the day, which is not really how the underlying binary storage stores it, it uses a number to represent the number of 1/300ths of a second since the day started.

This format was very forgiving though, if you wanted to add a day, instead of using DateAdd, you could just add 1 to the number, very convienient.

It does however make it easier to create a pseudo-timespan by deducting one date’s numeric representation from another dates, although the code is somewhat long-winded. As a side note, make sure you convert to float and not real – real has not go sufficient accuracy for this to work.

declare @date1 datetime
declare @date2 datetime
set @date1 = '20100101 12:00:00'
set @date2 = '20100102 13:15:35'
declare @result float
set @result = convert(float,@date2) - convert(float,@date1) declare @DurationDays float
declare @DurationTime float
declare @DurationHours float
declare @DurationMinutes float
declare @DurationSeconds float
set @DurationDays = floor(@result)
set @DurationTime = (@result - floor(@result) )
set @DurationTime = @DurationTime * 86400 set @DurationHours = floor(@DurationTime / 3600)
set @DurationTime = @DurationTime - @DurationHours * 3600
set @DurationMinutes = floor(@DurationTime/60)
set @DurationTime = @DurationTime - @DurationMinutes * 60
set @DurationSeconds  = @DurationTime
select @DurationDays as Days,  @DurationHours as Hours ,  
@DurationMinutes as Minutes,  @DurationSeconds as Seconds

Days              Hours              Minutes           Seconds
----------------- ------------------ ----------------- -----------------
1                 1                  15                35.0000002188608

Bit of a hack and was it really any shorter or better? Debatable. Whilst it can get time span information out, when used within SQL 2008 using the new datetime2 types, the wheels fall off:

declare @date1 datetime2(7)
set @date1 = '20100101 12:00:00'
select convert(float,@date1)
Msg 529, Level 16, State 2, Line 3 Explicit conversion from data type datetime2 to float is not allowed.

And that is where the problem comes in – the new datetime2 types will not allow the date to be converted to a number, and a number of these developer tricks no longer work.

Most if not all the tricks can be re-written to use multiple date functions with some mathematical logic – and it can be done without the code ever knowing if it had been embedded within a function / stored procedure.  Where you would see a less transparent move to the datetime2 data types would be where developers had embedded some of the tricks directly into ad-hoc SQL, that will fail if the type is altered. In an ideal world, is that the code never contains these techniques of course, but we do not all live in that nirvana.

So on the one hand Datetime2 gives great accuracy and can reduce storage, but on the other hands, the tricks used in the past to deal with the inadequacies of the built-in Date functions no longer work.

What I would really like is a revamp of the Date functions and the introduction of  a time span type – could be a long wait.