|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Difference in milisecondsHi there,
Can anybody explain to me why select convert(datetime, '2005-07-16 22:05:27.332') returns: 2005-07-16 22:05:27.333 Whichs has a difference at 1 miliseconds. When I try to store time in a datetime field with miliseconds they seldom gets saved with the correct miliseconds. Any help? Jens G That is because a datetime is only accurate to 3.33 milliseconds and will
round accordingly. If you look in BooksOnLine under Datetime it will explain that in more detail. -- Show quoteAndrew J. Kelly SQL MVP "Jens Gotthardsen" <j***@scanrate.dk> wrote in message news:520jd1d41d4e9ah5cclmlaig7g0k03ejsk@4ax.com... > Hi there, > > Can anybody explain to me why > > select convert(datetime, '2005-07-16 22:05:27.332') > > returns: > > 2005-07-16 22:05:27.333 > > Whichs has a difference at 1 miliseconds. > > When I try to store time in a datetime field with miliseconds they > seldom gets saved with the correct miliseconds. > > Any help? > > Jens G Just so it's in this thread, note that Books Online explains incorrectly
how datetime data is stored. The topic "datetime and smalldatetime" says <quote> Remarks Values with the datetime data type are stored internally by Microsoft SQL Server as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight. </quote> It should say "... The other 4 bytes store the time of the day represented as the number of 1/300 second units after midnight" SK Andrew J. Kelly wrote: Show quote >That is because a datetime is only accurate to 3.33 milliseconds and will >round accordingly. If you look in BooksOnLine under Datetime it will >explain that in more detail. > > > Right Steve. I was referring him to this part at the very top of the page:
datetime Date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007 seconds, as shown in the table. -- Show quoteAndrew J. Kelly SQL MVP "Steve Kass" <sk***@drew.edu> wrote in message news:e7d76kliFHA.1248@TK2MSFTNGP12.phx.gbl... > Just so it's in this thread, note that Books Online explains incorrectly > how datetime data is stored. The topic "datetime and smalldatetime" says > > <quote> > Remarks > Values with the datetime data type are stored internally by Microsoft SQL > Server as two 4-byte integers. The first 4 bytes store the number of days > before or after the base date, January 1, 1900. The base date is the > system reference date. Values for datetime earlier than January 1, 1753, > are not permitted. The other 4 bytes store the time of day represented as > the number of milliseconds after midnight. > </quote> > > It should say "... The other 4 bytes store the time of the day represented > as the > number of 1/300 second units after midnight" > > SK > > > Andrew J. Kelly wrote: > >>That is because a datetime is only accurate to 3.33 milliseconds and will >>round accordingly. If you look in BooksOnLine under Datetime it will >>explain that in more detail. >> >> Jens,
The precision of [datetime] in SQL Server is 1/300 of a second, or about 3.3 milliseconds. While an exact number of milliseconds is displayed, only 300 different sub-second time portions can be stored, and all datetime values are rounded to the nearest representable datetime value, and then displayed or converted to strings showing a number of milliseconds ending in 0, 3, or 7. Steve Kass Drew University Jens Gotthardsen wrote: Show quote >Hi there, > >Can anybody explain to me why > >select convert(datetime, '2005-07-16 22:05:27.332') > >returns: > >2005-07-16 22:05:27.333 > >Whichs has a difference at 1 miliseconds. > >When I try to store time in a datetime field with miliseconds they >seldom gets saved with the correct miliseconds. > >Any help? > >Jens G > > Hej Steve and Andrew,
Thanks for your answers. I never did see that. Is there a work around so it is posible to work acurate with time in miliseconds? Jens G On Sat, 16 Jul 2005 18:35:46 -0400, Steve Kass <sk***@drew.edu> wrote: Show quote >Jens, > > The precision of [datetime] in SQL Server is 1/300 of a second, or about >3.3 milliseconds. While an exact number of milliseconds is displayed, only >300 different sub-second time portions can be stored, and all datetime >values are rounded to the nearest representable datetime value, and >then displayed or converted to strings showing a number of milliseconds >ending in 0, 3, or 7. > >Steve Kass >Drew University > >Jens Gotthardsen wrote: > >>Hi there, >> >>Can anybody explain to me why >> >>select convert(datetime, '2005-07-16 22:05:27.332') >> >>returns: >> >>2005-07-16 22:05:27.333 >> >>Whichs has a difference at 1 miliseconds. >> >>When I try to store time in a datetime field with miliseconds they >>seldom gets saved with the correct miliseconds. >> >>Any help? >> >>Jens G >> >> Other than creating your own datatype I don't know of one.
-- Show quoteAndrew J. Kelly SQL MVP "Jens Gotthardsen" <j***@scanrate.dk> wrote in message news:l9ujd1df1afg90h2c4a1s62hml5vjfbk5a@4ax.com... > Hej Steve and Andrew, > > Thanks for your answers. I never did see that. > > Is there a work around so it is posible to work acurate with time in > miliseconds? > > Jens G > > On Sat, 16 Jul 2005 18:35:46 -0400, Steve Kass <sk***@drew.edu> wrote: > >>Jens, >> >> The precision of [datetime] in SQL Server is 1/300 of a second, or about >>3.3 milliseconds. While an exact number of milliseconds is displayed, >>only >>300 different sub-second time portions can be stored, and all datetime >>values are rounded to the nearest representable datetime value, and >>then displayed or converted to strings showing a number of milliseconds >>ending in 0, 3, or 7. >> >>Steve Kass >>Drew University >> >>Jens Gotthardsen wrote: >> >>>Hi there, >>> >>>Can anybody explain to me why >>> >>>select convert(datetime, '2005-07-16 22:05:27.332') >>> >>>returns: >>> >>>2005-07-16 22:05:27.333 >>> >>>Whichs has a difference at 1 miliseconds. >>> >>>When I try to store time in a datetime field with miliseconds they >>>seldom gets saved with the correct miliseconds. >>> >>>Any help? >>> >>>Jens G >>> >>> > Jens Gotthardsen <j***@scanrate.dk> wrote in
news:l9ujd1df1afg90h2c4a1s62hml5vjfbk5a@4ax.com: If the source of your time data is external, i.e. you're getting time data > Hej Steve and Andrew, > > Thanks for your answers. I never did see that. > > Is there a work around so it is posible to work acurate with time in > miliseconds? > accurate to milliseconds from OUTSIDE your program and need to insert it into SQL server, then I'd say your best bet would be to store it accurate to seconds in a datetime field and store the milliseconds in an int. But there's no way to get time data to milliseconds FROM Sql Server. CURRENT_TIMESTAMP will never return something and 150 ms; DATEADD(ms, 150, '2005-07-17 12:22:00') will never return '2005-07-17 12:22:00.150'. On 17 Jul 2005 16:23:12 GMT, Ross Presser wrote:
Show quote > Jens Gotthardsen <j***@scanrate.dk> wrote in Let me rephrase that. 150 ms is perfectly possible; 152 ms is not. Steve> news:l9ujd1df1afg90h2c4a1s62hml5vjfbk5a@4ax.com: > >> Hej Steve and Andrew, >> >> Thanks for your answers. I never did see that. >> >> Is there a work around so it is posible to work acurate with time in >> miliseconds? >> > > If the source of your time data is external, i.e. you're getting time data > accurate to milliseconds from OUTSIDE your program and need to insert it > into SQL server, then I'd say your best bet would be to store it accurate > to seconds in a datetime field and store the milliseconds in an int. > > But there's no way to get time data to milliseconds FROM Sql Server. > CURRENT_TIMESTAMP will never return something and 150 ms; DATEADD(ms, 150, > '2005-07-17 12:22:00') will never return '2005-07-17 12:22:00.150'. and Andrew were correct, I was wrong. Thanks for all the answers.
Just to round things up, I desided to use a float field instead and store it in seconds.miliseconds. Then I convert it back and forth in my program. Then it was still posible to compare and do math on my time directly in sql. Jens G On Sat, 16 Jul 2005 23:49:37 +0200, Jens Gotthardsen <j***@scanrate.dk> wrote: Show quote >Hi there, > >Can anybody explain to me why > >select convert(datetime, '2005-07-16 22:05:27.332') > >returns: > >2005-07-16 22:05:27.333 > >Whichs has a difference at 1 miliseconds. > >When I try to store time in a datetime field with miliseconds they >seldom gets saved with the correct miliseconds. > >Any help? > >Jens G Don't use a float. Floats are approximate values and much larger than you
need. Use a decimal with the proper scale and precision. You will save bytes and be assured of a more accurate process. -- Show quoteAndrew J. Kelly SQL MVP "Jens Gotthardsen" <j***@scanrate.dk> wrote in message news:qpdpd1hn1pb4d1slmk05797ahfuf701hd1@4ax.com... > Thanks for all the answers. > > Just to round things up, I desided to use a float field instead and > store it in seconds.miliseconds. Then I convert it back and forth in > my program. Then it was still posible to compare and do math on my > time directly in sql. > > Jens G > > On Sat, 16 Jul 2005 23:49:37 +0200, Jens Gotthardsen > <j***@scanrate.dk> wrote: > >>Hi there, >> >>Can anybody explain to me why >> >>select convert(datetime, '2005-07-16 22:05:27.332') >> >>returns: >> >>2005-07-16 22:05:27.333 >> >>Whichs has a difference at 1 miliseconds. >> >>When I try to store time in a datetime field with miliseconds they >>seldom gets saved with the correct miliseconds. >> >>Any help? >> >>Jens G >  |
|||||||||||||||||||||||
Other interesting topics