Click here to Skip to main content
15,919,028 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.

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]
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.

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