Alternate Way to Write query: ISNULL function causing issues

Question

Alternate Way to Write query: ISNULL function causing issues

Viewing 7 posts - 16 through 21 (of 21 total)

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

simon.barnes Old Hand Points: 370 More actions · Answer 1

RAThor (6/9/2016)
You are correct about the short ISO format (yyyyMMdd). But unfortunately the ODBC canonical format 'yyyy-MM-dd' can still be ambiguous.

Ah yes, DATEFORMAT. Though I don't think any languages use YDM and the newer data types don't support it. I don't get why people use the hyphens at all to be honest, I have a disproportionately low tolerance for needless typing 🙂

Edit: Oh, I see DMY does it too. That's probably in one of the languages actually.

simon.barnes Old Hand Points: 370 More actions · Answer 2

drew.allen (6/9/2016)
This suffers the same exact problem that I mentioned here http://www.sqlservercentral.com/Forums/FindPost1792490.aspx

Ohhh, I do apologise. I didn't click that he was assigning any semantic significance to the 1900 date; I just assumed it was a "data-that-should-never-happen" placeholder for the ISNULLs. My bad.

In that case @OP yeah, try to get some plans and table/index info up (you can anonymise plans with Plan Explorer for posting to the web). Having reread the question is there actually a performance issue occurring or do you just suspect that there may be?

drew.allen SSC Guru Points: 76944 More actions · Answer 3

Jeff Moden (6/6/2016)
And notice that Drew did NOT suggest using 9999-12-31. That's because the classic method of isolating date ranges requires a "AND SomeDTColumn < @EndDate +1" and all similar variations. I personally use a literal of "9999", which translates to 9999-01-01, which also leaves room for adding a month to the end date.

Actually, I'm starting to come to the conclusion that for some purposes 2079-06-06 (and a few others) might be included in those options. If you run the following, you might have a clue why.

SELECT CAST(DATEDIFF(DAY, 0, '20790606') AS VARBINARY(5))

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 4

drew.allen (6/9/2016)
Jeff Moden (6/6/2016)
And notice that Drew did NOT suggest using 9999-12-31. That's because the classic method of isolating date ranges requires a "AND SomeDTColumn < @EndDate +1" and all similar variations. I personally use a literal of "9999", which translates to 9999-01-01, which also leaves room for adding a month to the end date.
Actually, I'm starting to come to the conclusion that for some purposes 2079-06-06 (and a few others) might be included in those options. If you run the following, you might have a clue why.
SELECT CAST(DATEDIFF(DAY, 0, '20790606') AS VARBINARY(5))
Drew

Heh... the next "Y2K" for 2 byte dates (SMALLDATETIME... 2 bytes for date, 2 bytes for time). The next day is date serial number 65,536. BOOM!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

drew.allen SSC Guru Points: 76944 More actions · Answer 5

Jeff Moden (6/9/2016)
drew.allen (6/9/2016)
Jeff Moden (6/6/2016)
And notice that Drew did NOT suggest using 9999-12-31. That's because the classic method of isolating date ranges requires a "AND SomeDTColumn < @EndDate +1" and all similar variations. I personally use a literal of "9999", which translates to 9999-01-01, which also leaves room for adding a month to the end date.
Actually, I'm starting to come to the conclusion that for some purposes 2079-06-06 (and a few others) might be included in those options. If you run the following, you might have a clue why.
SELECT CAST(DATEDIFF(DAY, 0, '20790606') AS VARBINARY(5))
Drew
Heh... the next "Y2K" for 2 byte dates (SMALLDATETIME... 2 bytes for date, 2 bytes for time). The next day is date serial number 65,536. BOOM!

Ah, I never use SMALLDATETIME, so that never occurred to me. I have a different reason for choosing that date. Once I get it figured out, I might write an article. It's way beyond the scope of a forum post.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 6

drew.allen (6/10/2016)
Jeff Moden (6/9/2016)
drew.allen (6/9/2016)
Jeff Moden (6/6/2016)
And notice that Drew did NOT suggest using 9999-12-31. That's because the classic method of isolating date ranges requires a "AND SomeDTColumn < @EndDate +1" and all similar variations. I personally use a literal of "9999", which translates to 9999-01-01, which also leaves room for adding a month to the end date.
Actually, I'm starting to come to the conclusion that for some purposes 2079-06-06 (and a few others) might be included in those options. If you run the following, you might have a clue why.
SELECT CAST(DATEDIFF(DAY, 0, '20790606') AS VARBINARY(5))
Drew
Heh... the next "Y2K" for 2 byte dates (SMALLDATETIME... 2 bytes for date, 2 bytes for time). The next day is date serial number 65,536. BOOM!
Ah, I never use SMALLDATETIME, so that never occurred to me. I have a different reason for choosing that date. Once I get it figured out, I might write an article. It's way beyond the scope of a forum post.
Drew

Sounds like fun, Drew. That would be an interesting article, indeed.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110206 More actions · Answer 7

colin.frame (6/8/2016)
Sergiy (6/6/2016)
and NOT (pm.pdenddate=pt.pdenddate
OR (pm.pdenddate is NULL and pt.pdenddate is NULL)
)
This logic doesn't quite match what's given - the pm.pdenddate=pt.pdenddate part excludes all lines where one side only of the = operator is NULL.

I must apologise.

We had an exchange of private messages, and yes, the logic as I posted it was faulty.

It only works if used in NOT EXISTS kind of check - this is what I tend to use to use in all my queries.

With JOIN it's not that simple, of course.

_____________
Code for TallyGenerator