Beräkna ålder i SQL

Att beräkna någons ålder är lite klurigt i SQL. Använd följande exempel (MS SQL Server, MySQL) så går det enklare.

--

-- Create the table

--

-- DROP TABLE Pet;

CREATE TABLE Pet

(

name CHAR(5),

birth DATETIME

);

INSERT INTO Pet(name, birth) VALUES ('Pilot', '1984-04-27');

INSERT INTO Pet(name, birth) VALUES ('Kaiba', '2001-03-09');

INSERT INTO Pet(name, birth) VALUES ('Ludde', '2004-10-15');

SELECT * FROM Pet;

-- 

-- SQL to calculate age in Microsoft SQL Server

--

SELECT 

name, birth,

( DATEDIFF(YY, birth, GETDATE()) - 

CASE WHEN

(

(MONTH(birth)*100 + DAY(birth)) >

(MONTH(GETDATE())*100 + DAY(GETDATE()))

) THEN 1

ELSE 0

END

) AS age

FROM Pet;

--

-- SQL to calculate age in MySQL

-- also available in the manual

-- http://dev.mysql.com/doc/refman/5.0/en/date-calculations.html

--

SELECT

  name, birth,

  (YEAR(CURDATE())-YEAR(birth))

    - (RIGHT(CURDATE(),5)

  AS age

FROM Pet;