# Age between to dates

I am trying to get results where a user is of a certain age between two given dates (DOB) is the dob in the database.
I am not getting any results tho, here is my query:

SELECT * FROM dbo.Users
where   30
BETWEEN DATEDIFF(YEAR, DobD, 01/01/2000)AND DATEDIFF(YEAR, DobD, 01/01/2050)

The above example is for someone being 30 yrs old between 01/01/2000 and 01/01/2050 and his birthdate on DobD

Help is appreciated
LVL 1
###### Who is Participating?

Senior DeveloperCommented:
The problem is not obvious.

You haven't specified dates as boundary values. But expressions. 01/01/2000 and 01/01/2050 are not date literals but integer division expressions. They result in an integer, which is implicit casted to a DATETIME.

E.g.

``````SELECT  CAST(01 / 01 / 2000 AS DATETIME) ,
CAST(01 / 01 / 2050 AS DATETIME)

DECLARE @Users TABLE ( ID INT, DOB DATE );

INSERT  INTO @Users
VALUES  ( 0, '18700101' ),
( 1, '19700101' ),
( 2, '19800101' ),
( 3, '19900101' ),
( 4, '20000101' ),
( 5, '20100101' ),
( 6, '20200101' ),
( 7, '20300101' );

SELECT  *
FROM    @Users
WHERE   30 BETWEEN DATEDIFF(YEAR, DOB, 01 / 01 / 2000) AND DATEDIFF(YEAR, DOB, 01 / 01 / 2050);

SELECT  *
FROM    @Users
WHERE   30 BETWEEN DATEDIFF(YEAR, DOB, '20000101') AND DATEDIFF(YEAR, DOB, '20500101');
``````
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.