I have a table listing customers along with their date of birth (currently a nvarchar(25))

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

Given data:

```ID    Name   DOB
11     Peter   1997-02-06 00:00:00
12     Jim  1994-04-24 00:00:00
```

I want the result to be:

```ID    Name   AGE  DOB
11     Peter   24   1997-02-06 00:00:00
12     Jim     27   1994-04-24 00:00:00```
Feb 23, 2022 in Database 1,372 views

## 1 answer to this question.

Following can be a solution to your problem:

BEST METHOD FOR YEARS IN INTEGER

```DECLARE @Now  datetime, @Dob datetime
SELECT   @Now='1990-05-05', @Dob='1980-05-05'  --results in 10
--SELECT @Now='1990-05-04', @Dob='1980-05-05'  --results in  9
--SELECT @Now='1989-05-06', @Dob='1980-05-05'  --results in  9
--SELECT @Now='1990-05-06', @Dob='1980-05-05'  --results in 10
--SELECT @Now='1990-12-06', @Dob='1980-05-05'  --results in 10
--SELECT @Now='1991-05-04', @Dob='1980-05-05'  --results in 10

SELECT
(CONVERT(int,CONVERT(char(8),@Now,112))-CONVERT(char(8),@Dob,112))/10000 AS AgeIntYears```

you can change the above 10000 to 10000.0 and get decimals, but it will not be as accurate as the method below.

BEST METHOD FOR YEARS IN DECIMAL

```DECLARE @Now  datetime, @Dob datetime
SELECT   @Now='1990-05-05', @Dob='1980-05-05' --results in 10.000000000000
--SELECT @Now='1990-05-04', @Dob='1980-05-05' --results in  9.997260273973
--SELECT @Now='1989-05-06', @Dob='1980-05-05' --results in  9.002739726027
--SELECT @Now='1990-05-06', @Dob='1980-05-05' --results in 10.002739726027
--SELECT @Now='1990-12-06', @Dob='1980-05-05' --results in 10.589041095890
--SELECT @Now='1991-05-04', @Dob='1980-05-05' --results in 10.997260273973

SELECT 1.0* DateDiff(yy,@Dob,@Now)
+CASE
WHEN @Now >= DATEFROMPARTS(DATEPART(yyyy,@Now),DATEPART(m,@Dob),DATEPART(d,@Dob)) THEN  --birthday has happened for the @now year, so add some portion onto the year difference
(  1.0   --force automatic conversions from int to decimal
* DATEDIFF(day,DATEFROMPARTS(DATEPART(yyyy,@Now),DATEPART(m,@Dob),DATEPART(d,@Dob)),@Now) --number of days difference between the @Now year birthday and the @Now day
/ DATEDIFF(day,DATEFROMPARTS(DATEPART(yyyy,@Now),1,1),DATEFROMPARTS(DATEPART(yyyy,@Now)+1,1,1)) --number of days in the @Now year
)
ELSE  --birthday has not been reached for the last year, so remove some portion of the year difference
-1 --remove this fractional difference onto the age
* (  -1.0   --force automatic conversions from int to decimal
* DATEDIFF(day,DATEFROMPARTS(DATEPART(yyyy,@Now),DATEPART(m,@Dob),DATEPART(d,@Dob)),@Now) --number of days difference between the @Now year birthday and the @Now day
/ DATEDIFF(day,DATEFROMPARTS(DATEPART(yyyy,@Now),1,1),DATEFROMPARTS(DATEPART(yyyy,@Now)+1,1,1)) --number of days in the @Now year
)
END AS AgeYearsDecimal```
• 7,060 points

## How to split a string of text in excel based on a given word?

I have a list of combinations of ...READ MORE

## How to get address, Column Name and Row Name of all marked rows in Excel table as rows in new worksheet

need the row/column combinations marked with an ...READ MORE

## how to change format of date from mm/dd/yyyy to dd-mmm-yyyy in MS Excel

First, pick the cells that contain dates, ...READ MORE

## How to load data of .csv file in MySQL Database Table?

At first, put the dataset in the ...READ MORE

## How do I UPDATE from a SELECT in SQL Server?

MERGE INTO YourTable T USING ...READ MORE

## How do I UPDATE from a SELECT in SQL Server?

INSERT INTO Table (col1, col2, col3) SELECT col1, ...READ MORE

## What is a stored procedure?

A stored procedure is a set of ...READ MORE

## LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

At the top level there are mainly ...READ MORE