how to get record form table having age between 18 to 23 years.. Please Help..

Question

3.67/5 (2 votes)

See more:

I have sql table member like:-
id name dob
1 Ashwin 18/02/1989
2 Bhushan 30/12/1986
3 sachin 15/03/1991
4 ash 02/11/1980
5 kevin 03/03/1993

how to get record form table having age between 18 to 23 years.. Please Help..

Posted 2-Mar-14 21:40pm

Ashwin Ganvir

Add a Solution

6 solutions

Solution 2

try the below script..

Create table member
(
mno int identity(1,1) primary key,
name varchar(50),
dob varchar(50)
)
insert into member(name,dob) values('Ashwin','18/02/1989')
insert into member(name,dob) values('Bhushan','30/12/1986')
insert into member(name,dob) values('Sachin','15/03/1991')
insert into member(name,dob) values('Ash','02/11/1980')
insert into member(name,dob) values('Kevin','03/03/1993')

SELECT mno id,name,dob, DATEDIFF(YEAR,CONVERT(DATETIME, dob,105),GETDATE()) Age 
from member
where DATEDIFF(YEAR,CONVERT(DATETIME, dob,105),GETDATE()) between 18 and 23
drop table member

if you feel like useful then accept my answer and up vote it.

Posted 2-Mar-14 22:09pm

Ramug10

Comments

Vedat Ozan Oner 3-Mar-14 4:11am

it is not good to store a date info as varchar !!

Solution 4

You need to create a user function first

SQL

create function [dbo].[fn_GetAge]
(@dob AS datetime,@currentDate as datetime)
returns int
AS
BEGIN
DECLARE @age int

IF cast(datepart(m,@currentDate) as int) > cast(datepart(m,@dob) as int)
  SET @age = cast(datediff(yyyy,@dob, @currentDate) as int)
else
  IF cast(datepart(m,@currentDate) as int) = cast(datepart(m,@dob) as int)
    IF datepart(d,@currentDate) >= datepart(d,@dob)
      SET @age = cast(datediff(yyyy,@dob, @currentDate) as int)
    ELSE
      SET @age = cast(datediff(yyyy,@dob, @currentDate) as int) -1
  ELSE
    SET @age = cast(datediff(yyyy,@dob, @currentDate) as int) - 1
RETURN @age
END

Once the user function has been created, use it like the one mentioned below

SQL

SELECT ID, NAME, DOB, fn_GetAge(DOB, GetDate()) AS AGE FROM TABLE

You can put the result in a temporary table to get the desired output.
Hope this solution helps

Posted 2-Mar-14 22:22pm

rvinodsden

Updated 2-Mar-14 22:47pm

v3

Solution 6

SQL

SELECT mno, name, dob
FROM member  where datediff(year,convert(datetime,dob,103),GETDATE()) BETWEEN 18 AND 23

Posted 2-Mar-14 22:31pm

King Fisher

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Accepted Answer · 2014-03-02T22:02:00

Solution 1

You should use DATEDIFF[^] function.

SQL

SELECT id, name, dob
FROM member
WHERE DATEDIFF(yy, dob, GETDATE()) BETWEEN 18 AND 23

Posted 2-Mar-14 22:02pm

Maciej Los

Vedat Ozan Oner · Accepted Answer · 2014-03-02T22:09:00

Solution 3

or DATEADD[^] function:

SQL

select * from member
where DOB between DATEADD(YEAR, -23, getdate()) and DATEADD(YEAR, -18, getdate())

but datediff better :)

Posted 2-Mar-14 22:09pm

Vedat Ozan Oner

Comments

Maciej Los 3-Mar-14 4:41am

Good one, +5!

Vedat Ozan Oner 3-Mar-14 4:55am

yours is still better. I've just showed another way.

[no name] 3-Mar-14 4:49am

++5

Dharmesh Hadiyal · Accepted Answer · 2014-03-02T22:27:00

Solution 5

SQL

SELECT *
FROM member
WHERE DATEDIFF(yy, dob, GETDATE()) BETWEEN 18 AND 23

Posted 2-Mar-14 22:27pm

Dharmesh Hadiyal

Comments

Maciej Los 3-Mar-14 4:43am

The plagiat of the solution 1. That's the reason of 1 star.