?
Solved

Storing SYSTEMTIME in SQL 7

Posted on 2000-02-27
10
Medium Priority
?
743 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

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.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

765 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