结果是在减去两个变量时生成零

Question

SELECT DAYOFWEEK('2018/08/15');
SET @STARTDATE = '2017/08/15';
SET @ENDDATE = '2018/08/15';
  #1->part1

 SET @SEC_IN_START_DATE = UNIX_TIMESTAMP(@STARTDATE);
 SET @SEC_IN_END_DATE = UNIX_TIMESTAMP(@ENDDATE);
 SELECT @NUM_OF_DAYS = (@SEC_IN_END_DATE - @SEC_IN_START_DATE);
 SELECT @NUM_OF_WEEKS = (@NUM_OF_DAYS%7);  
 SELECT UNIX_TIMESTAMP(@ENDDATE);  
 #part-2
 SELECT 
     ROUND((
          (unix_timestamp(@END_DATE) - unix_timestamp(@START_DATE) ) 
          /(24*60*60)
              -7+WEEKDAY(@START_DATE)-WEEKDAY(@END_DATE)
          )/7);

执行时上面显示的所有零件都给出值nullthe图像显示第2部分＆amp;的执行。图显示了第1部分的执行但是两者都给出了无效，请提供帮助。谢谢您的帮助。

Answer 1

可能是您正在在字符串类型数据上进行操作，如果将它们转换为日期时间datatype

Answer 2

您的变量声明与用法不同，请在下面更新您的查询。

SELECT 
     ROUND((
          (unix_timestamp(@ENDDATE) - unix_timestamp(@STARTDATE) ) 
          /(24*60*60)
              -7+WEEKDAY(@STARTDATE)-WEEKDAY(@ENDDATE)
          )/7);

o/p：

 SELECT       ROUND((           (unix_timestamp(@ENDDATE) - unix_timestamp(@STARTDATE) )            /(24*60*60)               -7+WEEKDAY(@STARTDATE)-WEEKDAY(@ENDDATE)           )/7); +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ROUND((           (unix_timestamp(@ENDDATE) - unix_timestamp(@STARTDATE) )            /(24*60*60)               -7+WEEKDAY(@STARTDATE)-WEEKDAY(@ENDDATE)           )/7) |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                                                                                                                      51 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+