Getting the oldest record

Question

How can I write a SQL Query to get the oldest male age in number format, not in the dob format?

name    dob         date job   sex language   prof      salary
-------------------------------------------------------------------------
mitesh  1981-01-01  2001-01-01  m   java    architect   3100.00
ankur   1982-02-02  2001-02-02  m   ruby    scientist   3200.00
dhruv   1983-03-03  2001-03-03  m   csharp   designer   3300.00
ruchi   1981-01-01  2002-01-01  f   php      teacher    4000.00

What do you mean by *number format*? Do you mean their age? And what have you tried so far? We aren't a code-writing service. — Siyual, Dec 01 '15 at 16:13
Possible duplicate of [How to calculate age (in years) based on Date of Birth and getDate()](http://stackoverflow.com/questions/1572110/how-to-calculate-age-in-years-based-on-date-of-birth-and-getdate) — roottraveller, Dec 01 '15 at 16:16
i apologize for vague description, this my first question .. i want to calculate age ,, like if you were born in 1987 , the person should be around 29..thank you — Dorjee, Dec 01 '15 at 16:17
@Dorjee Please edit your Question with better wording. Currently it is unclear. That may be the reason for the down-vote. Look for the "edit" link below the Question and its Tags, if using a web browser to access StackOverflow. — Basil Bourque, Dec 01 '15 at 23:31

score 1 · Answer 1 · answered Dec 01 '15 at 16:26

1

You could do something like:

Select top (1) Name, dob, datediff(YY,[dob],getdate()) as Age
from dbo.YourTableName
where sex = 'm'
order by Age Desc

Which would work in SSMS

answered Dec 01 '15 at 16:26

Chris Test

59
4

score 0 · Answer 2 · answered Dec 01 '15 at 16:19

0

This MySQL query selects the oldest male and converts the 'dbo' format to an age

 SELECT MAX(TIMESTAMPDIFF(dob, '1970-02-01', CURDATE())) AS age
 FROM dbo.data
 WHERE sex='m'

answered Dec 01 '15 at 16:19

Alex

21,273
10
61
73

score 0 · Answer 3 · answered Dec 01 '15 at 23:29

`age` function

Some database such as Postgres offer an age function to calculate the span of time between a pair of timestamps. Passing a single timestamp means the current date-time will be used as the second of the pair.

Time Zone

You may care about time zone if you want a precise age.

For example, a new days dawns earlier in Paris than in Montréal. So if running SQL code around midnight, you will get a different result if the code runs on a server with a different current default time zone.

If you care about this level of accuracy, specify the second date in the pair. Use a function that takes a time zone to determine today’s date.

String as Date-Time Type

Ideally you should be storing date-time values as date-time types. But I'm guessing that in this Question your date is actually a textual value.

Alphabetical Order = Chronological Order

If that date-of-birth column is a text value in SQL format, parallel to ISO 8601 format, then its alphabetical ordering is also a chronological ordering. So we can directly use such ordering to find the oldest value.

`LIMIT` To Get First Row

The LIMIT command truncates the result set to the first x number of rows. Sorting by the date of birth in ascending order means we will get the first row only.

Example Code

Tip: Naming columns and other objects in SQL with a trailing underscore avoids absolutely any collision with keywords/reserved words. So promises the SQL spec.

SELECT name_ , date_of_birth_ , age( timestamp date_of_birth_ ) AS age_
WHERE sex_ = 'm'
ORDER BY date_of_birth_ ASC
LIMIT 1
;