Date Time range

Question

Post reply

Date Time range

Daniel Bowlin

SSC-Dedicated

Points: 34566
More actions
June 21, 2011 at 11:15 pm

#238321

Comments posted to this topic are about the item Date Time range
June 22, 2011 at 1:10 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

paul s-306273 SSChampion Points: 10730 More actions · Answer 1

paul s-306273

SSChampion

Points: 10730

June 22, 2011 at 2:10 am

#1342260

Thanks Daniel - nice question.

khullargirish02 SSChasing Mays Points: 651 More actions · Answer 2

khullargirish02

SSChasing Mays

Points: 651

June 22, 2011 at 2:31 am

#1342266

nice question. thanks

DugyC Hall of Fame Points: 3804 More actions · Answer 3

Good questions, thanks.

Unfortunately, I rushed the answer and got it wrong! Doh!

Not enough due diligence 🙂

_____________________________________________________________________
[font="Comic Sans MS"]"The difficult tasks we do immediately, the impossible takes a little longer"[/font]

calvo SSChampion Points: 12930 More actions · Answer 4

I had to figure out why I wasn't getting the right answer in my head.

I would look at the select statement and think Ben and David was the answer. I had excluded the other two because the times were equal to the begin and end expressions after rounding. Well, This is why I was getting a different answer.

When using BETWEEN-

"BETWEEN returns TRUE if the value of test_expression is greater than or equal to the value of begin_expression and less than or equal to the value of end_expression."

note: begin_expression >= test_expression <= end_expression

When using NOT BETWEEN-

"NOT BETWEEN returns TRUE if the value of test_expression is less than the value of begin_expression or greater than the value of end_expression."

note: begin_expression > test_expression < end_expressions

reference: msdn

Thanks for the question

______________________________________________________________________________________________
Forum posting etiquette.[/url] Get your answers faster.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 5

Nice question, thanks.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Richard Warr SSCertifiable Points: 6966 More actions · Answer 6

Interesting question. I knew about the rounding thing which helped. I did try to run the code but it failed due to me having a UK setup (as the question hinted).

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 7

Good question. If there was a time with 29.999 seconds as the last bit it would have gotten me.

Britt Cluff SSCertifiable Points: 5083 More actions · Answer 8

Good question. I looked at it quickly and would have answered wrong and I hadn't double checked and caught the smalldatetime.

http://brittcluff.blogspot.com/

SanDroid SSChampion Points: 10068 More actions · Answer 9

Nice Question.

I always wondered what they thinking when this was done.

Why would you want to make an existing data type that has been accurate to the second for almost twenty years and change it to round to the minute?

I guess someone wanted to re-write ALOT of code.

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 10

SanDroid (6/22/2011)
Why would you want to make an existing data type that has been accurate to the second for almost twenty years and change it to round to the minute?

Space. If being accurate to the second doesn't matter then why store the extra bits? The last application I worked on had a number of date columns that didn't need seconds stored but still did. Combine that with a number of other wasteful practices and I'm sure that, with a ton of recoding and effort, a lot of space could have been freed up which would also result in a performance gain, both when using and for maintenance.

SanDroid SSChampion Points: 10068 More actions · Answer 11

cfradenburg (6/22/2011)
SanDroid (6/22/2011)
Why would you want to make an existing data type that has been accurate to the second for almost twenty years and change it to round to the minute?
Space. If being accurate to the second doesn't matter then why store the extra bits? The last application I worked on had a number of date columns that didn't need seconds stored but still did. Combine that with a number of other wasteful practices and I'm sure that, with a ton of recoding and effort, a lot of space could have been freed up which would also result in a performance gain, both when using and for maintenance.

That first if is the point. This causes more space to be used not less.

Now to be accurate to the second a larger data type has to be used.

Not good to change an existing Data Type to be less accurate and force everyone to use more space.

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 12

According to BOL datetime uses 8 bytes and smalldatetime uses 4 bytes.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 13

Thanks Daniel

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events