CODEWITHSUNDEEP
sqlsql-server
Gordon Copestake
Gordon Copestake

Reputation: 1647

Round negative numbers up to zero

I'm trying to create a SQL query that returns the number of years an employee has been employed over the age of 41 based on their date of birth and start date. But obviously some employees are under 41. For these I would like to return a zero but my current attempt returns negatives.

How can I "round up" the negatives to zero?

select EmployeeNo, EmployeeFName, EmployeeLName,
 DATEDIFF(hour,DateOfBirth,GETDATE())/8766 AS Age, 
 DATEDIFF(hour,StartDate,GETDATE())/8766 AS LengthOfService, 
 DATEDIFF(hour,DateOfBirth,GETDATE())/8766 - 41 AS YearsOver41
from Employees

Upvotes: 0

Views: 1221

Answers (2)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

select EmployeeNo, EmployeeFName, EmployeeLName,
   DATEDIFF(hour,DateOfBirth,GETDATE())/8766 AS Age, 
   DATEDIFF(hour,StartDate,GETDATE())/8766 AS LengthOfService, 

   CASE 
       WHEN DATEDIFF(hour,DateOfBirth,GETDATE())/8766 - 41) < 0 THEN 0
       ELSE DATEDIFF(hour,DateOfBirth,GETDATE())/8766 - 41 
   END AS YearsOver41

from Employees

Upvotes: 1

dan04
dan04

Reputation: 91065

CASE WHEN X < 0 THEN 0 ELSE X END

where X is a numeric column or expression that may be negative.

Upvotes: 2

Related Questions