Calculate the age in Microsoft SQL Server Management Studio

Question

I'm doing this but the result give me all the names who they are 30 years and less than 30 years.

This is the database:

Donor ( Donor-ID, First-name, Last-name, Date-of-birth, Sex, Date-of-donate)
Donor-phone (Donor-ID, Phone-number)
Clinic (Clinic-ID, Clinic-name, Clinic-Location)
Blood (Blood-ID, Blood Type)
The donate day (Blood Status, Donate Date)
Employee (Employee-ID, First-name, Last-name, Sex)

I used this code - where is it wrong?

SELECT
    GETDATE () AS FirstName, 
    FirstName, LastName,
    DATEADD(DD, 30, GETDATE()) AS [DateOfDonate- DateOfBirth],
    DATEADD(DD, 1-1-2000, GETDATE()) AS [DonateOfDate]
FROM
    Donor

I need to solve this question: find the names of donors who their age is above 30 years and have donated since 1/1/2000

Okay, `DATEADD()`'s second parameter is an integer. So in one place you're adding `30` days to `today`, and in another your adding `-2000` days *(you've included a calculation, not a date, and the result of `1 - 1 - 2000` is `-2000`)* to `today`. But I have no idea what it is that you *want* to do. The best advice is to give some example data *(from the `donor` table)* and then give some examples results that you want from that example data. Also, perhaps read this? https://stackoverflow.com/help/mcve — MatBailie, Dec 20 '17 at 01:17
Find the names of donors who their age is above 30 years and have donated since 1/1/2000. i want to solve this — Ser Al-zhraa, Dec 20 '17 at 02:00
This link is about ["How to calculate age in Years"](https://stackoverflow.com/questions/1572110/how-to-calculate-age-in-years-based-on-date-of-birth-and-getdate/1572411#1572411) answered by KM. — TraxX, Dec 20 '17 at 02:01

MatBailie · Answer 1 · 2017-12-20T12:09:11.797

0

SELECT
    *
FROM
   donor
WHERE
        [date-of-birth]  <= DATEADD(YEAR, -30, GETDATE())  -- Anyone born more than 30 years ago
    AND [date-of-donate] >= '2000-01-01'                   -- Anyone that donated since 1st Jan 2000

edited Dec 20 '17 at 12:09

answered Dec 20 '17 at 11:46

MatBailie

83,401
18
103
137

score -1 · Answer 2 · edited Dec 20 '17 at 11:40

-1

Sorry for the previous typo . I calculated with actual fields and then replaced with yours resulting into the mess. Please use below

  cast(datediff(Year,Date-Of-Birth,getdate()) as nvarchar(100))
+ ' Years , '
+ cast(datediff(Month,Date-Of-Birth,getdate())%12 as nvarchar(100))
+ ' months and '
+ cast(datediff(day,Date-Of-Birth,getdate()) as nvarchar(100))
+' days '

edited Dec 20 '17 at 11:40

MatBailie

83,401
18
103
137

answered Dec 20 '17 at 01:17

jptr

178
10

i need it as statement in SQL – Ser Al-zhraa Dec 20 '17 at 01:24
i need to solve this question: Find the names of donors who their age is above 30 years and have donated since 1/1/2000 – Ser Al-zhraa Dec 20 '17 at 04:21
The `day` part will give a very large number... And this sin't what the OP is asking for... – MatBailie Dec 20 '17 at 12:10

vishal Jaiswal · Answer 3 · 2017-12-20T12:51:36.297

-1

 select  FirstName+" "+ LastName from Donor
 where  datediff(year,Date-of-birth,getdate()) >30 and Date-of-donate>
 convert(date,'Date-of-donate',103)=convert(date,'Date-of-
 donate',103)='1/1/2000'

This will work for you

edited Dec 20 '17 at 12:51

answered Dec 20 '17 at 05:34

vishal Jaiswal

1
2

What's this meant to mean? `and Date-of-donate>='' convert(date,'Date-of-donate',103)=convert(date,'Date-of-donate',103)='1/1/2000'` – MatBailie Dec 20 '17 at 11:43

Calculate the age in Microsoft SQL Server Management Studio

3 Answers3