SQL Select Upcoming Birthdays

by

Note: I’ve edited this to fix what I believe was a significant bug. The currently posted version works for me.

This should work after you modify the field and table names to correspond to your database.

SELECT 
  BRTHDATE AS BIRTHDAY
 ,FLOOR(DATEDIFF(dd,EMP.BRTHDATE,GETDATE()) / 365.25) AS AGE_NOW
 ,FLOOR(DATEDIFF(dd,EMP.BRTHDATE,GETDATE()+7) / 365.25) AS AGE_ONE_WEEK_FROM_NOW
FROM 
  "Database name".dbo.EMPLOYEES EMP
WHERE 1 = (FLOOR(DATEDIFF(dd,EMP.BRTHDATE,GETDATE()+7) / 365.25))
          -
          (FLOOR(DATEDIFF(dd,EMP.BRTHDATE,GETDATE()) / 365.25))

Basically, it gets the # of days from their birthday to now, and divides that by 365 (to avoid rounding issues that come up when you convert directly to years).

Then it gets the # of days from their birthday to a week from now, and divides that by 365 to get their age a week from now.

If their birthday is within a week, then the difference between those two values will be 1. So it returns all of those records.

More Related Contents:

  1. How do I UPDATE from a SELECT in SQL Server?
  2. How to calculate age (in years) based on Date of Birth and getDate()
  3. CROSS JOIN vs INNER JOIN in SQL
  4. Referring to a Column Alias in a WHERE Clause
  5. Convert Month Number to Month Name Function in SQL
  6. Function vs. Stored Procedure in SQL Server
  7. Multiple INSERT statements vs. single INSERT with multiple VALUES
  8. Limit on the WHERE col IN (…) condition
  9. Removing leading zeroes from a field in a SQL statement
  10. Generate a resultset of incrementing dates in TSQL
  11. Getting result of dynamic SQL into a variable for sql-server
  12. How to get difference between two rows for a column field?
  13. Better techniques for trimming leading zeros in SQL Server?
  14. What’s the best way to select the minimum value from several columns?
  15. Counting DISTINCT over multiple columns
  16. SQL Server SELECT into existing table
  17. How to check if a stored procedure exists before creating it
  18. Correct use of transactions in SQL Server
  19. How can I include null values in a MIN or MAX?
  20. Can an INNER JOIN offer better performance than EXISTS
  21. Find the real column name of an alias used in a view?
  22. Get Start and End Date from Week Number SQL Server
  23. How to get the identity of an inserted row?
  24. Get the last day of the month in SQL
  25. How to create a pivot query in sql server without aggregate function
  26. TSQL – How to use GO inside of a BEGIN .. END block?
  27. SQL take just the numeric values from a varchar
  28. SQL Server FOR XML Path make repeating nodes
  29. Why is RAND() not producing random numbers?
  30. Should every SQL Server foreign key have a matching index? [closed]

Leave a Comment