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.