Jump to content

Sql without 000-00-00 date


gmc1103
Go to solution Solved by benanamen,

Recommended Posts

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

 

Link to comment
Share on other sites

  • Solution

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 by benanamen
Link to comment
Share on other sites

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

Link to comment
Share on other sites

@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 by benanamen
Link to comment
Share on other sites

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 by benanamen
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.