Advanced SQL Server DATE and DATETIME Handling

DATETIME’s Precision

OK. I’m joking above. The truth is that SQL Server stores DATETIME with a precision of 3.33 Milliseconds (0. 00333 seconds). The exact value is rounded in steps of 0.000, 0.003 and 0.007. This is what caused the strange behavior above.

There are two ways to get around this problem. The first, and better one, is to write a dll for an extended stored procedure in C++. The other one is to code an ActiveX dll (e.g. with VB) and embed it in an user defined function.

To stay consistent in this article and not introduce a third language, I’ll show you the ActiveX solution. It is also faster to implement.

At first you have to create an ActiveX project, call it ‘SQLTimer’ and add a class with the name ‘clsSQLTimer’. Then write the following function into the class:

Public Function GetSQLTimer() As Double
GetSQLTimer = Int(Now) + Timer / 86400
End Function

As you may have noticed, it is the same algorithm you saw earlier in this article.

Set the (VB6) project’s properties to ‘Unattended Execution’ and ‘Retained In Memory’.

After compilation into the ‘SQLTimer.dll’, you should move this file to the ‘Binn’ directory of your SQL-Server installation and use regsvr32 on it (regsvr32 “C:\YOUR PATH\Binn\SQLTimer.dll”)!

The next step is to call the GetSQLTimer – function from an user defined SQL Server function.

As you may already know, you first have to create an instance of the ActiveX, then call the function, and finally you have to deallocate the component from memory.

And here is the ActiveX function:

Create Function udfGetTime() Returns float(53)
As
Begin
Declare @intObject int, @fltTimer float(53)
Exec sp_OACreate 'SQLTimer.clsSQLTimer', @intObject OUTPUT -- create ActiveX
Exec sp_OAMethod @intObject, 'GetSQLTimer', @fltTimer OUTPUT -- call timer function
Exec sp_OADestroy @intObject -- destroy ActiveX
Return @fltTimer
End

Of course you can improve this code with more error handling

The benefit of storing dates as float is:

1. higher precision,
2. a higher range of dates (01.01.0100 to 31.12.9999)!
3. it’s compatible with SQL-Server’s datetime datatype -within it’s date-range.

Point 3 can be checked with this script:

Select Cast(dbo.udfGetTime() as datetime)

At last you must change the spTestSQL procedure to:

Alter Procedure "spTestSQL"
@strSQL as nvarchar(400),
@dtStart float(53) OUTPUT,
@dtEnd float(53) OUTPUT
As
Set nocount on
Declare @intObject int

Exec sp_OACreate 'SQLTimer.clsSQLTimer', @intObject OUTPUT -- create ActiveX

Exec sp_OAMethod @intObject, 'GetSQLTimer', @dtStart OUTPUT -- get start
Exec sp_executeSQL @strSql
Exec sp_OAMethod @intObject, 'GetSQLTimer', @dtEnd OUTPUT -- get end

Exec sp_OADestroy @intObject -- destroy ActiveX
return

Finally change the VB code from section ‘Check your SQL’ and set the Parameter datatype of @dtStart and @dtEnd to adDouble.

Now, this is what is on my local SQL Server installation:

Code started at: 2004-07-28 02:59:02.145
Server started at: 2004-07-28 02:59:02.145
Sever ended at: 2004-07-28 02:59:02.396
Code ended at: 2004-07-28 02:59:02.396

Full duration: 00:00:00.251
Server duration: 00:00:00.251
Network duration: 00:00:00.000

This makes a lot more sense.

I hope this article has shown you some of the issues with VB's DATE ad SQL Server's DATETIME, and how to get around them.

Reprinted with the express written permission of the author. Copyright 2004