3
Good morning, you guys.
I have in my database DATE fields for 'start date' and 'end date', so I can search for ranges. When I imported this data some dates were empty or inconform and were not logged correctly.
I would like to know: there is some way for me to select the 'end date' fields of my database and where it is empty and make it equal to 'start date' + 30(days), doing it by mysql itself or I will have to use php?
Thanks!
3
You can use a UPDATE to update the final date field when it is NULL.
Thus:
UPDATE minha_tabela
SET data_final = DATE_ADD(data_inicial ,INTERVAL 30 DAY)
WHERE data_final IS NULL;
This directly in the bank, without needing an application.
About function DATE_ADD:
https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-add
The solution is to update the field? And when it needs to know which fields were imported with error it will do what?
Friend, I may have misunderstood, but if he wants to correct the import data use the UPDATE. If he just wants to get around the problem when reading the data, then use the COALESCE as you suggested. I believe I understood one thing and you another. Only the author can clarify what he would really like to do.
Emerson, it worked perfectly! It was exactly what I needed. I didn’t know this date_add() function. What I did was: UPDATE box SET data_fin = DATE_ADD(data_ini ,INTERVAL 30 DAY) WHERE data_fin is NULL and data_ini is not null; .
I’m glad it worked. I updated the response with a link to the DATE_ADD, in case you want to know more.
Thank you very much!
0
You can do it directly through the database and without having to change the data in your table.
Use the function coalesce. It tries to get the leftmost value of the expression and if it is null the value is returned to the right.
SELECT DATA_INICIAL, COALESCE(DATA_FINAL, DATE_ADD(DATA_INICIAL, INTERVAL 30 DAY))
FROM TABELA
WHERE CONDICAO
Browser other questions tagged php mysql date
You are not signed in. Login or sign up in order to post.
When you say "empty" you mean the null field, right?
– user48732
Yes, the field was empty in the spreadsheet and entered the bank as NULL.
– Chiwyrow Cortin