Solved

Storing SYSTEMTIME in SQL 7

Posted on 2000-02-27
10
728 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
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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 300 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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SSRS 2013 - Creating a summarized report 19 35
sql server service accounts 4 26
SQL Server Connection String through a VPN 8 26
sql server query 6 9
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

809 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