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,550 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Programmer's Notepad is, one of the best free text editing tools available, simply because the developers appear to have second-guessed every weird problem or issue a programmer is likely to run into. One of these problems is selecting and deleti…
Introduction: Hints for the grid button.  Nested classes, templated collections.  Squash that darned bug! Continuing from the sixth article about sudoku.   Open the project in visual studio. First we will finish with the SUD_SETVALUE messa…
The viewer will learn how to use and create keystrokes in Netbeans IDE 8.0 for Windows.
The viewer will learn how to use and create new code templates in NetBeans IDE 8.0 for Windows.

743 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

13 Experts available now in Live!

Get 1:1 Help Now