Solved

How to put milliseconds into an SQL datetime field using void RFX_Date(CFieldExchange* pFX,  const char* szName,  TIMESTAMP_STRUCT& value); ?

Posted on 2009-05-08
4
1,562 Views
Last Modified: 2013-12-14
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.
// 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);

...

}

Open in new window

0
Comment
Question by:tharstern
  • 2
4 Comments
 
LVL 2

Expert Comment

by:Gregdo
ID: 24340701
What data type is the field in your database? DATETIME or SMALLDATETIME?
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 24352412
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)
0
 
LVL 7

Author Comment

by:tharstern
ID: 24352467
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.
0
 
LVL 7

Accepted Solution

by:
tharstern earned 0 total points
ID: 24353090
Ok finally figured it out.
Seems it was the setting of the fraction member of TIMESTAMP_STRUCT.
According to this article: http://support.microsoft.com/kb/263872/en-us the fraction is 1,000,000 * actual milliseconds and any setting of the fraction < 1,000,000 will truncate it to 0 during RFX_Date calls.

So the line m_Date.fraction = sysTime.wMilliseconds; should be:

m_Date.fraction = sysTime.wMilliseconds * 1000000;

Also the ::GetSystemTime(&sysTime); isn't quite right too, it doesn't take into account daylight savings. It should be:

::GetLocalTime(&sysTime);
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is to be the first in a series of articles demonstrating the development of a complete windows based application using the MFC classes.  I’ll try to keep each article focused on one (or a couple) of the tasks that one may meet.   Introductio…
Introduction: Database storage, where is the exe actually on the disc? Playing a game selected randomly (how to generate random numbers).  Error trapping with try..catch to help the code run even if something goes wrong. Continuing from the seve…
The viewer will learn how to use and create keystrokes in Netbeans IDE 8.0 for Windows.
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now