Search code examples
sql-server-2008-r2isnulldatepart

How to deal with NULL value while using DATEPART?

I am using SQL 2008 R2 and I have used DATEPART in one of my select lines, this converts a date and time column to a decimal based on the time.

But I have some NULL values coming through that I would like to show as 0. I have used ISNULL on other columns but unable to use it on this one because I don't know how to do it in conjunction with the DATEPART.

Please see below where I have used datepart but also want to change null values to 0.

DATEPART(hour, tblCarerContract.ContractHours) + (DATEPART(minute, tblCarerContract.ContractHours) / 60.0) as ContractHours

Can anybody help with this please.

Solution

  • The correct way to go about this would be to use a COALESCE statement to change the nulls to 0.

    Try something like this

    COALESCE(DATEPART(hour, tblCarerContract.ContractHours), 0) + (COALESCE(DATEPART(minute, tblCarerContract.ContractHours), 0) / 60.0) as ContractHours