Displaying the last 30 days of a query

Question

Displaying the last 30 days of a query

Navigation

#1 by (8 votes)
#2 by (4 votes)

6

I'm trying some alternatives to show only the records of the last 30 days, but I could not make it work, the query I have looks like this:

SELECT comunidade.descricao AS nomeunidade
      ,comcargo.descricao AS nomecargo
      ,comcolaborador.nome
      ,date_format(comcolaborador.dtadmissao,'%d/%m/%Y') AS dataadm
      ,comcolaborador.foto
      ,comcolaborador.idcargo
      ,comcolaborador.idunidade
      ,comcolaborador.login

  FROM comunidade
 INNER JOIN comcolaborador
    ON comunidade.idunidade = comcolaborador.idunidade
 INNER JOIN comcargo
    ON comcolaborador.idcargo = comcargo.idcargo

 WHERE comcolaborador.ativo = 1
   AND comcolaborador.dtadmissao >= '2016-08-01'
   AND comunidade.ativo = 1
   AND comcargo.ativo = 1
 ORDER BY comcolaborador.dtadmissao DESC

And what I tried was this, following some tips and suggestions:

 WHERE data >= dateadd(minute,-30,getdate())

mysql sql query

asked by anonymous 03.02.2017 / 18:31

2 answers

4

I think this might help:

>=  CONVERT(CHAR(10), GETDATE()-30, 101)

GETDATE () - 30, will bring the last 30 days to the query, and we use convert so that the date exits in the following format: 02/06/2017 (which is the 101 format). If you want in other formats I suggest you go testing 102,103,104 and so on.

select CONVERT (varchar(20), getdate(), 101) --vá trocando os valores do 101 para ver qual melhor de atende, mas acredito que seja 101.

06.02.2017 / 13:25

Angular blocking submit form GET Why use static methods in python

score 8 · Accepted Answer

Option 1

To decrease a date use DATE_SUB

DATE_SUB (date, INTERVAL expr type)

In your case:

data >= DATE_SUB(NOW(), INTERVAL 30 MINUTE)

Option 2

Another method described this answer from Stack Overflow in English is as follows:

...
WHERE data BETWEEN NOW() - INTERVAL 30 DAY AND NOW()
...

Note: GETDATE is unique ( considering the most widespread databases) of Transact-SQL (Deployment used by SQL Server). In other banks we have other functions with similar functionality, listed below:

MySQL: NOW, CURRENT_TIMESTAMP, CURRENT_TIME, CURRENT_DATE, CURTIME;
Firebird: CURRENT_DATE;
Oracle: SELECT SYSDATE FROM DUAL;
PostgreSQL: NOW, CURRENT_TIMESTAMP;