How to use months_between function in SQL Server 2008?
I have a table EMP, I need the months difference between their HIREDATE to present date.
I used MONTHS_BETWEEN function and I wrote this SQL statement:
SELECT HIREDATE, SYSDATE, MONTHS_BETWEEN (SYSDATE, HIREDATE) AS DIFF FROM EMP
HIREDATE SYSDATE DIFF --------- --------- ---------- 17-DEC-80 31-JAN-14 397.452866 20-FEB-81 31-JAN-14 395.356092 22-FEB-81 31-JAN-14 395.291576 02-APR-81 31-JAN-14 393.936737
But I need the same output in SQL Server 2008, I tried using DATEDIFF function like this:
Declare @Date1 datetime Declare @Date2 datetime set @Date1 = (SELECT GETDATE()); set @Date2 = (SELECT HIREDATE FROM SCOTT.EMP ) SELECT DATEDIFF(MONTH, @Date1, @Date2) AS DifferenceOfDay
But I got error message:
Msg 512, Level 16, State 1, Line 6 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Please help me writing a query in SQL Server 2008 as to get same output...
To Fix your original query, try the following.
SELECT HIREDATE , SYSDATE , DATEDIFF(MONTH, [HIREDATE], GETDATE()) AS DifferenceOfDay FROM SCOTT.EMP
Your original error is because the following variable assignment is going to return more than 1 value.
set @Date2 = (SELECT HIREDATE FROM SCOTT.EMP )
If you want to use a similar query, please use this.
SELECT DATEDIFF(M, HIREDATE, GETDATE()) FROM SCOTT.EMP
Here's a link to the DATEDIFF documentation.