2. PHPRunner General questions
  3. convert varchar field to date field and update field convert in a new date field
This topic is locked

convert varchar field to date field and update field convert in a new date field

 		6/30/2023 10:18:56 AM
PHPRunner General questions
konenamogo author

Hi,
I want to update date field.
first i have varchar field "Date_Facture" and i want to update date field "Date_Facture_N" after converting varchar field to date field. My code is :
<?php
global $conn;
$DateDuJour = date('d/m/Y');
$recupSiecle = substr($DateDuJour,6,2);
$century = $recupSiecle;
$rsPrincipal = DB::Query("SELECT * FROM exportcomptablemmr WHERE Id is not null",$conn);
WHILE ( $datePrincipal = $rsPrincipal->fetchAssoc()) {
$DateEnregistrement = now();
//Recup key field

$Id = $datePrincipal['Id'];
$DetailFacture = $datePrincipal['DetailFacture'];
$NumFacture = $datePrincipal['NumFacture'];
// convertion and update my date variable Date_Facture_N in phpmysql
//$Date_Facture = $datePrincipal['Date_Facture'];
$Date_Facture = "291220";
//recup jour1 du champ varchar "Date_Facture"
$jour = substr($Date_Facture,0,2);
$jour = (int)$jour;
//recup Mois1 du champ varchar "Date_Facture"
$Mois = substr($Date_Facture,2,2);
$Mois = (int)$Mois;
//recup Annee1 du champ varchar "Date_Facture"
$Annee = substr($Date_Facture,4,2);
$Annee = $century.$Annee;
$Date1 = $Mois.'/'.$jour.'/'.$Annee;
$time = strtotime($Date1);
$newformat = date('d/m/Y',$time);

$Date_Facture_N = $newformat;
// UPDATE
$update1 = "UPDATE exportcomptablemmr
SET DateEnregistrement = '$DateEnregistrement',
StatutLigne = '$StatutLigne',
Date_Facture_N = '$Date_Facture_N'
WHERE Id = '$Id' AND DetailFacture = '$DetailFacture' AND NumFacture ='$NumFacture' ";
DB::Exec($update1);
}
?>
After Update, the date is not display. I have 00/00/000 in my field date insteed of 29/12/2020. How can i do for updating yhe field Date ?
Sergey Kornilov admin 6/30/2023

I'm sorry to ask, but how do this apply to PHPRunner?

konenamogo author 6/30/2023

Hi,
I have a file extracted from sql (extracted in .xlsx) whose date field has in the text format '291220' tinstead of '29/12/2020'.
I would like to do an integration of the .xlsx file in phprunner by mapping.
For this, I created a Date field (DATE_T_N) from mysql in order to update the initial format '291220' to be transformed into Date format in order to update this format in
the field (DATE_T_N) and make other treatment.
version of PRHRUNNER : 10.8
konenamogo author 7/10/2023

Hello,
I find solution.
//**
//--DEBUT NKO AU 09-07-2023---CONVERTION EN DATE AU FORMAT DATE ET MISE A JOUR
//**
` $rsPrincipa111 = DB::Query("SELECT CAST(CONCAT(SUBSTRING(Date_Facture ,5 ,2 ) , SUBSTRING(Date_Facture ,3 ,2 ) , SUBSTRING(Date_Facture ,1 ,2 ) ) AS DATE ) AS Date_Facture_N,
Id, CAST(CONCAT(SUBSTRING(Date_T ,5 ,2 ) , SUBSTRING(Date_T ,3 ,2 ) , SUBSTRING(Date_T ,1 ,2 ) ) AS DATE ) AS Date_T_N,
CAST(CONCAT(SUBSTRING(Date_D ,5 ,2 ) , SUBSTRING(Date_D ,3 ,2 ) , SUBSTRING(Date_D ,1 ,2 ) ) AS DATE ) AS Date_D_N
FROM exportcomptablemmr ",$conn);
WHILE ( $datePrincipa111 = $rsPrincipa111->fetchAssoc())
{
// Recupération des valeurs
$Id111 = $datePrincipa111['Id'];
$Date_Facture_N111 = $datePrincipa111['Date_Facture_N'];
$Date_T_N111 = $datePrincipa111['Date_T_N'];
$Date_D_N111 = $datePrincipa111['Date_D_N'];
$rsPrincipa112 = DB::Query("SELECT *
FROM exportcomptablemmr
WHERE Id = '$Id111' ",$conn);
WHILE ( $datePrincipa112 = $rsPrincipa112->fetchAssoc())
{
$Date_Facture_N111 = $datePrincipa111['Date_Facture_N'];
$Date_T_N111 = $datePrincipa111['Date_T_N'];
$Date_D_N111 = $datePrincipa111['Date_D_N'];

$Id112 = $datePrincipa112['Id'];
// Mise à jour
$update112 = "UPDATE exportcomptablemmr
SET Date_Facture_N = '$Date_Facture_N111',
Date_T_N = '$Date_T_N111',
Date_D_N = '$Date_D_N111'
WHERE Id = $Id112 ";
DB::Exec($update112);
}
}
//**
//--FIN NKO AU 09-07-2023---CONVERTION EN DATE AU FORMAT DATE ET MISE A JOUR
//**`