tharstern
asked on
How to put milliseconds into an SQL datetime field using void RFX_Date(CFieldExchange* pFX, const char* szName, TIMESTAMP_STRUCT& value); ?
I have a class that wraps CRecordset to read/write values to an SQL table using ODBC.
I recently discovered however that when dealing with datetime fields the milliseconds were being lost.
So I looked up RFX_Date and did some googling to find that the version we had used was the CTime version, which has no milliseconds.
OK, so I changed my class from using CTimes to TIMESTAMP_STRUCT.
I ran my code and stepped through and could now see the milliseconds being valid using the following code where m_tDate is the TIMESTAMP_STRUCT which is used in the RFX_Date.
The problem is it isn't actually working! The value in the table is still truncated to the nearest second. I have checked using SQL Server 2005 management studio.
I have also tried using a COleDateTime but also debugs fine but doesn't actually put the milliseconds into the table datetime field.
What am I missing?
Thanks.
I recently discovered however that when dealing with datetime fields the milliseconds were being lost.
So I looked up RFX_Date and did some googling to find that the version we had used was the CTime version, which has no milliseconds.
OK, so I changed my class from using CTimes to TIMESTAMP_STRUCT.
I ran my code and stepped through and could now see the milliseconds being valid using the following code where m_tDate is the TIMESTAMP_STRUCT which is used in the RFX_Date.
The problem is it isn't actually working! The value in the table is still truncated to the nearest second. I have checked using SQL Server 2005 management studio.
I have also tried using a COleDateTime but also debugs fine but doesn't actually put the milliseconds into the table datetime field.
What am I missing?
Thanks.
// From the header
TIMESTAMP_STRUCT m_Date;
// From the source
CProductHistorySet::CProductHistorySet(CDatabase* pdb)
: CRecordset(pdb)
{
m_nDefaultType = dynaset;
SYSTEMTIME sysTime;
::GetSystemTime(&sysTime);
m_Date.year = sysTime.wYear;
m_Date.month = sysTime.wMonth;
m_Date.day = sysTime.wDay;
m_Date.hour = sysTime.wHour;
m_Date.minute = sysTime.wMinute;
m_Date.second = sysTime.wSecond;
m_Date.fraction = sysTime.wMilliseconds;
}
void CProductHistorySet::DoFieldExchange(CFieldExchange* pFX)
{
//{{AFX_FIELD_MAP(CProductHistorySet)
pFX->SetFieldType(CFieldExchange::outputColumn);
RFX_Date(pFX, _T("[Datedb]"), m_Date);
...
}
What data type is the field in your database? DATETIME or SMALLDATETIME?
From help about datatypes in SQL Server:
datetime
Date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007 seconds, as shown in the table.
If you really require milliseconds then a datetime is NOT suitable for storage. (ps the smalldatetime is even worse)
datetime
Date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007 seconds, as shown in the table.
If you really require milliseconds then a datetime is NOT suitable for storage. (ps the smalldatetime is even worse)
ASKER
The field is a datetime and the reason I need to figure this out is because I use stored procedures sometimes and if I use GetDate() then it will store the date/time with milliseconds but all my records created from code/crecordset don't.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.