Query with variable date in Mysql returning empty

Asked

Viewed 332 times

2

I need to make a query in Mysql setting 3 variables,when only in the case of right integer,more when I include empty return date ta.

Follows 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

Follow the result of the query, 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
mysql sql query datetime

1 answer

2


I don’t know if you have other problems, but you should put the date in quotes. The way he was doing the account 2016 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 make the following test:

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

I put in the Github for future reference.

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

I don’t think it’s ideal in every situation, but I could do the query as in that Sqlfiddle.

Browser other questions tagged mysql sql query datetime

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