CODEWITHSUNDEEP
mysqlsql
Mario
Mario

Reputation: 4998

How to query between a series of dates per year?

Given the tables resumes and experiences in a one to many relationship with the following structure

resumes

  id int
  title varchar(255)

experiencies

  id int
  resume_id int
  from_date datetime
  to_date datetime

I try to get all resumes where a parameter year is between the values ​​of the fields from_date and to_date in experiences table

I attach a sqlfidlle with the structure

For example, given the following data

Table resumes enter image description here

Table experiencies enter image description here

If the parameter is 2010 the results I expect are the resume with id 1 and 2

1 because the experience in row 1 is in 2010 this despite the fact that the other experiences associated with the resume 1 are not in the year 2010. And 2 because 2010 is between 2008 and 2018

Thank you in advance for your help

Upvotes: 0

Views: 28

Answers (1)

Vinit
Vinit

Reputation: 2607

You can use Year function to get year from date columns and apply the where clause like below -

select * 
from experiencies 
where year(from_date) <= 2010 and year(to_date) >= 2010

You may use BETWEEN in where clause too. 

SET @Param = 2010
select * 
from experiencies 
where @Param between year(from_date) and year(to_date)

Upvotes: 1

Related Questions