Query with date in variable in MySQL returning empty

Navigation
2

I need to make a query in MySQL setting 3 variables, when I know in the case of integer number of the right one, more when I include data ta returning empty.

Follow my query , what could be doing wrong? 

SET @tipo    = 47;
SET @dataini = 2016-03-17;
SET @datafin = 2016-03-17;
    SELECT 
      * 
       FROM sale 
            WHERE  start_time BETWEEN '@dataini' AND '@datafin' 
               AND sale_type = @tipo

The result of the query follows without using the variables: 

2   44  346479  2016-03-17 17:44:58 0   0   47  0   10.000  0.000   0   0   395 0   0.000   0.000   0.000   11962677.200    0.000   2016-03-17 16:47:51 2016-03-17 17:44:58     2016-03-17  0   0       0   0.000   0   0   0   1458243445  1458243508  1458244061  
2   44  346483  2016-03-17 17:55:56 0   0   47  0   10.000  10.000  0   0   395 35  0.000   0.000   0.000   11962677.200    0.000   2016-03-17 16:58:57 2016-03-17 17:55:56     2016-03-17  0   0       0   0.000   0   0   0   1458244642  1458244678  1458244720  
2   44  346510  2016-03-17 18:48:33 0   0   47  0   10.000  10.000  0   0   621 0   0.000   0.000   0.000   11962906.700    0.000   2016-03-17 17:51:38 2016-03-17 18:48:33     2016-03-17  0   0       0   0.000   0   0   0   1458247805  1458247848  1458247877  
2   44  346513  2016-03-17 18:50:29 0   0   47  0   10.000  0.000   0   0   621 0   0.000   0.000   0.000   11962906.700    0.000   2016-03-17 17:53:25 2016-03-17 18:50:29     2016-03-17  0   0       0   0.000   0   0   0   1458247931  1458247977  1458247992
    
asked by anonymous 04.05.2016 / 21:35

1 answer

2

I do not know if you have any other issues, but you should put the date in quotation marks. The way it was he made the 2016 account minus 3 minus 17. 

SET @tipo    = 47;
SET @dataini = '2016-03-17';
SET @datafin = '2016-03-17';
    SELECT 
      * 
       FROM sale 
            WHERE  start_time BETWEEN @dataini AND @datafin 
               AND sale_type = @tipo

I test the following: 

SET @tipo    = 47;
SET @dataini = '2016-03-17';
SET @datafin = '2016-03-18';
    SELECT 
      * 
       FROM sale 
            WHERE  start_time BETWEEN @dataini AND @datafin 
               AND sale_type = @tipo

When the time is used, you can not specify only the date, you have to put the whole time you want in the range. Or you have to use dates that contemplate all the schedules of a day. '2016-03-17' is the same as '2016-03-17 00:00:00'

I do not think it's ideal in all situations, but I could do the query as in this SQLFiddle .

    
04.05.2016 / 21:39
How to do a select in date format? Laravel 5.2 $ errors empty