?
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
Medium Priority
?
1,647 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Introduction: Ownerdraw of the grid button.  A singleton class implentation and usage. Continuing from the fifth article about sudoku.   Open the project in visual studio. Go to the class view – CGridButton should be visible as a class.  R…
Here is a helpful source code for C++ Builder programmers that allows you to manage and manipulate HTML content from C++ code, while also handling HTML events like onclick, onmouseover, ... Some objects defined and used in this source include: …
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
The viewer will learn how to use NetBeans IDE 8.0 for Windows to connect to a MySQL database. Open Services Panel: Create a new connection using New Connection Wizard: Create a test database called eetutorial: Create a new test tabel called ee…
Suggested Courses

762 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