1
Following the line of Validate different date formats
The function below has met my needs:
function validateDate( $date ){
$europeu = date('Y-m-d', strtotime(current(explode(' ', trim($date)))));
$brazil = date('Y-m-d', strtotime(str_replace('/', '-', current(explode(' ', trim($date))))));
return ( $europeu != '1970-01-01' ? $europeu : ( $brazil != '1970-01-01' ? $brazil : null));
}
Exit:
validateDate('11/12/2014'); // MÊS/DIA/ANO -> 2014-11-12 OK
validateDate('31/10/2014'); // DIA/MÊS/ANO -> 2014-10-31 OK
Now I come across another problem, when recording in DB using this function some dates (not all) are like this:
0000-00-00
4537-11-13
7963-11-13
2014-10-29
The structure is like this:
Nome Tipo Colação Atributos Nulo Padrão Extra
base_dataos date Não Nenhum wrap (padrão: none)
Inserting manually by phpmyadmin date writes correctly, follow query generated
INSERT INTO `banco`.`tabela` (`base_id`, `base_posto`, `base_numeroos`, `base_numerosr`, `base_modelo`, `base_garantia`, `base_codays`, `base_codphi`, `base_dataos`, `base_full`, `base_upload`, `base_user`) VALUES (NULL, 'EMPRESA TESTE', '99999999999', '88888888888', 'NTRX', 'Na garantia', '33333', 'ap1212', '2014-11-13', 'N', CURRENT_TIMESTAMP, 'Admin');
The query used in PHP
// dados pego de um arquivo excel
$data_os = validateDate( $data[5] );
$mysqli->query("INSERT INTO `tablea` (`base_posto`, `base_numeroos`, `base_numerosr`, `base_modelo`, `base_garantia`, `base_codays`, `base_codphi`, `base_dataos`, `base_user`)
VALUES ('".$posto."','".$numero_os."','".$numero_sr."','".$modelo."','".$garantia."','".$codigo_posto_ays."','".$codigo_posto_phi."','".$data_os."', '".$userLogin."')")
or die ( $mysqli->error );
Already tried to enter a date manually?
– KhaosDoctor
Yes @Khaosdoctor, on option insert phpmyadmin add a new line and recorded a certain.
– smigol
It shows the SQL query?
– KhaosDoctor
The format
date
receives the values asYYYY-MM-DD
in the form of a string. You’re inserting like,:'2014-10-29'
, that is, with plicas ?– Zuul
@Zuul didn’t get it, what’s wrong? It should be like
VARCHAR
the column base_dataos?– smigol
@Khaosdoctor, I supplemented my question with your question
– smigol
I’ve seen by editing the question that you’re using quotes to save the date in the database. If you do
var_dump($data_os);
before the object$mysqli
what result you get?– Zuul
@Zuul, I went to check if I was importing the data correctly as you suggested
var_dump
and I found the OR. I’m using the class Phpexcel to import data from a spreadsheet and save to DB. vi that dates in format11/12/2014 // MÊS/DIA/ANO
cell loads with format General returning something like41770.67140046296
hence the dates are recording wrong. The question now is how to import the spreadsheet in format TEXT– smigol
@Hugo You will need to find the dates to be able to convert into days again. Maybe take the actual values of the cells?
– KhaosDoctor
@Khaosdoctor, then.. I opened one POST with that doubt
– smigol