Registrations are now open. Join us today!
There is still a lot of work to do on the wiki yet! More information about editing can be found here.
Already have an account?

Microsoft KB Archive/246438

From BetaArchive Wiki

Article ID: 246438

Article Last Modified on 10/31/2003



APPLIES TO

  • Microsoft ActiveX Data Objects 2.1
  • Microsoft ActiveX Data Objects 2.5
  • Microsoft ActiveX Data Objects 2.6
  • Microsoft ActiveX Data Objects 2.7



This article was previously published under Q246438

SYMPTOMS

When using an ADO Recordset to insert a date/time value into a SQL Server database, the millisecond portion of the value is truncated and the value is rounded off to the nearest second.

RESOLUTION

For a workaround see the "More Information" section.

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

  1. Create a Microsoft Visual C++ console application.
  2. Paste the following code into your implementation file:

    Note You must change the User ID=<username> value and the password = value to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database.

    #import "C:\Program Files\Common Files\System\ado\msado15.dll" no_namespace rename( "EOF", "adoEOF" )
    
    
    int main(int argc, char* argv[])
    {
    
        _ConnectionPtr pCon;
        _RecordsetPtr pRs;
        _variant_t varDate;
        double d;
        _bstr_t btCon;
        _bstr_t btSQL;
    
        CoInitialize(NULL);
    
        try {
    
            pCon.CreateInstance(__uuidof(Connection));
            pRs.CreateInstance(__uuidof(Recordset));
    
            btCon = L"Provider=SQLOLEDB.1;Persist Security Info=False;User ID=<username>;Password=<strong password>;Initial Catalog=Pubs;Data Source=(local)";
            btSQL = L"SELECT * FROM employee WHERE fname='Test'";
            pCon->Open(btCon,L"",L"",-1);
            pRs->Open(btSQL,pCon.GetInterfacePtr(),
                    adOpenStatic,adLockOptimistic,-1);
            
            // THIS CODE TRUNCATES MILLISECONDS BY USING _RecordsetPtr->AddNew():
            pRs->AddNew();
            pRs->GetFields()->GetItem(L"emp_id")->PutValue(L"ABC12345M");
            pRs->GetFields()->GetItem(L"fname")->PutValue(L"Test");
            pRs->GetFields()->GetItem(L"lname")->PutValue(L"Test");
            d = 36438.409988773; //1999-10-05 09:50:23.030
            varDate = d;
            varDate.vt = VT_DATE;
            pRs->GetFields()->GetItem(L"hire_date")->PutValue(varDate);
            pRs->Update();
                          // SET BREAKPOINT ON NEXT LINE:
            pRs->Requery(-1);
            varDate = pRs->GetFields()->GetItem(L"hire_date")->GetValue();
            d = varDate;  // Value now rounded down to the nearest second. (36438.409988426)
    
    
            // THIS CODE CORRECTLY INSERTS MILLISECONDS BY USING _ConnectionPtr->Execute():
            pCon->Execute(L"UPDATE employee SET hire_date = '1999-10-05 09:50:23.030' WHERE fname = 'Test'",&vtMissing,-1);
    
            pRs->Requery(-1);        
            varDate = pRs->GetFields()->GetItem(L"hire_date")->GetValue();
            d = varDate;  // Value now contains the correct value. (36438.409988773)
        
            // Clean up:
            pRs->Delete(adAffectCurrent);  
        }
        catch (_com_error& e)
        {
            HRESULT hr = e.Error();
            const TCHAR* szError = e.ErrorMessage();
            _bstr_t btDesc = e.Description();
        }
        return 0;
    }
                            
  3. Change the connect string to reflect your SQL Server Pubs database
  4. Compile, set a breakpoint (as indicated in the code comments), and run the code.
  5. In stepping through the code, you will see that setting the date to a value containing millisecond precision produces different outcomes depending upon the method used to insert the record:

    1. Using _RecordsetPtr->AddNew() the milliseconds are truncated when the value is stored in SQL Server.
    2. Using _ConnectionPtr->Execute() the milliseconds are preserved and correctly stored in SQL Server.

Workaround

Use the Execute method of the Connection object to update/insert records with millisecond precision and use a string literal for the date value as outlined in the code earlier.

Keywords: kbdatabase kbprb KB246438