question

japper_nrw avatar image
japper_nrw asked

find today´s birthdays in a query

Dear all, I have a question about a query in SQL. I have a table with people´s birthdays and I need a query to show a result of those people, who have birthday today. My idea was: SELECT T0.[peopleID], T0.[peopleName], T0.[peoplelastName], T0.[peoplebirthDate] FROM PEOPLETABLE T0 WHERE T0.[peoplebirthDate] = GETDATE() This query doesn´t show any results....where is my problem ?? Many thanks in advance kind regards.
query-resultsdates
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Lukasz8519 avatar image
Lukasz8519 answered
maybe You should use SELECT T0.[peopleID], T0.[peopleName], T0.[peoplelastName], T0.[peoplebirthDate] FROM PEOPLETABLE T0 WHERE T0.[peoplebirthDate] > CAST(DATEADD(DAY,-1,GETDATE()) as DATE) AND [peoplebirthDate] < CAST(DATEADD(DAY,1,GETDATE()) as DATE)
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

born between yesterday and tomorrow?
1 Like 1 ·
grater than yesterday and less then tommorow
0 Likes 0 ·
But... How many people in your PeopleTable were born (born) today? That's what this query is returning. Not how many people celebrate the *anniversary* of their birth...
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
Because GETDATE() returns a datetime datatype, including ***the current time***. Birthdays are generally matched on the ***date*** alone. Also GETDATE() is now, today, current time. How many people in your table are born today? Does that give you enough to work out what's going wrong?
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks a lot... "sometimes you can´t see the trees in a forest" ;)
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
I assume you're talking about celebrating birthdays, not how many people were actually *born* today... SELECT T0.[peopleID], T0.[peopleName], T0.[peoplelastName], T0.[peoplebirthDate] FROM PEOPLETABLE T0 WHERE datepart(month, T0.[peoplebirthDate]) = datepart(month,GETDATE()) AND datepart(day, T0.[peoplebirthDate]) = datepart(day, GETDATE()) or something. The thing is, not to match on the YEAR part of the date... ...unless your database is literally tracking people born today because you're doing a report in the neonatal clinic...
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.