CODEWITHSUNDEEP
sql-server-2008-r2isnulldatepart
Jason Shaw
Jason Shaw

Reputation: 29

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.

Upvotes: 1

Views: 2152

Answers (1)

Spaceman Spiff
Spaceman Spiff

Reputation: 934

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

Upvotes: 1

Related Questions