curdate mysql无法正常工作 [英] Curdate mysql not working function

问题描述

需要今天的报告



当没有此行的代码时

Need report for today

When put code without this line

#where p.VrijemePrijave = CURDATE()

工作正常



当这一行（今天的报告）结果为空时



我尝试过：

Working fine

When put this line (Report for today) result is empty

What I have tried:

SELECT o.Ime, 
sum(case when (TIME(p.VrijemePrijave) between '13:00' and '22:00') and (TIME(p.VrijemeOdjave) between '00:00' and '04:00')  then (SELECT FLOOR((TIME_TO_SEC(TIMEDIFF('22:00', DATE_FORMAT(p.VrijemePrijave,'%H:%i:%s')))) / 3600))  else case when (TIME(p.VrijemePrijave) between '17:00' and '22:00') and (TIME(p.VrijemeOdjave) between '00:00' and '15:00')  then 0 else (case when WEEKDAY(VrijemePrijave) in (5, 6) then 0 else (CASE WHEN(SELECT FLOOR((TIME_TO_SEC(TIMEDIFF(p.VrijemeOdjave, p.VrijemePrijave))) / 3600)) > 8 THEN 8 else (SELECT FLOOR((TIME_TO_SEC(TIMEDIFF(p.VrijemeOdjave, p.VrijemePrijave))) / 3600)) END) end) end end ) AS 'SATI [h]',   
sum(case when (TIME(p.VrijemePrijave) between '13:00' and '22:00') and (TIME(p.VrijemeOdjave) between '00:00' and '04:00') then (SELECT FLOOR((TIME_TO_SEC(TIMEDIFF(DATE_FORMAT(p.VrijemeOdjave,'%H:%i:%s'), '06:00'))) / 3600)) else case when (TIME(p.VrijemePrijave) between '17:00' and '22:00') and (TIME(p.VrijemeOdjave) between '00:00' and '15:00') then (SELECT FLOOR((TIME_TO_SEC(TIMEDIFF(DATE_FORMAT(p.VrijemeOdjave,'%H:%i:%s'), '06:00'))) / 3600)) + (SELECT FLOOR((TIME_TO_SEC(TIMEDIFF('22:00', DATE_FORMAT(p.VrijemePrijave,'%H:%i:%s')))) / 3600)) else  (case when WEEKDAY(VrijemePrijave) in (5, 6) then(SELECT FLOOR((TIME_TO_SEC(TIMEDIFF(p.VrijemeOdjave, p.VrijemePrijave))) / 3600)) else (CASE WHEN(SELECT FLOOR((TIME_TO_SEC(TIMEDIFF(p.VrijemeOdjave, p.VrijemePrijave))) / 3600)) > 8 THEN((SELECT FLOOR((TIME_TO_SEC(TIMEDIFF(p.VrijemeOdjave, p.VrijemePrijave))) / 3600)) - 8) else NULL END) end) end end) AS 'PREKOVREMENO [h]',   
sum(case when (TIME(p.VrijemePrijave) between '13:00' and '22:00') and (TIME(p.VrijemeOdjave) between '00:00' and '12:00')  then (SELECT CASE WHEN TIME(p.VrijemeOdjave) between '06:00' and '06:59' THEN 8 ELSE (SELECT FLOOR((TIME_TO_SEC(TIMEDIFF(DATE_FORMAT(p.VrijemeOdjave,'%H:%i:%s'), '00:00'))) / 3600))+2 END) else case when (TIME(p.VrijemePrijave) between '17:00' and '22:00') and (TIME(p.VrijemeOdjave) between '00:00' and '15:00')  then (SELECT CASE WHEN TIME(p.VrijemeOdjave) between '06:00' and '06:59' THEN (SELECT FLOOR((TIME_TO_SEC(TIMEDIFF(DATE_FORMAT(p.VrijemeOdjave,'%H:%i:%s'), '00:00'))) / 3600))+2 ELSE 8 END) else null end end) as 'NOCNI RAD'
from prijava_radnika p inner
join osoba o on o.IdOsoba = p.IdPosjetioc
#where p.VrijemePrijave = CURDATE() 
group by o.Ime 
order by o.Ime; 

推荐答案

基本上，从数据开始。检查 prijava_radnika.VrijemePrijave 中的值，并确切地看到你在那里的值 - 请记住，等于日期要求日期相同，直到勾选，所以今天正午今天12：00：00：00001不匹配，CURDATE根据上下文返回一个字符串/数字，需要与你比较的值完全匹配。



对不起，但我们不能为你做到这一点！

Basically, start with your data. Check the values in prijava_radnika.VrijemePrijave and see exactly what values you have in there - remember that equals for dates requires the dates to be identical, right to the tick, so midday today will not match 12:00:00:00001 today, and CURDATE returns a string / number based on context which needs to exactly match the values you are comparing.

Sorry, but we can't do that for you!

这篇关于curdate mysql无法正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！