1

I am trying to convert a postgresql query to ms sql query. This is what I have in postgresql round(cast((Current_Date - emp.hiredate) As Decimal)/365,1)

now I thought it would be a simple conversion and I tried to change it for MS SQL to

round(cast((GetDate() - emp.hiredate) as decimal) / 365, 1)

but in MS SQL Server I get this error

The data types date time and date are incompatible in the subtract operator

1

So, you are looking for the difference in Years between the two dates, correct? And you want to show one decimal place in the calculation, correct? Then a simple DATEDIFF() will not work for you. Try this instead:

SELECT ROUND(CAST(Datediff(Day, emp.hiredate, GETDATE()) as decimal(9,1))/ 365, 1)

This is also imprecise (because of leap years) but matches what you have now.

EDIT:
You might want to check this logic, as the current ROUND() logic would show a person at 1.0 years when they hit 0.95 years. If you want to wait until they actually have 1.0 years or more to show them at 1.0 years, use this variant: SELECT ROUND(CAST(Datediff(Day, emp.hiredate, GETDATE()) as decimal(9,1))/ 365, 1, 1). This adds a trailing 1 to the current command. That added parameter TRUNCATES at the point specified instead of rounding up, so 0.99' would still show '0.9'

|improve this answer|||||
  • You might want to check this logic, as the current ROUND() logic would show a person at 1.0 years when they hit 0.95 years. If you want to wait until they actually have 1.0 years or more to show them at 1.0 years, use this variant: SELECT ROUND(CAST(Datediff(Day, emp.hiredate, GETDATE()) as decimal(9,1))/ 365, 1, 1). This adds a trailing 1 to the current command. That added parameter TRUNCATES at the point specified instead of rounding up, so 0.99' would still show '0.9'. – Laughing Vergil Jan 9 at 19:48
  • 2
    I think this comment would be better as part of the answer itself! – Vérace Jan 9 at 19:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.