gmc1103 Posted October 27, 2015 Share Posted October 27, 2015 Hi I'm having a problem with this query, it was good until the user starting to put 0000-00-00 SELECT t1.`id_plano`, t1.`atividade`, t1.`data_prevista` FROM `new_pae` AS t1 WHERE t1.`id_user` = '$idUser' AND t1.`data_prevista` <= CURDATE() AND (t1.`realizado` IS NULL OR LENGTH(t1.`realizado` ) =0) Now i trying to modfy this query to avoid this kind of date but i don't get it. This is an example how it comes id_plano atividade data_prevista -------- ------------------------------------------- --------------- 168 ''O Eco-Clube Informa'' 0000-00-00 169 Campanhas de recolha de materiais 0000-00-00 170 Campanha de recolha de ROUPAS E CALÇADO 0000-00-00 181 ''Pour un noel écolo!'' 2015-10-20 184 ''Pour un noel écolo!'' 0000-00-00 185 Visita de Estudo a uma Produção Agrícola 0000-00-00 186 Decorações de Natal 0000-00-00 187 Exposição/Concurso 0000-00-00 188 ECO-CRIATIVO 0000-00-00 189 Dia ECO-ESCOLA 0000-00-00 190 Exposição 0000-00-00 191 Workshop's 0000-00-00 192 Exposição 0000-00-00 193 Criação/construção de cenários 0000-00-00 The date 0000-00-00 is set when the user don't know when he gonna make that activity. Any help Thanks Quote Link to comment https://forums.phpfreaks.com/topic/298869-sql-without-000-00-00-date/ Share on other sites More sharing options...
Solution benanamen Posted October 27, 2015 Solution Share Posted October 27, 2015 (edited) Add this to your query AND t1.`data_prevista` <> '0000-00-00' SELECT t1.`id_plano`, t1.`atividade`, t1.`data_prevista` FROM `new_pae` AS t1 WHERE t1.`id_user` = '$idUser' AND t1.`data_prevista` <= Curdate() AND t1.`data_prevista` <> '0000-00-00' AND ( t1.`realizado` IS NULL OR Length(t1.`realizado`) = 0 ) Edited October 27, 2015 by benanamen Quote Link to comment https://forums.phpfreaks.com/topic/298869-sql-without-000-00-00-date/#findComment-1524499 Share on other sites More sharing options...
gmc1103 Posted October 27, 2015 Author Share Posted October 27, 2015 Thanks for helping me Ok, but now i have those dates 0000-00-00 , how can i avoid them in select query? Quote Link to comment https://forums.phpfreaks.com/topic/298869-sql-without-000-00-00-date/#findComment-1524501 Share on other sites More sharing options...
benanamen Posted October 27, 2015 Share Posted October 27, 2015 I just gave you the answer. Quote Link to comment https://forums.phpfreaks.com/topic/298869-sql-without-000-00-00-date/#findComment-1524502 Share on other sites More sharing options...
Barand Posted October 27, 2015 Share Posted October 27, 2015 If you still want the records but not show the date when it is 0000-00-00 then SELECT t1.`id_plano` , t1.`atividade` , NULLIF(t1.`data_prevista`, 0) as `data_prevista FROM `new_pae` AS t1 WHERE t1.`id_user` = '$idUser' AND t1.`data_prevista` <= CURDATE() AND (t1.`realizado` IS NULL OR LENGTH(t1.`realizado` ) =0) or just insert null dates instead of 0000-00-00 from the outset Quote Link to comment https://forums.phpfreaks.com/topic/298869-sql-without-000-00-00-date/#findComment-1524503 Share on other sites More sharing options...
benanamen Posted October 27, 2015 Share Posted October 27, 2015 (edited) @Barand, That is not going to work for what the OP wants. He is still going to return records he doesnt want. They will just have null for data_prevista instead of the 000-00-00 He is asking for records only WHERE data_prevista <= CURDATE() (Plus the other where conditions) Edited October 27, 2015 by benanamen Quote Link to comment https://forums.phpfreaks.com/topic/298869-sql-without-000-00-00-date/#findComment-1524508 Share on other sites More sharing options...
gmc1103 Posted October 27, 2015 Author Share Posted October 27, 2015 Thank you both of you. Learning everyday with you guys. Best regards Quote Link to comment https://forums.phpfreaks.com/topic/298869-sql-without-000-00-00-date/#findComment-1524512 Share on other sites More sharing options...
benanamen Posted October 27, 2015 Share Posted October 27, 2015 (edited) just insert null dates instead of 0000-00-00 from the outset @Barand has made a good suggestion here. It is actually what I was going to tell you but since you already had data I just gave you what you needed. If you implement what @Barand said you can easily do an update query to set all the 000-00-00 to NULL which is really the "right" way to do this. Edited October 27, 2015 by benanamen Quote Link to comment https://forums.phpfreaks.com/topic/298869-sql-without-000-00-00-date/#findComment-1524514 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.