DateDiff years into decimals

sql-server-2008 tsql datediff
chrissy p picture chrissy p · May 2, 2012 · Viewed 17.7k times · Source

I have two dates in which i would like to find the number of years between them, however i would need to show the value to two decimal places. I have tried the following but i always get a value returned of 0 as all of my dates do not cover a whole year:

DATEDIFF(yy, @EndDateTime, i.mat_exp_dte)

I have then tried finding the number of days between the two and then dividing it by 365, but this still returns 0:

DATEDIFF(dd, @EndDateTime, i.mat_exp_dte)/365

Am confused now as to how to calculate this. Would i need to convert the DataDiff into a different data type?

Answer

Mikael Eriksson picture Mikael Eriksson · May 2, 2012

Try this instead.

DATEDIFF(dd, @EndDateTime, i.mat_exp_dte)/365.0

Dividing int with an int returns int. Divide with a decimal and you will get a decimal as a result.

Related questions

How to convert number of minutes to hh:mm format in TSQL?

I have a select query that has DURATION column to calculate number of Minutes . I want to convert those minutes to hh:mm format. Duration has values like 60, 120,150 For example: 60 becomes 01:00 hours 120 becomes 02:00 hours 150 becomes 02:30 hours Also, this is how …

Read More
How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More
How to check if a column exists in a SQL Server table?

I need to add a specific column if it does not exist. I have something like the following, but it always returns false: IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'myTableName' AND COLUMN_NAME = 'myColumnName') How can …

Read More