-1

I have a table listing people along with their date of birth

How can I convert that to a date, and then calculate their age in years?

My data looks as follows

|ID  |  Name|   DOB.     |
|1   | Sai  | 07/03/1997 |
|2   | Ram  | 07/03/1996 |

I would like to see:

ID | Name |AGE|  DOB     |
1  | Sai  |24 |07/03/1997|
2  | Ram  |23 |07/03/1996|
user3079
  • 1
  • 1
  • 2
    Dates have no format, they're binary values like `bit`, `int` and `decimal`. Formats apply only when parsing strings to dates or formatting dates as strings for display. If you store strings instead of actual dates you have a *major* bug. What does 7/3/1996 mean? March 7? July 3? You have *no* way of knowing, only assuming. There's no way to prevent mixed up values – Panagiotis Kanavos Aug 22 '21 at 10:39
  • If you use the correct type (ie `date`) you can use the database's date functions to calculate the age quite easily. Those functions differ from one database to another though. In SQL Server you could use `DATEDIFF(year,DOB,GetDate())` to get the current age. – Panagiotis Kanavos Aug 22 '21 at 10:42
  • You should never store dates in string data types. You should use date/datetime data types – nacho Aug 22 '21 at 10:44
  • 1
    There are several duplicate questions asking how to calculate the difference in years in either MySQL or SQL Server. All of them work with proper dates. Even if you keep storing dates as strings you'll have to parse the strings back into dates before you calculate the age – Panagiotis Kanavos Aug 22 '21 at 10:46
  • https://stackoverflow.com/questions/2533890/how-to-get-an-age-from-a-d-o-b-field-in-mysql – Kundan Singh Chouhan Aug 22 '21 at 10:51
  • @PanagiotisKanavos `DATEDIFF(year,DOB,GetDate())` doesn't give a person's age. Someone born on 31 December 2020 isn't 1, for example. – Thom A Aug 22 '21 at 10:51
  • @Larnu that's why I said there are a lot of duplicates. Now that only one tag is used we can mark the correct duplicates – Panagiotis Kanavos Aug 22 '21 at 10:58
  • I suggest not to store age in database as you will need it to be updated every day for rest of your web lifetime – Justinas Aug 22 '21 at 10:59
  • Does this answer your question? [How to get an age from a D.O.B field in MySQL?](https://stackoverflow.com/questions/2533890/how-to-get-an-age-from-a-d-o-b-field-in-mysql) – akshaivk Aug 22 '21 at 11:08

1 Answers1

-1

My recommendation is to fix the data, by changing the data type to a date:

alter table t
    set dob = str_to_date(dob, '%d/%m/%Y');

alter table t alter column dob date;

You can do this dynamically in a query:

select t.*, timestampdiff(year, curdate(), str_to_date(dob, '%d/%m/%Y'))
from t;

But you should really fix your data so columns are using the correct type.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Please share how to calculate without altering dob column. My doubts here is curdate() format is yyyy-mm-dd HH:mm:ss and my DOB column format is DD/mm/yyyy whether should I convert my DOB column as per curdate() or like how to proceed @Gordon Linoff – user3079 Aug 22 '21 at 12:51