InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 03:59:56 GMT20http://www.sqlservercentral.com/Forums/Topic9381-104-1.aspx&lt;quote&gt;Datetime and smalldatetime are like the floating-point data types, float and real, in that they’re approximate numerics. That means the value retrieved from SQL Server may be different from the value that was originally stored. &lt;/quote&gt; Reading the datetime as float will mostly result the correct values, but not always. DECLARE @d datetimeSELECT @d = '1900-01-24 13:12:10.990'SELECT @d as_datetime, convert(datetime, cast(@d as float)) as_float Results are: 1900-01-24 13:12:10.990 as_datetime, 1900-01-24 13:12:10.987 as_float. You can see that casting datetime to float and back result a value different from the initial one. The reason is simple: Datetime and smalldatetime are not really floating point datatype. They are actually 2 4-bytes integers (or 2 2-bytes integers in case of smalldatetime). One represents the number of days past since 1900-01-01. The other, contains the number of clock ticks past since midnight (1 second = 300 clock ticks). To calculate the integer containing the datepart, we could use the datediff function: -- Calculate numner of days since 1900-01-01:SELECT datediff(dd, 0, @d) as number_of_daysThis will result 23 days. Calculating the time part is trickier. We have to first calculate the number of second past since midnight, multiply is by 300 clock ticks and add the fraction of the milliseconds: -- Calculate the number of clock ticks:SELECT ( datepart(hour,@d) * 60 * 60 + datepart(minute,@d) * 60 + datepart(second,@d) ) * 300 + cast(datepart(ms,@d)/1000.*300 as int) as number_of_clock_ticks This results 14259297 clock ticks. Now let's try to read the 2 integers that are composing the datetime. I will cast the variable as binary(8), then convert to int the first 4 byes that represent the date part, and the last 4 bytes that represent the time part: SELECT cast(substring(convert(binary(8), @d),1, 4) as int) date_part_in_int, cast(substring(convert(binary(8), @d),5, 8) as int) time_part_in_int The results are 23 days and 14259297 clock ticks which are the exact same values calculated above.Note that for this example I used .990 as milliseconds to avoid rounding problems. Milliseconds are always rounded to .000 .003 or .007 in following way: .990 .991 --&gt; .990.992 .993 ..994 --&gt; .993.995 .996 .997 .998 --&gt; .997.999 --&gt; .000 DECLARE @d1 datetime, @d2 datetimeSELECT @d1 = '2002-02-02 23:59:59:998', @d2 = '2002-02-02 23:59:59:999'SELECT @d1, @d2 Results: 2002-02-02 00:00:00.000 2002-02-03 00:00:00.000This assignment might not have much sense. You are not likely to find yourself asigning 998 milliseconds to a datetime variable. But if you do, you can see that rounding occures before asigning the value. From this point on the value retrieved will never be different from the one asigned. To see the difference between the conversion to float and int, try to run this script: SET NOCOUNT ONDECLARE @x datetimeDECLARE @t table ( base_date datetime , as_float datetime , as_binary binary(8) )DECLARE @i intSELECT @x = '1900-01-01 13:12:10.999' SELECT @i = 1SELECT @x = dateadd(millisecond,@i,@x) WHILE @i &lt;= 10BEGIN SELECT @x = DATEADD(millisecond,@i,@x) INSERT INTO @t SELECT @x , convert(datetime,cast(@x as float)) , convert(binary(8),@x) SELECT @i = @i + 1END SELECT base_date , as_float , case when as_float &lt;&gt; base_date then 'WRONG' else 'OK' end ok_f , convert(datetime, as_binary) binary_to_date , case when convert(datetime, as_binary) &lt;&gt; base_date then 'WRONG' else 'OK' end ok_bFROM @t There is not doubt that there are limitations to the datetime variable. I would have liked, for example, to be able to store values before 1753. I also cannot understand why millisecond are rounded and not stored correctly. However, as long as you are reading datetimes correctly, the system always returns the same value stored. If you choose to refer to datetime at lower level, and avoid having the datetime algorytm run on all values (and I do it in some cases since performance can improve dramatically!), you must read it as 2 integers acording to the shown above. This is what it is - 2 integers. Not a float. :-) A few words about searching. SQL Server does does not have seperate datatypes for date and time. And as long as this is the case (and from what I have heard in the next version there will be), it would be wrong to refer to datetime as date only. When searching a date range, we have to make sure our time portion is set correctly. If time is irelevant, start_date time portion should be set to '00:00:00: and the end_date to '23:59:59.997'. This can be done in serveral ways - convert(varchar(10), start_date, 120) (ISO is always the best way to handle dates) - cast( substring(cast(start_date as binary(8), 1, 4) as datetime) not that intuitive, but works much better than the above. - dateadd(dd,datediff(dd,0,start_date ),0) This is probably the most elegant and efficient way. To set the end_date, you should first set the time part to zero, then add a day and substruct 2 milliseconds. - dateadd(millisecond, -2, dateadd(dy,1,end_date_00)) I hope this shades some light on the datetime variable. Bambola. <font face='Verdana'></font id='Verdana'>Edited by - bambola on 03/07/2003 04:30:07 AMFri, 07 Mar 2003 04:27:00 GMTbambolahttp://www.sqlservercentral.com/Forums/Topic9381-104-1.aspxNice article. Just put your suggestions on how to calc time to good use for a report I had to write. Thanks<img src=icon_smile.gif border=0 align=middle> Fri, 24 Jan 2003 15:20:00 GMTJohnMarkshttp://www.sqlservercentral.com/Forums/Topic9381-104-1.aspxThanks for your comments.You're right about the omission. However, as with most search expressions, there are many working solutions. I didn't mentionWHERE CONVERT(varchar, dateEntered, 103) LIKE '09/07/2002%'nor did I mentionWHERE CAST(CAST(dateEntered AS char(11)) AS datetime) = '2002-09-07'(I do show a few of these in Chapter 8 of my book, however.)As you mentioned, the LIKE version will perform badly, perhaps even worse than the other version that just use functions. I figured there's no reason to include multiple poorly-performing solutions.Regards,Wed, 22 Jan 2003 10:18:00 GMTbryansyversonhttp://www.sqlservercentral.com/Forums/Topic9381-104-1.aspxMost excellent article Brian, one of those reference ones that will go into the very useful solutions folder. Thank you.Jon ReadeTue, 21 Jan 2003 04:45:00 GMTJonrhttp://www.sqlservercentral.com/Forums/Topic9381-104-1.aspxHiOutstanding article that covers off some classic issues, thanks for spending the time writing it.CheersChris Kwww.chriskempster.com Tue, 21 Jan 2003 03:11:00 GMTckempstehttp://www.sqlservercentral.com/Forums/Topic9381-104-1.aspxhi abhi_develops,I mean something like this, it obviously is not an efficient way. Just another methodex:SELECT CONVERT(varchar, dateEntered, 103)FROM dbo.tblDirectoryWHERE CONVERT(varchar, dateEntered, 103) LIKE '09/07/2002%' Tue, 21 Jan 2003 00:03:00 GMTpmanisekaranhttp://www.sqlservercentral.com/Forums/Topic9381-104-1.aspxWhy was "dtColumn like dt +'%' " not mentioned as one of the ways seraching for dates in s datetime column with inconsistent storage like storing date and time. Mon, 20 Jan 2003 21:16:00 GMTpmanisekaranhttp://www.sqlservercentral.com/Forums/Topic9381-104-1.aspxComments posted to this topic are about the content posted at <A HREF=http://www.sqlservercentral.com/columnists/bsyverson/sqldatetime.asp>http://www.sqlservercentral.com/columnists/bsyverson/sqldatetime.asp</A>Sun, 19 Jan 2003 00:00:00 GMTbryansyverson