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
AleksAsked:
Who is Participating?
 
ste5anSenior 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');

Open in new window

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.

All Courses

From novice to tech pro — start learning today.