I use MySQL. I have a database with data about employees: name, lastname, hiredate (year-month-day). I want to get a number of days worked in the first month of employment. The months have different number of days and I couldn’t figure out how to do it.
I think TIMESTAMPDIFF() function could be useful, but I can’t figure out the proper syntax.
I am a beginner in SQL and would apprecate your forbearance.
>Solution :
If you have a column called hiredate of type DATE, you can determine the last day of the month where the person was hired with LAST_DAY(hiredate).
SELECT LAST_DAY(hiredate) AS hired_in_month_ending
Then you can determine the number of calendar days between the hire date and the end of the month with DATEDIFF(end, start).
SELECT 1 + DATEDIFF(LAST_DAY(hiredate), hiredate) AS calendar_days_in_month_hired
(The 1 + is to deal with the fact that a person hired on the last day of the month actually was on payroll for one day.)
As far as the number of days worked, that depends on all sorts of factors you didn’t mention. Weekends? Holidays? Absences?