date diff in sql

i have a query which shows datediff if it is received today it shows null value how can i show 0 instead of null value.

SELECT T1.[ItemCode], T1.[ItemName], T0.[WhsCode], T0.[IntrSerial], T0.[CreateDate] as [IntoSystem],DATEDIFF(DAY,Convert(nvarchar,T0.CreateDate,101),Convert(nvarchar,getdate(),101)) as NoOfDays FROM OSRI T0  INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode WHERE T0.[Status] = 0 ORDER BY T0.[WhsCode], T0.[ItemCode]
romeiovasuAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nungaCommented:
Use the ISNULL function


SELECT T1.[ItemCode], T1.[ItemName], T0.[WhsCode], T0.[IntrSerial], T0.[CreateDate] as [IntoSystem], ISNULL(DATEDIFF(DAY,Convert(nvarchar,T0.CreateDate,101),Convert(nvarchar,getdate(),101)), 0) as NoOfDays FROM OSRI T0  INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode WHERE T0.[Status] = 0 ORDER BY T0.[WhsCode], T0.[ItemCode]
romeiovasuAuthor Commented:
i tried that already it sill gives me null value.
dcgrindleCommented:
You can try using CASE, I think it should work for you.

CASE WHEN DATEDIFF(DAY,Convert(nvarchar,T0.CreateDate,101),Convert(nvarchar,getdate(),101))
      IS NULL THEN 0
ELSE DATEDIFF(DAY,Convert(nvarchar,T0.CreateDate,101),Convert(nvarchar,getdate(),101))
END
as NoOfDays
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Ephraim WangoyaCommented:
SELECT T1.[ItemCode],
       T1.[ItemName],
       T0.[WhsCode],
       T0.[IntrSerial],
       T0.[CreateDate] as [IntoSystem],
       COALESCE(DATEDIFF(DAY,Convert(nvarchar,T0.CreateDate,101),Convert(nvarchar,getdate(),101)), 0) as NoOfDays
FROM OSRI T0  
INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode
WHERE T0.[Status] = 0 ORDER BY T0.[WhsCode], T0.[ItemCode]
swargCommented:
SELECT T1.[ItemCode], T1.[ItemName], T0.[WhsCode], T0.[IntrSerial], T0.[CreateDate] as [IntoSystem],ISNULL(DATEDIFF(DAY,Convert(nvarchar,T0.CreateDate,101),Convert(nvarchar,getdate(),101)),0) as NoOfDays FROM OSRI T0  INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode WHERE T0.[Status] = 0 ORDER BY T0.[WhsCode], T0.[ItemCode]
sarabhaiDeveloperCommented:
Please check your parameter createdate is holding values check this by using print
swargCommented:
whether the date you are passing is in this format

Convert(nvarchar,'2010-12-24',101)

in yyyy-mm-dd format
BrandonGalderisiCommented:
The result of a datediff shouldn't ever be NULL because the difference in days between today and today is 0, not null.  I think something maybe going in with the implicit conversions that occur after you explicitly convert the date values to nvarchar.  

Try:

SELECT  T1.[ItemCode]
      , T1.[ItemName]
      , T0.[WhsCode]
      , T0.[IntrSerial]
      , T0.[CreateDate] as [IntoSystem]
      , DATEDIFF(DAY, T0.CreateDate, getdate()) as NoOfDays
FROM    OSRI T0
INNER JOIN OITM T1
ON      T0.ItemCode = T1.ItemCode
WHERE   T0.[Status] = 0
ORDER BY T0.[WhsCode]
      , T0.[ItemCode]

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.