Click here to Skip to main content
15,886,873 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
hii.. all

i m anil.i m new in development.. i want to calculate no. of candidates between 1 to 14 age.

in my table the dob is a nvarchar type..


please help

thanks in advance..
Posted
Comments
[no name] 29-Mar-14 15:03pm    
Get the D.O.B. from the database that you mistakenly stored as an nvarchar, convert it to a DateTime so you can do date calculations on it and then calculate the age.

good idea is to use Date , or datetime datatype for this type insted of using nvarchar
 
Share this answer
 
Comments
Maciej Los 29-Mar-14 16:10pm    
Good tip! Voted: 4 stars, because of incomplete answer.
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:
SQL
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:

SQL
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]
 
Share this answer
 
v4
Comments
bindash 29-Mar-14 16:14pm    
the error occured

The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.
Maciej Los 29-Mar-14 16:17pm    
Is it a joke? Above example executes without any errors.
bindash 29-Mar-14 16:24pm    
it's not a joke.. same error is occured..in sql server 2008 r2
Maciej Los 29-Mar-14 16:25pm    
Share your sample data and i'll promise to improve my answer.
bindash 29-Mar-14 16:27pm    
i want to fatch data from this table ..

SELECT CONVERT(DATETIME, [dob]) AS dob, GETDATE() AS CurrentDate, DATEDIFF(yyyy, [dob], GETDATE()) AS Age
FROM[SCJ].[dbo].[13CHILD_D]

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900