-1

I have a table with two rows "birthday" and "age". Actually the row "age" is empty.

How can I with a simple update set my age from the birthday ?

create table ex(
  birthday nvarchar(50),
  age int
)

insert into ex values ('02-04-1962',null)


select * from ex


--update ex set age = ....

DB FIDDLE

KeusT
  • 105
  • 8
  • 4
    Never store age. Most people tend to become older each year, and your data will soon be out-of-date. Store date/year of birth instead. – jarlh Jun 22 '22 at 08:25
  • What you may be looking for instead is a `VIEW` that calculates the age from the date. – Thorsten Kettner Jun 22 '22 at 08:26
  • 5
    and to help calculating the age, use a `date` field in stead of `nvarchar(50)` – mikkel Jun 22 '22 at 08:27
  • It's not to be stored it is to be used immediately – KeusT Jun 22 '22 at 08:28
  • If you have to have an age column in your table, then do so as a calculated column. You won't be able to `PERSIST` such a column though, so using it in a queries `WHERE` would be a poor choice as no indexes would be able to be used. – Thom A Jun 22 '22 at 08:28

1 Answers1

0

I assume you need to calculate age for some reason, but it is not a good idea to store data that can be calculated, especially if this data will be expired over time, such as in your case.

Putting that aside, this is one way of doing that:

You will need to convert birthday from string to date, then use datediff

update ex set age = datediff(year, cast(birthday as date), getdate())
Ahmad
  • 12,336
  • 6
  • 48
  • 88
  • 1
    Someone born on `2000-12-31` is not the age of 1 on `2001-01-01`. And then even if this were the correct logic, what happens tomorrow when someone inevitably has a birthday? – Thom A Jun 22 '22 at 08:30
  • the birthday is in nvarchar(255) and the format is : 'DD--MM-YYYY' '02-04-1962' – KeusT Jun 22 '22 at 08:32
  • @KeusT fix your design; don't store dates as a `(n)varchar` there are **6** date and time data types to choose from. As for the value `N'02-04-1962'` is that 02 April 1962 or 04 February 1962? How do *you* know? Or, more importantly, how would the SQL Server instance know? What's stopping someone from entering the date `N'12-27-1974'`? Or a nonsense value like `N'02-29-1985'`? – Thom A Jun 22 '22 at 08:35
  • @KeusT no, this doesn't work great, the logic is wrong... People don't age on the first day of the year. – Thom A Jun 22 '22 at 10:05
  • Try `SELECT DATEDIFF(YEAR, CAST('20000901' AS date), GETDATE()) AS Age;` @KeusT . This *incorrectly* gives the age `22`. Someone born on 01 September 2000 doesn't turn 22 until 01 September 2022; they are *not* 22 today. – Thom A Jun 22 '22 at 10:08
  • @Larnu I doubt that the OP wanted that level of precision and accuracy when asking this question. I guess he is fine with the possibility of an error margin of +1 or -1 years in the age calculation. For a better solution, I would count datediff in Days and divide by 365 – Ahmad Jun 22 '22 at 12:25
  • If they wanted a margin of error of <= 365 days then they aren't after age, @Ahmad . The method in that comment also wouldn't work as someone got older; leap years (normally) occur every 4 years. – Thom A Jun 22 '22 at 12:26