WEBINAR: On-demand webcast
How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017 REGISTER >
This document describes one possible way of how you can safely store a Visual Basic 6 Date datatype in an XML Document. Recently, I spent a significant amount of time trying to solve a very simple problem. In a Visual Basic 6 application, I had a variable of a Date datatype that I had to store in an XML DOM document. This seems easy enough; however, the problem is doing it without losing any of the accuracy of the Date datatype. Storing a Date in an XML file with a dateTime.tz datatype won't save all the milliseconds of this date. The method presented in this article works as long as you handle the XML document completely in VB6.
It appears that the if statement in the following code sometimes (depending on the accuracy of myDate1) returns dates are different:
' Creating an XML document with a root element Set xmlDoc = New MSXML2.DOMDocument Set xmlRoot = xmlDoc.createElement("root") Set xmlDoc.documentElement = xmlRoot ' Assigning our date to the root element xmlRoot.dataType = "dateTime.tz" xmlRoot.nodeTypedValue = myDate1 ' Serializing our document to XML, loading it again and ' retrieving our saved date xmlDoc.loadXML (xmlDoc.xml) myDate2 = xmlDoc.documentElement.nodeTypedValue ' Comparing the two dates... If myDate1 <> myDate2 Then Call MsgBox("Dates are different") Else Call MsgBox("Dates are the same") End If
The source of this problem is Visual Basic 6's inability to handle milliseconds stored in dates. These other options also don't work or are too big of a headache to implement in VB6:
- Converting it to a string removes all milliseconds.
- Converting it to a Double and storing it as a string-typed XML element makes you worry about regional settings in VB6.
- Converting it to a Double and storing it as a float-typed XML element also loses some accuracy.
This behavior appears to be independent of the version of the Microsoft XML Parser used. I've tried using Microsoft XML v2.6, v3.0, v4.0 and v5.0, and in each case this behavior appears.
The code with this article is a small VB6 project that you can use to reproduce this behavior. It uses Microsoft XML v4.0, but because this is installed by default on a Windows XP (if I remember correctly), you should be able to run it perfectly if you have VB6 installed. If you look at the source of this project, you'll see that you have to do some effort to reproduce this problem. I have encountered this problem because I was handling dates that were originally calculated by the GETDATE() function of SQL Server 2000, which is accurate to about three milliseconds. You will never see the problem, however, if you only use the Now function of Visual Basic because this function never returns a date that is sufficiently accurate.
The solution that I've used simply reads the memory used by the date, converts each byte to its hexadecimal representation, and saves this in a string. This string then can safely be used in an XML element without losing accuracy and without worrying about regional settings. For example, the date 2005-09-30 17:07:59.623 is converted to 3F5C24D836DCE240.
Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" _ (pDst As Any, pSrc As Any, ByVal ByteLen As Long) Private Type DateTimeStructure Value(7) As Byte End Type Public Function AccurateDateToString(ByVal lcDate As Date) As String Dim strDate As String Dim objDate As DateTimeStructure Dim i As Byte Call CopyMemory(ByVal VarPtr(objDate), ByVal VarPtr(lcDate), 8) For i = 1 To 8 strDate = strDate & Right("0" & Hex(objDate.Value(i - 1)), 2) Next AccurateDateToString = strDate End Function Public Function AccurateStringToDate(ByVal lcDate As String) As Date Dim dtmDate As Date Dim objDate As DateTimeStructure Dim i As Byte For i = 1 To 8 objDate.Value(i - 1) = Val("&h" & mID(lcDate, (i * 2) - 1, 2)) Next Call CopyMemory(ByVal VarPtr(dtmDate), ByVal VarPtr(objDate), 8) AccurateStringToDate = dtmDate End Function
As you can see, the code contains two functions—one called AccurateDateToString that can be used to convert a Date to its string representation, and one called AccurateStringToDate that does the reverse. Because the date stored in the XML file is nothing more than a memory dump of a VB6 date, it'll be difficult to use it in other environments. For me, this wasn't an issue because the XML file that I created is only used internally in my project and, because of this, there never is any need to access it using another application.