Select with date returning empty

1

Good afternoon,  What could be wrong in my query that is returning empty because of the date condition.  Follow query:

   SELECT  
  imp_loja       AS LOJA,
  imp_item       AS ITEM,
  imp_desc       AS DESCRICAO,
  imp_dias_giro  AS DIAS_DE_GIRO,
  imp_nec        AS NECESSIDADE,
  imp_pedido     AS PEDIDO,
  imp_bancao     AS BANCAO,
  imp_romaneio   AS ROMANEIO, 
  imp_transito   AS TRANSITO,
  imp_gondola    AS GONDOLA,
  imp_fator      AS FATOR,
  imp_reposicao  AS REPOSICAO,
  imp_estoque    AS ESTOQUE,
  imp_estoque_dc AS EST_DC,
  (imp_nec - imp_fator) AS NEC_FAT,
  (imp_estoque / imp_fator) AS EXT_X_FATOR,
  (imp_estoque + imp_transito) AS ESTTRAN,
  (imp_nec - imp_fator) AS NECFAT,
  imp_data       AS DATA
  FROM importacao  WHERE imp_data =  NOW() 
       ORDER BY imp_desc

Follow the table:

# Host: localhost  (Version 5.5.5-10.1.13-MariaDB)
# Date: 2016-06-23 15:57:02
# Generator: MySQL-Front 5.3  (Build 5.33)

/*!40101 SET NAMES utf8 */;

#
# Structure for table "importacao"
#

CREATE TABLE 'importacao' (
  'imp_loja' int(11) DEFAULT NULL,
  'imp_item' int(11) DEFAULT NULL,
  'imp_desc' varchar(254) DEFAULT NULL,
  'imp_obs' varchar(50) DEFAULT NULL,
  'imp_dias_venda' int(11) DEFAULT NULL,
  'imp_dias_giro' int(11) DEFAULT NULL,
  'imp_nec' int(11) DEFAULT NULL,
  'imp_pedido' int(11) DEFAULT NULL,
  'imp_bancao' int(11) DEFAULT NULL,
  'imp_romaneio' int(11) DEFAULT NULL,
  'imp_transito' int(11) DEFAULT NULL,
  'imp_gondola' int(11) DEFAULT NULL,
  'imp_fator' int(11) DEFAULT NULL,
  'imp_reposicao' int(11) DEFAULT NULL,
  'imp_estoque' int(11) DEFAULT NULL,
  'imp_estoque_dc' int(11) DEFAULT NULL,
  'imp_id' int(11) NOT NULL AUTO_INCREMENT,
  'imp_data' timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY ('imp_id')
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#
# Data for table "importacao"
#

Note:  Already tried:

CURRENT_TIMESTAMP()
CURDATE()
NOW()
    
asked by anonymous 23.06.2016 / 20:57

2 answers

4

If you are comparing only the date, cast on both values to make the comparison 'in the right measure'.

The current comparison would be something like: WHERE '2016-06-26 12:00:01' = '2016-06-26 17:03:34' , when it should be WHERE '2016-06-26' = '2016-06-26'

Change:

WHERE imp_data =  NOW() 

To:

WHERE cast(imp_data as date) =  cast(NOW() as date)
    
23.06.2016 / 21:03
1

The problem should be that you compare your imp_data field with NOW , which represents the current date / time .

If you want to list records with imp_data equal to current date , use CURDATE instead of NOW . So:

SELECT  
  imp_loja       AS LOJA,
  imp_item       AS ITEM,
  imp_desc       AS DESCRICAO,
  imp_dias_giro  AS DIAS_DE_GIRO,
  imp_nec        AS NECESSIDADE,
  imp_pedido     AS PEDIDO,
  imp_bancao     AS BANCAO,
  imp_romaneio   AS ROMANEIO, 
  imp_transito   AS TRANSITO,
  imp_gondola    AS GONDOLA,
  imp_fator      AS FATOR,
  imp_reposicao  AS REPOSICAO,
  imp_estoque    AS ESTOQUE,
  imp_estoque_dc AS EST_DC,
  (imp_nec - imp_fator) AS NEC_FAT,
  (imp_estoque / imp_fator) AS EXT_X_FATOR,
  (imp_estoque + imp_transito) AS ESTTRAN,
  (imp_nec - imp_fator) AS NECFAT,
  imp_data       AS DATA
  FROM importacao  WHERE DATE(imp_data) =  CURDATE() 
       ORDER BY imp_desc

In addition, you should extract the date part from your field for comparison with the DATE() function.

    
23.06.2016 / 21:03