• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 756
  • Last Modified:

Storing SYSTEMTIME in SQL 7

Hi,

we need to be able to store time stamps in our SQL server.  We use the SYSTEMTIME structure to store time, and then convert to a double to store in the SQL server.  We convert using SystemTimeToVariantTime.  When we get the time stamp out of the database we convert back using VariantTimeToSystemTime and find we loose the milliseconds?

We find that when we run a test program shown below that we loose the milliseconds in the reconstructed SYSTEMTIME structure.

Can you convert SystemTimeToVariantTime and VariantTimeToSystemTime and not loose the milliseconds?

We would appreciate being shown how to do this or how to store the SYSTEMTIME structure in SQL 7.

thank you
Mark

// DateTime.cpp : Defines the entry point for the console application.
//
#include "stdafx.h"
#include <atlbase.h>
#include <list>
using namespace std;

int main(int argc, char* argv[])
{
SYSTEMTIME systime;
GetSystemTime(&systime);
printf("Current date is: %d\n", systime.wDay);
printf("Current time is: %d:%d:%d:%d\n", systime.wHour, systime.wMinute,
systime.wSecond, systime.wMilliseconds);

double dVariantTime;
SystemTimeToVariantTime(&systime, &dVariantTime);
printf("The freakin variant time: %ul\n", dVariantTime);
SYSTEMTIME systime2;
VariantTimeToSystemTime(dVariantTime, &systime2);
printf("To check time loss, converted to system time again:
%d:%d:%d:%d\n", systime2.wHour, systime2.wMinute, systime2.wSecond,
systime2.wMilliseconds);
return 0;
}


0
uanmi
Asked:
uanmi
1 Solution
 
ahoorCommented:
Can't you use the function datepart in something?
The milliseconds are in teh database exept you don't get to see them.

By doing an explicit select for the milliseconds

select datepart(ms,datecolumn)

and maybe bind them to the rest of the date...

Arjan
0
 
uanmiAuthor Commented:
No, you miss the point, we store the timestamp in the database, get it back as a double then convert to SYSTEMTIME and we find the milliseconds missing,
gone, finis, not there.  So we can't get them using a call just on the milliseconds
0
 
Gustavo Perez BuenrostroCommented:
uanmi,
What MS-SQL datatype is used to store the variant time value (double type)?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
uanmiAuthor Commented:
yes, we use a double type to store the variant time value
0
 
Gustavo Perez BuenrostroCommented:
uanmi,
Why donĀ“t you post your table's structure?

I need to know what the column's datatype (in wich double type value is stored) is.
0
 
uanmiAuthor Commented:
The column type is datetime.
This is SQL 7.

If you try to run my sample program you will see that our problem lies in the conversion between VariantTimeToSystemTime.  Is there any way to get the datetime type out of SQL 7 and into SystemTime without using VariantTime, as it appears this is the lossy variable.

thank you
Mark
0
 
uanmiAuthor Commented:
Adjusted points to 300
0
 
david_levineCommented:
Here's something I found on the newsgroups...

> > > Can you convert SystemTimeToVariantTime and VariantTimeToSystemTime
> > > and not loose the milliseconds?
> >
> > The SystemTime format is inherently a precise,
> > integer format.  The VariantTime format is an
> > inexact format, based on a floating point value
> > that approximates continous time.  Converting
> > back and forth between them is inherently
> > subject to rounding errors because, for any
> > particular time value, the unit of granularity
> > is different between the two representations.
> > What you want to do is practically impossible.
 
0
 
uanmiAuthor Commented:
I know this, I posted a program that shows the loss after all.
What I'm after is a solution, surely someone stores timestamps in SQL7 and retrieves these for use in C++, or ATL, or COM programming, or something else.
cheers
Mark
0
 
wqwCommented:
first on all, timestamp data type is something completely different from datetime. so you are actually storing datetime stamps :-)

anyway, i suugest that you use string values to update your datetime stamps and datepart function to retrieve milliseconds. check cannonical ODBC datetime format:

select convert(varchar,getdate(),121)
------------------------------
2000-03-08 10:57:49.290

make you datetimes in this format and update/insert them using strings. to retrieve milliseconds use something like:

select YourDatetimeStamp, datepart(ms,YourDatetimeStamp) as Milliseconds
from YourTable

crude solution but it's working.

</wqw>
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now