ScottPletcher (9/1/2015)

---

Sean Lange (9/1/2015)

---

ScottPletcher (9/1/2015)

---

Sean Lange (9/1/2015)

---

Munabhai (9/1/2015)

---

drop table #temp

CREATE TABLE #TEMP

(

date1 DATE,

date2 DATE

)

insert into #TEMP values

('2011-06-18','2011-06-29'),('2010-12-09','2010-12-15'),('2011-03-04','2011-03-16'),('2010-02-09','2010-07-08'),('2010-03-10','2010-03-31'),

('2010-03-31',null),('2014-08-01',null),(null,'2010-07-08'),('2010-07-08',null),(null,'2014-05-23'),

('2011-02-09','2011-02-02'),('2011-06-22','2011-06-14'),(null,null),(null,null)

SELECT date1, date2,

CASE

WHEN (ISNULL(date1, '1900-01-01') = '1900-01-01' AND ISNULL(date2, '1900-01-01') = '1900-01-01') THEN 0

WHEN ISNULL(date2, '1900-01-01') = '1900-01-01' THEN DATEDIFF(DAY, date1, GETDATE())

WHEN ISNULL(date1, '1900-01-01') = '1900-01-01' THEN 0

ELSE

DATEDIFF(DAY, date1, date2)

END AS PaymentDays

FROM #temp WITH (NOLOCK)

In above, in "WHEN (ISNULL(date1, '1900-01-01') = '1900-01-01' AND ISNULL(date2, '1900-01-01') = '1900-01-01') THEN 0" condition instead of 0 I want blank ('') but it saying since we are substracting, it won't accept blank in int column.

In "WHEN ISNULL(date1, '1900-01-01') = '1900-01-01' THEN 0" case also want blank.

Any suggestion.

Why in the world are you using NOLOCK here? There is no possibility that table can be locked by any other process. Are you familiar with that hint and everything it brings to the table? http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/[/url]

Since it's impossible that anyone else needs to use that table, why on earth would you not use NOLOCK to reduce overhead?

Edit: Corrected one typo.

Scott last I remembered in the thread where you and Jeff were discussing this you were not able to provide significant proof that it made much, if any, difference.

You have been around this technology long enough to know that when you see that hint it is very frequently an indication that the person doesn't understand the nuances of that hint. As you well know that hint is not a magic go faster button but you seem to not only condone its usage, you seem to promote using it.

Only because you seem to blindly disparage it no matter what the context. NOLOCK wouldn't exist in SQL if it didn't have an effect. Any lock requires resources to accomplish: that's axiomatic. Thus avoiding any lock saves resources: that's axiomatic as well. Are those resources significant? Impossible to say without a lot of details.

In this specific case, how could NOLOCK ever cause a consistency problem? I guess I just don't see how.