Menu
Can any one tell me how to get the list of an employee who have their birthdays today ....
Thanks, Vishal
430
-- Query to find workers, whose birthday is in current week SELECT * FROM @Workers WHERE DATEPART( Week, DATEADD( Year, DATEPART( Year, GETDATE()) - DATEPART( Year, DOB), DOB)) = DATEPART( Week, GETDATE()); The basic idea is the same as in the first query I explained. Only this time the week numbers are compared.
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()) ) .....
SQL Server provides several different functions that return the current date time including: GETDATE(), SYSDATETIME(), and CURRENT_TIMESTAMP.
In SQL Server, you can use the ISDATE() function to check if a value is a valid date. To be more specific, this function only checks whether the value is a valid date, time, or datetime value, but not a datetime2 value. If you provide a datetime2 value, ISDATE() will tell you it's not a date (it will return 0 ).
Sql Birthdate Query. In this post you will learn how to find birth date of customer or student. We will use getdate(), month and day functions in the query. Month function returns back the month of the date.(1-12 values). Day function returns back the day of the date. Getdate() function returns the moment the query has run.
The first query, which retrieves the workers whose birthday is in given days, can also be formulated as the following: The idea is, that the actual birthday is shifted to current year by adding the number of years between the year of DOB and the current year.
However, it has another function named GETDATE () that returns the current date and time. To get the current date, you use the CAST () function with the GETDATE () function as shown in the following statement: SELECT CAST ( GETDATE () AS DATE) 'Current Date' ; In this tutorial, you have learned how to use the SQL CURRENT_DATE function to return ...
The idea is, that the actual birthday is shifted to current year by adding the number of years between the year of DOB and the current year. After that, a simple date range comparison is used. The second query which retrieves the workers, whose birthday is on current week can be written like:
SELECT *
FROM Employees
WHERE DATEPART(d, DateOfBirth) = DATEPART(d, GETDATE())
AND DATEPART(m, DateOfBirth) = DATEPART(m, GETDATE())
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())
)
.....
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
