Migrate EXTRACT(YEAR_MONTH,data) from Mysql to SQL Server

Question

I have a Mysql query that would need to convert to SQL Server.

I tried with datepart SQL Server but it does not maintain the main feature of the Mysql query, which is to maintain the range month by month but also take into account the trainings that extend for more than a month.

Follow the SELECT used in Mysql:

SELECT DISTINCT
   *
FROM
   treinamentos,usuario_x_treinamento,usuario
WHERE
   status_treinamento = 'REALIZADO' 
   AND
      '201705' BETWEEN
      EXTRACT(YEAR_MONTH FROM treinamentos.data_inicio_treinamento) AND
      EXTRACT(YEAR_MONTH FROM treinamentos.data_fim_treinamento)
   AND
      usuario_x_treinamento.id_usuario = usuario.id_usuario
   AND
      usuario_x_treinamento.id_treinamento = treinamentos.id_treinamentos;

In SQL Server has remained so far something like:

SELECT count(DISTINCT usuario.id_usuario) as TREINADO,
FROM treinamentos, usuario_x_treinamento, usuario
WHERE 
datepart(YEAR, treinamentos.data_inicio_treinamento) = '2017'
and datepart(YEAR, treinamentos.data_fim_treinamento) = '2017'
AND DATEPART(MONTH, treinamentos.data_inicio_treinamento) = '01'
and DATEPART(month, treinamentos.data_fim_treinamento) = '01'
AND usuario_x_treinamento.id_usuario = usuario.id_usuario
and status_treinamento = 'REALIZADO'
AND usuario_x_treinamento.id_treinamento = treinamentos.id_treinamentos;

But as I had already said, this select is only returning training that began and ended in January 2017. I need to be counted for example training that began in October 2016 and have been completed in August 2017.

Answer 1

Here are suggestions that try to simulate the construction used in Mysql:

-- código #1
...
where 201705 between 
      (year(treinamentos.data_inicio_treinamento) * 100 + month(treinamentos.data_inicio_treinamento)) and
      (year(treinamentos.data_fim_treinamento) * 100 + month(treinamentos.data_fim_treinamento)) 
...

or else

-- código #2
...
where '201705' between 
      convert(char(6), treinamentos.data_inicio_treinamento, 112) and
      convert(char(6), treinamentos.data_fim_treinamento, 112) 
...

Answer 2

You want the period '201705' between the dates of trainings.data_home_training and trainings.data_fim_training

There are some ways to do this, you need to test to see the performance, but could do:

WHERE treinamentos.data_inicio_treinamento >= '20170501'
  AND treinamentos.data_fim_treinamento < '20170601'

another way would be:

WHERE FORMAT(treinamentos.data_inicio_treinamento, 'yyyyMM') >= '201705' 
  AND FORMAT(treinamentos.data_fim_treinamento , 'yyyyMM') <= '201705' 

The function FORMAT tends to be slower, but it depends on the volume of data in your table, it is worth testing.