Microsoft KB Archive/246438

Knowledge Base

PRB: Inserting a Date/Time Value into SQL Server with ADO Loses Milliseconds

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

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.