How to view the last 30 days of a query

Asked

Viewed 1,510 times

6

I’m trying some alternatives to show only the records of the last 30 days, but I couldn’t make it work, the query I have is 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 a few tips and suggestions:

 WHERE data >= dateadd(minute,-30,getdate())
mysql sql database query
  • 3

    Just for the record: getdate(), if memory serves, it does not exist in Mysql. It works only in SQL Server.

  • 1

    Just as Sorack commented, the equivalent of getdate in the MySQL, is: curdate or now

2 answers

8


Option 1

To decrement a date use DATE_SUB

DATE_SUB(date, INTERVAL expr type)

In your case:

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

Option 2

Other method described in this Stack Overflow response is as follows:

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

Observing: The function GETDATE is exclusive (considering the most widespread databases) of Transact-SQL (Implementation used by SQL Server). In other banks we have other functions with similar functioning, listed below:

  • Mysql: NOW, CURRENT_TIMESTAMP, CURRENT_TIME, CURRENT_DATE, CURTIME;
  • Firebird: CURRENT_DATE;
  • Oracle: SELECT SYSDATE FROM DUAL;
  • Postgresql: NOW, CURRENT_TIMESTAMP;
  • 2

    Thanks @Sorack, helped a lot, thanks a lot.

4

I believe this can help :

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

The GETDATE()-30, will bring the last thirty days for the query, and we use the Convert to get the date out in the following format : 02/06/2017 (which is the 101 format). If you want in other formats I suggest 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.

Browser other questions tagged mysql sql database query

You are not signed in. Login or sign up in order to post.