Oracle and SQL Server databases use different date/time resolutions, which poses a problem when you compare times from the two databases: The times will rarely be equal. Solve this problem by allowing for a margin of error. Treat the dates and times as floating-point numbers and remember that each day is equal to the whole number 1, and there are 86,400 seconds in a day. This function matches times within five seconds (default) of one another:
Public Function MatchTime(adoFldOracle As ADODB.Field, _ adoFldSQLServer As ADODB.Field, _ Optional ByVal Tolerance As Long = 5) As Boolean Dim dtOracle As Date Dim dtSQLServer As Date Dim dblTolerance As Double Const OneSecond As Double = 1 / 86400 dblTolerance = OneSecond * Tolerance dtOracle = adoFldOracle.Value dtSQLServer = adoFldSQLServer.Value If ((dtOracle > (dtSQLServer + dblTolerance)) Or _ (dtOracle < (dtSQLServer - dblTolerance))) Then MatchTime = False Else MatchTime = True End IfEnd Function
Andy Clark, Richmond, Va.