CODEWITHSUNDEEP
mysqlsql
Itzik984
Itzik984

Reputation: 16764

Subtraction in sql statement

Is this SQL statement legal?

     SELECT  firstName, lastName
     FROM  Presidents 
     WHERE  (endTerm-beginTerm)>4;

I need to find Presidents which had a term longer than 4 years. can i use '-' in this query? if not, what is the good way?

the data types are endTerm and beginTerm

Upvotes: 0

Views: 393

Answers (3)

NotGaeL
NotGaeL

Reputation: 8484

Yes it is, as long as endterm and beginterm column's type accepts substraction.

Upvotes: 1

gbn
gbn

Reputation: 432200

Add 4 years onto beginTerm, then compare

SELECT  firstName, lastName
     FROM  Presidents 
     WHERE  endTerm > DATE_ADD(beginTerm, INTERVAL 4 YEAR)

This is generally (including using other RDBMS) safer than subtracting or calculating date differences because of how boundaries between periods are handled.

Upvotes: 4

Tom van der Woerdt
Tom van der Woerdt

Reputation: 29965

Your query is correct. :-) However, if endTerm and beginTerm are DATETIME values, you may like to compare them with INTERVAL 4 YEARS

Upvotes: 2

Related Questions