Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««23456»»»

T-SQL Expand / Collapse
Author
Message
Posted Tuesday, February 17, 2009 7:48 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, July 06, 2010 10:17 AM
Points: 511, Visits: 181
So the QotD answer was wrong but I learned something anyway -- I had forgotten about the rounding issue.

It reminded me of an Advanced Electromagnetic Fields class I took in college -- even the professor's answer key only got an 86% on the final exam.


- Randall Newcomb
Post #658523
Posted Tuesday, February 17, 2009 8:04 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, March 04, 2014 7:41 PM
Points: 634, Visits: 399
I agree with Hugo K.'s point#s 3 and 6 in his p.1 reply, and because of the latter point (QotD used a colon between seconds and ms. rather than a dot), I figured the answer was "error." Oh well, that's where I learned something new :).
Post #658542
Posted Tuesday, February 17, 2009 8:31 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:57 AM
Points: 2,413, Visits: 3,452
Points schmointz... who cares?

The takeaway is that SQL Server rounds time and don't use BETWEEN with a date range. But, this seems such a strange way to do this. What is the rationale? Can anyone explain that? Do other databases handle time the same way? It seems so counter-intuitive.

Post #658568
Posted Tuesday, February 17, 2009 9:11 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:12 PM
Points: 22,518, Visits: 30,269
skjoldtc (2/17/2009)
Points schmointz... who cares?

The takeaway is that SQL Server rounds time and don't use BETWEEN with a date range. But, this seems such a strange way to do this. What is the rationale? Can anyone explain that? Do other databases handle time the same way? It seems so counter-intuitive.



I think it actually comes down to how the data is stored in binary in the system, but I may be wrong.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #658601
Posted Tuesday, February 17, 2009 10:22 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 7:14 AM
Points: 1,344, Visits: 1,983
I got the same wrong (but right) answer as everyone else (Option 4 only), but learned something while testing the result.
select '2009-02-01 00:00:00:000' union
select '2009-01-31 23:59:59:999'

Produces 2 rows.
select convert(datetime,'2009-02-01 00:00:00:000') union
select convert(datetime,'2009-01-31 23:59:59:999')

Only 1 row inserted.

Obviously, the UNION removal of duplicates is done as a character type in the first case, then the result is converted and rounded to produce 2 identical values, while in the second case, the conversion rounds both values to be the same before removing duplicates resulting in only 1 value!


