Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


T-SQL


T-SQL

Author
Message
randall.c.newcomb
randall.c.newcomb
Mr or Mrs. 500
Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)

Group: General Forum Members
Points: 513 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
Michael Poppers
Michael Poppers
Right there with Babe
Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)

Group: General Forum Members
Points: 761 Visits: 416
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 Smile.
OCTom
OCTom
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2813 Visits: 4152
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.

Unsure
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24177 Visits: 37948
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.

Unsure


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

Cool
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)
StarNamer
StarNamer
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1294 Visits: 1992
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
SQAPro
SQAPro
SSC-Enthusiastic
SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)

Group: General Forum Members
Points: 166 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
knechod
knechod
SSC-Enthusiastic
SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)

Group: General Forum Members
Points: 166 Visits: 268
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
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36077 Visits: 18738
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
My Blog: www.voiceofthedba.com
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8321 Visits: 11554
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 pointSmile) 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 BigGrin

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... Wink)


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8321 Visits: 11554
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search