Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Storing SYSTEMTIME in SQL 7

Posted on 2000-02-27
10
Medium Priority
?
746 Views
Last Modified: 2008-03-03
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
Comment
Question by:uanmi
[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
10 Comments
 
LVL 3

Expert Comment

by:ahoor
ID: 2564254
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
 
LVL 3

Author Comment

by:uanmi
ID: 2564274
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
 
LVL 4

Expert Comment

by:Gustavo Perez Buenrostro
ID: 2565075
uanmi,
What MS-SQL datatype is used to store the variant time value (double type)?
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 3

Author Comment

by:uanmi
ID: 2565105
yes, we use a double type to store the variant time value
0
 
LVL 4

Expert Comment

by:Gustavo Perez Buenrostro
ID: 2565482
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
 
LVL 3

Author Comment

by:uanmi
ID: 2566946
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
 
LVL 3

Author Comment

by:uanmi
ID: 2567001
Adjusted points to 300
0
 
LVL 9

Expert Comment

by:david_levine
ID: 2567365
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
 
LVL 3

Author Comment

by:uanmi
ID: 2567392
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
 
LVL 4

Accepted Solution

by:
wqw earned 1200 total points
ID: 2595119
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.

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

636 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