Derek
Post #658657
Posted Tuesday, February 17, 2009 11:30 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 14, 2010 4:03 PM
Points: 164, Visits: 143
I think this might have been better asked as 'what is the correct query to return the results we want?', instead of asking what return values would be possible (which I'd argue is bad form without first specifying that values are actually in the db in the first place).

Another potential way to get the same point across might have been to present us with several values in datetime format and ask which one will never appear in a db using the older (pre sql2008) datetime datatype..

Also illustrative is to use a bit of script to play with string values and see how they convert to datetime

DECLARE @dt VARCHAR(22)
SET @dt = '20090131 23:59:59.999'
SELECT CAST(@dt AS DATETIME)


that results in 2009-02-01 00:00:00.000 if you run it.


(edited to read a bit more clearly)
---
not a DBA just a QA guy who enjoys learning more about SQL
Post #658699
Posted Tuesday, February 17, 2009 11:31 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 21, 2014 5:33 PM
Points: 160, Visits: 262
skjoldtc (2/17/2009)

The takeaway is that SQL Server rounds time and don't use BETWEEN with a date range. But, this seems such a strange way to do this. What is the rationale? Can anyone explain that? Do other databases handle time the same way? It seems so counter-intuitive.


My guess is that if you truncated the date, you would not exercise any index on [InsertedOn]. However, IMHBIO (in my humble but inexperienced opinion), I would question the wisdom of indexing a datetime. I stay away from indexing any inexact fields.

Kevin


--
Please upgrade to .sig 2.0
Post #658701
Posted Tuesday, February 17, 2009 12:38 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:11 PM
Points: 32,810, Visits: 14,959
OK, my head hurts.

I've awarded back points since there is obviously some issue here. I missed the correct answer with 1/30, so I've fixed that.

As far as the other items, I've changed the colon to a period for the ms, but I get a conversion error when adding the "T" in the date format. I've also changed the answers to show one correct answer.

apologies for the delays.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #658750
Posted Tuesday, February 17, 2009 4:33 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:59 PM
Points: 5,797, Visits: 8,017
skjoldtc (2/17/2009)
Points schmointz... who cares?

The takeaway is that SQL Server rounds time and don't use BETWEEN with a date range. But, this seems such a strange way to do this. What is the rationale?


Hi skjoldtc,

The rationale for rounding? Or for not using BETWEEN?

For rounding, the rationale is that the internal presentation of datetime values uses two four-byte integer values, one for the number of whole days since Jan. 1st, 1900; and the other for the number of 1/300-second units (represented in textual format as .xx0, .xx3, and .xx7) after midnight. This internal presentation makes it impossible to store a value such as 23:59:59.999, so an attempt to set the time component of a datetime to this value can either cause an error or result in implicit rounding during the text-to-datetime conversion. I like the choice for rounding, since I'd hate to explain to my users that they can only enter datetime values with specific last numbers for the milliseconds. (Well, not that I expect my end users to manually key in a datetime with millisecond precision, but you get the point:)) Once the choice for rounding has been made, the next decision is to round always up, always down, or to the nearest representable value. Always down appears nice when you look at 23:59:59.999 specifically, but doesn't make much sense for any other value.

The rationale for not using BETWEEN follows logically from the above. But that's not the only reason. Even though SQL Server has some limitations in its representation, time is in essence a continuous measure. If you want to divide such a measure in intervals, the only correct way to do this is by using half-open intervals (i.e. with = 'A' and MyString < 'B'

Can anyone explain that?


If you're asking for an explanation on the 1/300 millisecond precision, I must disappoint you. I can only speculate. Until a few minutes ago, I had always believed it had to do with the maximum range of numbers that can be represented in a 4-bute integer. However, I just did the math, and I found that there are less than 85 million milliseconds to a day, but over 2 billion values in a 4-byte integer, so that is one theory down the drain :D

My new theory is that is the heritage of some limitations of the timing hardware back in the early (Sybase) days of SQL Server.

Do other databases handle time the same way? It seems so counter-intuitive.


I have never heard of any other database storing time values in 1/300 second units. But I'm far from an expert on any other database, so who knows? Maybe others are even more weird? (But if I had to bet, I'd put my money on other databases having a more logical implementation... ;))



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #658964
Posted Tuesday, February 17, 2009 4:43 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:59 PM
Points: 5,797, Visits: 8,017
knechod (2/17/2009)
My guess is that if you truncated the date, you would not exercise any index on [InsertedOn]. However, IMHBIO (in my humble but inexperienced opinion), I would question the wisdom of indexing a datetime. I stay away from indexing any inexact fields.


Hi Kevin,

I don't really understand what you mean. Since datetime has a higher precendence than char, the character constants will first be converted to datetime, and then the comparison can be made using the datatype of the column, so an index can be used. How exactly the string is converted to datetime doesn't affect this. Maybe I misunderstand your point?

Anyhow, never indexing a datetime is not a wise decision. Tables that are often queried with a date/time interval in the filter can often profit enormously from a clustered index on that datetime column. The same goes for tables where queries often include an ORDER BY clause for the datetime column. And if a table is often queries with a filter for an exact date/time value, then a nonclustered index can be very beneficial - the large number of possible values in a date/time column (provided it is not constrained to have the time value always equal to midnight, turning the datatype datetime in an effective equivalent of date only) almost guarantees high selectivity, which is an important factor in determining a nonclustered index's effectivity.

Finally, the datetime datatype is not inexact. It is basically a counter of the number of 1/300 second intervals since a fixed starting point (midnight of Jan 1st, 1900), so in many regards it is equivalent to an integer.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #658966
« Prev Topic | Next Topic »

Add to briefcase «««23456»»»

Permissions Expand / Collapse