How to convert DATETIME to DATE using NOW function?

Asked

Viewed 31 times

0

Can you give me a hint of how I can get around the problem below ?

The goal of the Query below is to return the number of times I have received ABANDON, CONGESTION and MACHINE service status during a day. It would look like this, if today is day '2021-08-04 11:11:11' when the query was executed would have to return the result of the previous day '2021-08-04', staying that way

'DISCADOR_ABANDONADAS |DISCADOR_IMPRODUTIVAS_MENSAGEM_OPERADORA |DISCADOR_IMPRODUTIVAS_SECTR
         44                          564                                  2222

My problem here is I’m having trouble converting the column calldate type DATETIME to DATE picking up one less day. You can tell me what to do in this case ?

SELECT  
    @VAR23:= COUNT(CASE WHEN statusatendimento = 'ABANDON'    THEN statusatendimento END) AS 'DISCADOR_ABANDONADAS',
    @VAR24:= COUNT(CASE WHEN statusatendimento = 'CONGESTION' THEN statusatendimento END) AS 'DISCADOR_IMPRODUTIVAS_MENSAGEM_OPERADORA',
    @VAR25:= COUNT(CASE WHEN statusatendimento = 'MACHINE'    THEN statusatendimento END) AS 'DISCADOR_IMPRODUTIVAS_SECTR_ELETRONICA_FAX',
FROM chamadas
    WHERE fila IN (1000,1002,1010,1013,1016)
    AND CAST(calldate AS DATE) =NOW()-1;
  • Which bank are you using? See if it helps, replace "+" with "-" in your case: Mysql’s now() +1 day.

  • Hi Jean, I understand the problem. What’s happening is that for some reason when I try to get records from a month before my variable gets the value 0, I’m only able to get records from the current month. That was the change I made at the end of Query ... AND CAST(LASTCALL AS DATE) = DATE(NOW()-7 ;

No answers

Browser other questions tagged

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