Are the posted questions getting worse?

Question

Are the posted questions getting worse?

Viewing 15 posts - 12,781 through 12,795 (of 66,712 total)

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

Paul White SSC Guru Points: 150467 More actions · Answer 1

jcrawf02 (3/16/2010)
Paul White (3/15/2010)
In fact it is as bad as DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 0).
All right Paul, edumacate me. Why is this bad?

Zero is not a date any more than 365 is 😉

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 2

phew! all caught up! no more outdated replies for today from Jon...

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 3

Paul White (3/16/2010)
jcrawf02 (3/16/2010)
Paul White (3/15/2010)
In fact it is as bad as DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 0).
All right Paul, edumacate me. Why is this bad?
Zero is not a date any more than 365 is 😉

point taken, but the uncertainty is definitely a lot smaller. (the zero date won't change every four years)

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

Paul White SSC Guru Points: 150467 More actions · Answer 4

jcrawf02 (3/16/2010)
point taken, but the uncertainty is definitely a lot smaller. (the zero date won't change every four years)

Neither will 365 days 😛 😀

Ok, seriously, it wasn't the inaccurate number of days that really jarred me - it was the use of the subtraction operator with a datetime and a numeric.

Same goes for the DATEDIFF function: the parameters are datetimes, not numerics. So's the second one to DATEADD, but that is less easy to phrase well, as I have just amply demonstrated.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Luke L SSC-Dedicated Points: 35547 More actions · Answer 5

Michael Valentine Jones pointed out to me once that using 0 instead of 17530101 can cause issues on date calculations where you are dealing with dates prior to 1900-01-01. If I recall correctly week calculations were particularly affected.

While I've never had reason to use dates prior to this it's something to keep in mind if you are doing any kind of historical modeling. Also, I'm thinking with the new Date, datetime2 etc datatypes with much larger ranges that 17530101 really won't quite cut it anymore for those older historical dates.

Thoughts?

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

GSquared SSC Guru Points: 260824 More actions · Answer 6

Paul White (3/16/2010)
jcrawf02 (3/16/2010)
Paul White (3/15/2010)
In fact it is as bad as DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 0).
All right Paul, edumacate me. Why is this bad?
Zero is not a date any more than 365 is 😉

Good point. I've used that one. Easy enough to put an explicit date in there. Same way I do when I need to do seconds or minutes in that same kind of calculation.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Paul White SSC Guru Points: 150467 More actions · Answer 7

Luke L (3/16/2010)
Michael Valentine Jones pointed out to me once that using 0 instead of 17530101 can cause issues on date calculations where you are dealing with dates prior to 1900-01-01. If I recall correctly week calculations were particularly affected.

Yes I think he blogged about it.

Also, I'm thinking with the new Date, datetime2 etc datatypes with much larger ranges that 17530101 really won't quite cut it anymore for those older historical dates.

The only international standards for a string literal representation of a date & time are of the form '1753-01-01T00:00:00' and '1753-01-01T00:00:00+13:00'.

The new date/time types have a different range (from '0001-01-01' to '9999-12-31'). Explicit conversions from an int to DATETIME2, DATE etc. are not allowed in 2008 (though implicit conversions are, at least in the context of DATEDIFF). Interestingly, and presumably for backward-compatibility reasons, 0 converts to '1900-01-01' for both DATETIME, DATETIME2, DATE, and DATETIMEOFFSET. The same arbitrary date is used as a default value in many circumstances.

All the following produce zero as a result (notice the time zones too!):

DECLARE @D1 DATETIME = {d '1900-01-01'};

DECLARE @D2 DATETIME2 = '1900-01-01T00:00:00';

DECLARE @D3 DATE = '1900-01-01T00:00:00+13:00';

DECLARE @D4 DATETIMEOFFSET = '1900-01-01T00:00:00+13:00';

SELECT DATEDIFF(HOUR, 0, @D1);

SELECT DATEDIFF(HOUR, 0, @D2);

SELECT DATEDIFF(HOUR, 0, @D3);

SELECT DATEDIFF(HOUR, 0, @D4);

The full set of specifications and rules are quite daunting, see Using Date and Time Data.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 8

GSquared (3/16/2010)
Good point. I've used that one. Easy enough to put an explicit date in there. Same way I do when I need to do seconds or minutes in that same kind of calculation.

Yep. I think an arbitrary date makes the purpose of the function easier to see as well. Zero just looks like black magic.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

spaghettidba SSC Guru Points: 105732 More actions · Answer 9

All this code on THE THREAD is a disturbance in the force.

p??u?i?osip l??? i

:hehe:

-- Gianluca Sartori

Paul White SSC Guru Points: 150467 More actions · Answer 10

Gianluca Sartori (3/16/2010)
All this code on THE THREAD is a disturbance in the force.

I thought I could get away with eight lines!

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 11

CirquedeSQLeil (3/16/2010)
Roy Ernest (3/16/2010)
I got my $500 yesterday. If I can write an article there, anyone here can write it for sure. 🙂
You wrote an article for the standard? What's the link so we can go read it.

It has not been published yet. Still more people in front of the queue. 🙂

-Roy

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

Roy Ernest (3/16/2010)
CirquedeSQLeil (3/16/2010)
Roy Ernest (3/16/2010)
I got my $500 yesterday. If I can write an article there, anyone here can write it for sure. 🙂
You wrote an article for the standard? What's the link so we can go read it.
It has not been published yet. Still more people in front of the queue. 🙂

And you didn't let us review it first?:crying:

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

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 13

Roy Ernest (3/16/2010)
CirquedeSQLeil (3/16/2010)
Roy Ernest (3/16/2010)
I got my $500 yesterday. If I can write an article there, anyone here can write it for sure. 🙂
You wrote an article for the standard? What's the link so we can go read it.
It has not been published yet. Still more people in front of the queue. 🙂

Only three in the queue, including you. It'll be out real soon. No links yet.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 14

CirquedeSQLeil (3/16/2010)
Roy Ernest (3/16/2010)
CirquedeSQLeil (3/16/2010)
Roy Ernest (3/16/2010)
I got my $500 yesterday. If I can write an article there, anyone here can write it for sure. 🙂
You wrote an article for the standard? What's the link so we can go read it.
It has not been published yet. Still more people in front of the queue. 🙂
And you didn't let us review it first?:crying:

No worries. It went through a crack team of technical editors. They're even meaner than you guys.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Trey Staker SSCarpal Tunnel Points: 4796 More actions · Answer 15

Grant Fritchey (3/16/2010)
CirquedeSQLeil (3/16/2010)
Roy Ernest (3/16/2010)
CirquedeSQLeil (3/16/2010)
Roy Ernest (3/16/2010)
I got my $500 yesterday. If I can write an article there, anyone here can write it for sure. 🙂
You wrote an article for the standard? What's the link so we can go read it.
It has not been published yet. Still more people in front of the queue. 🙂
And you didn't let us review it first?:crying:
No worries. It went through a crack team of technical editors. They're even meaner than you guys.

I don't know about that we can be pretty mean. :w00t:

---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum