MYSQL How to get list of records with time period included in specified year and month?-CodePudding

I have a table that links an employee to a project, the columns are:

BIGINT ID;
BIGINT FK_PROJECT;
BIGINT FK_EMPLOYEE;
DATE FROM;
DATE TO;

from and to are the first and last day during which the employee will be assigned to the project, now what I would like to do is getting records by using the id of the employee, the year and month, currently I have a whole date to give to the query but I can extract month and year if necessary. I need a query that by using these infos it fetches the records in which the year and month provided are included in the time period from-to, like if I have employee 3 and march 2022 I get all the projects said employee was assigned to in march 2022.

A project can last more than a year.

An employee could be assigned from 23/05/2022 to 29/08/2022.

I already tried select where fk_employee = 3 and 2022-03-01 between from and to but it clearly wouldn't work if from and to were in random days of the month like 13 and 23

Can you guys help me? I'm not good at all in sql

CodePudding user response：

You can use EXTRACT function

Try:

SELECT ID, 
       FK_PROJECT, 
       FK_Employee 
FROM tablename     
WHERE EXTRACT(YEAR_MONTH FROM 'your_date')  BETWEEN EXTRACT(YEAR_MONTH FROM `from`) and EXTRACT(YEAR_MONTH FROM `to`)

Fiddle

CodePudding user response：

SELECT ID, 
       FK_PROJECT, 
       FK_Employee 
FROM tablename 
WHERE DATE > '2020-01-01' 
AND DATE < '2022-01-01';

Not that if this does not give you the results you are expecting, the most likely reason is that the DATE field is not defined as a DATE OR DATETIME column in MySql. If this is the case, you can change the column type, or if that is not an option, you can use str_to_date to treat that column as a date column for this query.