5

Can any one tell me how to get the list of an employee who have their birthdays today ....

Thanks, Vishal

Vegeta
  • 291
  • 2
  • 7
  • 20

5 Answers5

15
SELECT *
FROM Employees
WHERE DATEPART(d, DateOfBirth) = DATEPART(d, GETDATE())
    AND DATEPART(m, DateOfBirth) = DATEPART(m, GETDATE())
Miika L.
  • 3,333
  • 1
  • 24
  • 35
  • 3
    One comment to make on this is that this assumes that `DateOfBirth` is is stored in the same time zone as that set on the SQL server. Should the `DateOfBirth` be stored in UTC then `GETUTCDATE()` would be required. – rrrr-o Jan 10 '12 at 13:29
  • @Dems I checked the execution plan in my sample database and it at least claims to be using the index. – Miika L. Jan 10 '12 at 13:38
  • @miikaL. - It will be using a scan at best. It's still a full scan and not a seek. – MatBailie Jan 10 '12 at 13:44
  • But its a scan on the index, and not a full table scan, no? – Miika L. Jan 10 '12 at 13:49
  • @MiikaL. - Yes, it may scan the index, but it's still a full scan. The alternative in my answer is really long winded, but yields a seek instead of a scan. – MatBailie Jan 10 '12 at 13:51
  • This solution is incomplete as it doesn't handle Leap years, see my solution on https://stackoverflow.com/a/69647961/7886965 – ili Oct 20 '21 at 14:36
1

Ideally a bit more information on the structure of your data sources would help, but a simple answer providing your employee records have a D.O.B. field would be to compare the day and month of this against the current system date in the where clause of your query.

Something along the following lines:

select * from wherever
where
....
(
  datepart(d, EmployeeDOB) = datepart(d, getdate()) and
  datepart(m, EmployeeDOB) = datepart(m, getdate())
)
.....
Andy Pollitt
  • 31
  • 3
  • Hey andy , thnx a lot.... I got it working.. :) – Vegeta Jan 10 '12 at 13:41
  • 1
    @VishalAvhad - Please note, this option obfuscates the INDEX and will force a full table scan. If you have a large number of records this will be a significant overhead. – MatBailie Jan 10 '12 at 13:45
0

WHERE month and day of employee's birthday match today's month and day (obviously). More specific instructions would require more input.

Michael Krelin - hacker
  • 138,757
  • 24
  • 193
  • 173
0

You can do something like this:

select e.name from employeeTable e
where right(e.birthday) = right(convert(varchar(8), getdate(), 112), 4)

or

select e.name from employeeTable e
where datepart(d, e.birthday) = datepart(d, getdate())
    and datepart(m, e.birthday) = datepart(m, getdate())
aF.
  • 64,980
  • 43
  • 135
  • 198
0

Although this is much more long winded, it does avoid having to scan the entire table looking for matches.

WITH
  a_century AS
(
  SELECT 1 AS year
  UNION ALL
  SELECT year * 2 + 0 AS year FROM a_century WHERE year < 64
  UNION ALL
  SELECT year * 2 + 1 AS year FROM a_century WHERE year < 64
)
SELECT
  *
FROM
  yourTable
INNER JOIN
  a_century
    ON yourTable.birthday = DATEADD(year, -a_century.year, DATEADD(day, DATEDIFF(day, 0, getDate()), 0))
MatBailie
  • 83,401
  • 18
  • 103
  • 137