Error writing dates [duplicate]

1

Following in the line 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));
}

Output:

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 have one more problem, when writing to the DB using this function some dates (not all) look like this:

0000-00-00
4537-11-13
7963-11-13
2014-10-29

The structure looks like this:

Nome       Tipo Colação Atributos   Nulo    Padrão                    Extra
base_dataos  date                  Não  Nenhum wrap (padrão: none)  

Manually entering phpmyadmin the date records correctly, followed 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 );
    
asked by anonymous 13.11.2014 / 14:18

2 answers

1

The problem was in importing the worksheet, using PHPExcel the dates in format MM/DD/YYYY returned a value like 41770.67140046296 a solution is in the question: Import spreadsheet in text format using PHPExcel

    
18.11.2014 / 15:33
0

I went through a problem very similar to yours. Searching a little I found the following simple solution that solved my problem.

I needed to capture a filled date on a form in DD / MM / YYYY format and turn it into YYYY-MM-DD format and insert it into the bank. When inserting in the bank, some gave problem. So to fix it, I captured the form date and corrected it with a single line:

$data = $_POST['data'];
$data_corrigida = implode("-",array_reverse(explode("/",$data)));
    
18.11.2014 / 14:12