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

Output

  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...

Answers


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.


Need Your Help

Running ssh-keygen without human interaction?

linux ssh debian ssh-keys

Would it be possible to run ssh-keygen without human interaction?

Releasing NSData causes exception

iphone objective-c cocoa cocoa-touch memory

Can someone please explain why the following code causes my app to bomb?