Subtract table value from today's date - SQL

Question

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

You probably want `DATE_SUB` also is this MSSQL or MYSQL? I don't recall DATEADD being a function in MYSQL — CollinD, Mar 16 '16 at 20:57

score 0 · Answer 1 · answered Mar 16 '16 at 21:02

0

Looks like you're using T-SQL? If so, you should use DATEDIFF:

DATEDIFF(year, profile.JOIN_DATE, getdate())

answered Mar 16 '16 at 21:02

Joseph M. Shunia

310
2
10

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

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

Is there a way to make that calculation down to the day ? For example of someone joined 6/1/2010 and today is 5/1/2016 it should return a 5. After or on 6/1/2016, it should return 6. — DerFox, Apr 15 '16 at 20:35

score 0 · Answer 3 · edited May 23 '17 at 12:07

0

Using the method from this answer, you can get your result with the following query :

SELECT 
  Profile.ID as 'ID', 
  YEAR(getdate()) - YEAR(profile.JOIN_DATE) - (DATE_FORMAT(getdate(), '%m%d') < DATE_FORMAT(profile.JOIN_DATE, '%m%d')) as 'Years with Org'
FROM  Profile

edited May 23 '17 at 12:07

Community

1
1

answered Mar 16 '16 at 21:04

Sam Bauwens

1,317
11
18

score 0 · Answer 4 · answered Mar 16 '16 at 21:05

0

If this is MS SQL:

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

answered Mar 16 '16 at 21:05

Cory Buczkowski

56
1
4

Subtract table value from today's date - SQL

4 Answers4