Error in saving dates

Asked

Viewed 193 times

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 );
php mysqli

  • Already tried to enter a date manually?

  • Yes @Khaosdoctor, on option insert phpmyadmin add a new line and recorded a certain.

  • It shows the SQL query?

  • The format date receives the values as YYYY-MM-DD in the form of a string. You’re inserting like,: '2014-10-29', that is, with plicas ?

  • @Zuul didn’t get it, what’s wrong? It should be like VARCHAR the column base_dataos?

  • @Khaosdoctor, I supplemented my question with your question

  • 1

    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, 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 format 11/12/2014 // MÊS/DIA/ANO cell loads with format General returning something like 41770.67140046296 hence the dates are recording wrong. The question now is how to import the spreadsheet in format TEXT

  • @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, then.. I opened one POST with that doubt

Show 5 more comments

2 answers

1

0

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

I needed to capture a completed date in a form in DD/MM/YYYY format and turn it into YYYY-MM-DD format and insert it into the seat. When entering into the bank, some gave problem. So, to solve, I captured the date of the form and fixed with a single line:

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

  • Thanks for your post @Abbominavell

Browser other questions tagged php mysqli

You are not signed in. Login or sign up in order to post.