Do not store dates as
nvarchar
data type! Use
proper data type[
^].
To convert nvarchar data type, use
CONVERT[
^] function and
DATEDIFF[
^] to calculate age. Example:
DECLARE @tmp TABLE (dob NVARCHAR(30))
INSERT INTO @tmp (dob)
VALUES('1974-06-13'), ('1999-08-16'), ('2002-02-18')
SELECT CONVERT(DATETIME, dob) AS dob, GETDATE() AS CurrentDate, DATEDIFF(yyyy, dob, GETDATE()) AS Age
FROM @tmp
[EDIT]
If the
dob
field stores data like
'13-06-1974'
, the conversion will fail if the current server date-time setting is
yyyy-MM-dd
. In this case you need to change it by using
SET DATEFORMAT[
^] command. For example:
DECLARE @tmp TABLE (dob NVARCHAR(30))
INSERT INTO @tmp (dob)
VALUES('13-06-1974'), ('16-08-1999'), ('18-02-2002')
SET DATEFORMAT dmy;
SELECT CONVERT(DATETIME, dob) AS dob, GETDATE() AS CurrentDate, DATEDIFF(yyyy, dob, GETDATE()) AS Age
FROM @tmp
WHERE DATEDIFF(yyyy, dob, GETDATE()) BETWEEN 6 AND 14
[/EDIT]