mySQL datetime field reports as Long Integer with ODBC

[Boutang]

I'm using Access 2003 as a reporting tool against a mySQL database,
connecting with the mySQL ODBC driver 3.51.17.

The ODBC driver reports every datetime field as a Long Integer in Access.
This makes date operations problematic.

I notice an interesting post in this forum from 2005 that would work fine
("Convert UNIX time to windows general date"), but using a function with
every row seems like an unnecessary performance hit. Also, it seems
counter-intuitive that the driver doesn't recognize the correct field type.

The problem looks suspiciously like the mySQL bug report #8363 (3.51.11
incorrectly describes Date field) shown at
http://bugs.mysql.com/bug.php?id=8363, but this bug is several years & many
versions old now -- I'm sure it would have been fixed.

Anybody experienced this before? Can you point me to a fix?

 

[Michel Walsh]

That probably because it uses the number of (milli-)seconds since some date,
while Jet uses the number of days (and decimal) since 30th December 1899.

If you need to compare to a Jet-given date, in the WHERE clause, let say,
then convert the Jet-given date, instead of converting all the rows from
MySQL.

Sure, if you need to display the data, as date, you then need to convert the
unix-time into a Jet-time, but that will occurs AFTER the where clause would
have, hopefully, eliminate lot of rows.


Vanderghast, Access MVP

 

[Boutang]

Yeah, thanks. Cottoned on to that very quickly when I started performance
tuning.

For what it's worth, here are my two functions.

Still hoping for a better ODBC driver that would handle this directly.

Function ConvertMySQLDateToAccess(ByVal pvarTimeStamp As Variant, _
Optional pdteReturnWhenNull As Date = #1/1/100#) As Date
On Error GoTo ConvertMySQLDateToAccessErr
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
' Ins: pvarTimeStamp - value of mySQL timestamp value
' Outs: returns an Access date value
' Access stores dates as # of fractional days since 1899. Unix stores dates
as # of
' seconds since 1970. So, you need a function to convert between them.
' For some reason, my app also has a 1/4 day offset from 1/1/1970.
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
' Declarations & Setup
Dim dte As Date

' Main Line
If Len(pvarTimeStamp & "") <> 0 Then 'ADH says this is faster than
IsNull()
dte = DateAdd("s", pvarTimeStamp, #1/1/1970# - 0.25)
Else 'pvarTimeStamp is null
dte = pdteReturnWhenNull
End If
ConvertMySQLDateToAccess = dte

ConvertMySQLDateToAccessBye:
Exit Function

ConvertMySQLDateToAccessErr:
Select Case Err.Number
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description & vbCrLf _
& vbCrLf & DBEngine.Errors(0).Description, _
vbCritical, "basMySQLDateTimeFunctions:
ConvertMySQLDateToAccess"
End Select
ConvertMySQLDateToAccess = pdteReturnWhenNull
Resume ConvertMySQLDateToAccessBye
End Function

Function ConvertAccessDateToMySQL(ByVal pvarTimeStamp As Variant, _
Optional pdteReturnWhenNull As Date = #1/1/100#) As Long
On Error GoTo ConvertAccessDateToMySQLErr
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
' Ins: pvarTimeStamp - value of Access date
' Outs: returns a mySQL date/time value
' Access stores dates as # of fractional days since 1899. Unix stores dates
as # of
' seconds since 1970. So, you need a function to convert between them.
' For some reason, my app also has a 1/4 day offset from 1/1/1970.
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
' Declarations & Setup
Dim lng As Long

' Main Line
If Len(pvarTimeStamp & "") <> 0 Then 'ADH says this is faster than
IsNull()
lng = DateDiff("s", #1/1/1970# - 0.25, pvarTimeStamp)
Else 'pvarTimeStamp is null
lng = pdteReturnWhenNull
End If
ConvertAccessDateToMySQL = lng

ConvertAccessDateToMySQLBye:
Exit Function

ConvertAccessDateToMySQLErr:
Select Case Err.Number
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description & vbCrLf _
& vbCrLf & DBEngine.Errors(0).Description, _
vbCritical, "basMySQLDateTimeFunctions:
ConvertAccessDateToMySQL"
End Select
ConvertAccessDateToMySQL = pdteReturnWhenNull
Resume ConvertAccessDateToMySQLBye
End Function