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

Question

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

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

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

I tried with datepart of SQL Server but it does not maintain the main feature of the MySQL query, which is to maintain the month-to-month range but also to take into account the training that extends for more than a month. >

Here is 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, 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 me training that started and ended in January 2017. I need to be counted for example training that started in October 2016 and have been finalized in August 2017.

mysql sql

asked by anonymous 20.02.2018 / 03:15

2 answers

0

You want the '201705' period matches between the dates for training.data_training start and training.data_training

There are a few ways to do this, you need to test to see the performance, but you 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 FORMAT function tends to be slower but depends on the volume of data in your table, it's worth testing.

20.02.2018 / 13:08

Warning: session_start (): Failed to read session data: user (path: C: \ xampp \ tmp) How to add an empty directory in a remote git repository?

score 0 · Accepted Answer

Here are suggestions that simulate the build 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

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