Advanced SQL Server DATE and DATETIME Handling

By : Daniel Pfaff May 25, 2004

Printer friendly

There are lot of situations where you need an exact timestamp. For example, you may want to differentiate a recordsets’ creation dates, store technical measurements, or just to see how fast your latest SQL is running. Coding the 4th dimension is another common task, and therefore milliseconds are essential.

But there is a problem receiving milliseconds with ADO from SQL Server.

On one hand, VB with ADO, like this example:

Debug.Print cn.Execute("SELECT GetDate()").Fields(0)

returns:

23.07.2004 12:03:49

But on the other hand, running the same code directly in Query Analyzer returns:

23.07.2004 12:03:49.997

Even with:

Debug.Print FormatDateTime(cn.Execute("SELECT GetDate()").Fields(0), vbLongTime)

you won’t receive the milliseconds.

Okay, you can do a conversion to characters on the server and return that string like this:

Debug.Print FormatDateTime(cn.Execute("SELECT Convert(varchar, GetDate(),21)").Fields(0)

Which returns:

23.07.2004 12:14:07.993

But in my opinion, it’s to expensive to send such a long string over the net and store it in a much larger structure, than it could be possible in its native numeric form! There must be a way to handle milliseconds at the client.

Another problem is that SQL Server stores DATETIME with a precision of 3.33 milliseconds (0. 00333 seconds). This is not sufficient in some circumstances.

So let's take a look at what we can do.

The VB DATE & SQL Server DATETIME Datatypes

From the example above, it seems like ADO strips the milliseconds, but it really doesn’t, as I’ll show you later. First of all, you should understand how SQL Server’s DATETIME and VB’s DATE represent their data.

Both use 8 bytes to store the data, where the first 4 bytes are the date and the last 4 bytes represent the time.

The data type DOUBLE can store 8 bytes too, which means you can transform DATETIME to DOUBLE with no loss of information!

And last, you should always remember:

• SQL Server only stores dates from: 01.01.1753 to 31.12.9999
• VB’s date range runs from: 01.01.0100 to 31.12.9999

So you can always convert SQL Server dates to VB, but the other direction won’t work with historical dates. The conclusion is that you can handle both dates later than 01.01.1753.

The Lost Milliseconds

So why does VB strip the milliseconds? The answer: It doesn’t!

VB’s standard for date/time operations is based on seconds, as it’s minimal unit. (E.g. the Now() function returns the current system time and rounds its milliseconds.) Consequently, FormatDateTime and other date/time function do the same. They are built for VB and ‘exotic’ values from outside its world are treated fine, by rounding them.

If you want to measure milliseconds in VB you can use the Timer() Function on the Windows OS, but this won’t work with the MAC OS (MAC just returns full seconds). Timer() returns the seconds after midnight as single data type and the fractional part will contain the milliseconds! We’ll use this function in the later section, “check your SQL!”

Receiving Milliseconds from SQL Server with ADO

As I previously explained, VB naturally doesn’t expect milliseconds. For this reason you have to write a small function, to teach it how to understand them.

Before you start, you should realize there are 86400 seconds per day (86400s/d = 24 h/d * 60 min/h * 60 s/min). The following function will strip the milliseconds from the SQL DATETIME and format the entire date expression:

'returns the fractional part of a positive double
'in VB.Net you can substituted this with the truncate function
Function Frac(dbl As Double) As Double
Frac = dbl - Fix(dbl)
End Function

'returns a formatted date with milliseconds
Function FormatDt(ByVal dt As Double, Optional strFormat As String = "yyyy-mm-dd hh:mm:ss") As String
Dim dblMSec As Double
' strip the full seconds of the day from dt, to return the percentage of the seconds left.
dblMSec = Frac(86400 * dt)
' remove the milliseconds from dt, so that format won't round it (because it's 0)
dt = dt - (dblMSec / 86400)
'return the formatted dt.
FormatDt = Format(dt, strFormat) & Format(dblMSec * 1000, "\.000")
End Function

The following function will test this approach:

Sub TestFormatDate()
Dim cmd As New ADODB.Command, rst As New ADODB.Recordset
'init command to get the date and it's string equivalent, to compare them later
With cmd
.ActiveConnection = "Your connection string here"
.CommandText = "SELECT GetDate(), Convert(varchar,getdate(),21)"
.CommandType = adCmdText
End With
rst.Open cmd

'show results and compare the values
MsgBox "Server: " & rst(1).Value & vbCrLf & "Daniel: " & FormatDt(rst(0).Value)

rst.Close: Set rst = Nothing: Set cmd = Nothing
End Sub