Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 774
  • 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
Building an Effective Phishing Protection Program

Join Director of Product Management Todd OBoyle on April 26th as he covers the key elements of a phishing protection program. Whether you’re an old hat at phishing education or considering starting a program -- we'll discuss critical components that should be in any program.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

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