Search code examples
sqlsubtractiongetdate

Subtract table value from today's date - SQL

I am trying to return the number of years someone has been a part of our team based on their join date. However i am getting a invalid minus operation error. The whole getdate() is not my friend so i am sure my syntax is wrong.

Can anyone lend some help?

SELECT 
  Profile.ID as 'ID', 
  dateadd(year, -profile.JOIN_DATE, getdate()) as 'Years with Org'
FROM  Profile
Solution

  • MySQL Solution

    Use the DATE_DIFF function

    The DATEDIFF() function returns the time between two dates.

    DATEDIFF(date1,date2)

    http://www.w3schools.com/sql/func_datediff_mysql.asp

    This method only takes the number of days difference. You need to convert to years by dividing by 365. To return an integer, use the FLOOR method.

    In your case, it would look like this

    SELECT 
  Profile.ID as 'ID', 
  (FLOOR(DATEDIFF(profile.JOIN_DATE, getdate()) / 365)) * -1 as 'Years with Org'
FROM  Profile

    Here's an example fiddle I created

    http://sqlfiddle.com/#!9/8dbb6/2/0

    MsSQL / SQL Server solution

    The DATEDIFF() function returns the time between two dates.

    Syntax: DATEDIFF(datepart,startdate,enddate)

    It's important to note here, that unlike it's MySql counterpart, the SQL Server version takes in three parameters. For your example, the code looks as follows

    SELECT Profile.ID as 'ID', 
       DATEDIFF(YEAR,Profile.JoinDate,GETDATE()) as difference
FROM Profile

    http://www.w3schools.com/sql/func_datediff.asp