Change date_format but nothing display

Why are you trying to format the date in SQL - store it in the native DB format and format it when you pull it out of the database - that is the right way to do it.

Additionally, without seeing all of the code, I wonder if the SQL statement in line one is correct.  It looks like you are trying to format the date with a variable of "f_name".  Assuming that f_name is correct, should that not at least be "$f_name"?

I believe that "date_format" only works when you read a date from the database.  Use the standard format to put it in a 'date' field.

database format can't be change ? just to display ?

f_name actually is my variable for detect file name and i want to change f_name into date .. but my file name format date start with mm/dd/yy

I want convert string file name that consist the date to date with format(mm/dd/yy) because example of my file name is 11-01-17 VM Configuration.csv

but my file name format date start with mm/dd/yy

because example of my file name is 11-01-17 VM Configuration.csv

so i assume 11-01-17 means 1 Nov 17?

and you wish to save 1 Nov 17 into field: date ? what's the data type of field: date??

so i assume 11-01-17 means 1 Nov 17?

yes yes ..

and you wish to save 1 Nov 17 into field: date ? what's the data type of field: date??

yes save into date .. data type of field is date ..

i think you should use function: str_to_date instead of date_format.
like:

$f_name = "11-01-17";
$sql = "UPDATE nov set date = str_to_date('$f_name','%m-%d-%y') ";

Select allOpen in new window

i think you should use function: str_to_date instead of date_format.

I try first this coding ..

I would use the 'standard' format of YYYY-MM-DD because it will sort properly as a string.  All the other formats don't.

$f_name = "11-01-17";
$sql = "UPDATE nov set date = str_to_date('$f_name','%m-%d-%y') ";

The output didnt match

f_name actually is my variable for detect file name and i want to change f_name into date .. but my file name format date start with mm/dd/yy

so f_name is referred to a field in your table! not really a "variable".

so f_name is referred to a field in your table! not really a "variable".

because i got many php file .. another variable is in my upload.php ..  this one i show  was config.php ..

 foreach($results_array as $value)
  {
 $csvMimes = array('text/x-comma-separated-values', 'text/comma-separated-values', 'application/octet-stream', 'application/vnd.ms-excel', 'application/x-csv', 'text/x-csv', 'text/csv', 'application/csv', 'application/excel', 'application/vnd.msexcel', 'text/plain');
    if(!empty($_FILES['file']['name']) && in_array($_FILES['file']['type'],$csvMimes)){
        if(is_uploaded_file($_FILES['file']['tmp_name'])){
            
            //open uploaded csv file with read only mode
            $csvFile = fopen($_FILES['file']['tmp_name'], 'r');
            
            //skip first line
            fgetcsv($csvFile);
            
            //parse data from csv file line by line
            while(($line = fgetcsv($csvFile)) !== FALSE){
                 //insert member data into database
                    $db->query("INSERT INTO nov(f_name,name, cpu_count, memory_size, disk_space_size, nic_count, power_state) VALUES ('".$value."','".$line[0]."','".$line[1]."','".$line[2]."','".$line[3]."','".$line[4]."','".$line[5]."')");
					
if ($db->query($sql) === TRUE) {
    echo "  ";
}
			$sql = "UPDATE nov set date = str_to_date('$value','%m-%d-%y') ";
			if ($db->query($sql) === TRUE) {
    echo "  ";
                }
            }

Select allOpen in new window

I do it at upload.php

$sql = "UPDATE nov set date = str_to_date(left(f_name, 8),'%m-%d-%y') ";

I try this one first .. if can i use this one

Thank this work

i guess you need:

$sql = "UPDATE nov set date = str_to_date('".substr($value,0,8)."','%m-%d-%y') ";

Select allOpen in new window

to do the tranformation at mysql end, use:

 $sql = "UPDATE nov set date = str_to_date(left(f_name, 8),'%m-%d-%y') ";

Select allOpen in new window

to do the tranformation at php end, use:

$sql = "UPDATE nov set date = str_to_date('".substr($value,0,8)."','%m-%d-%y') ";

Select allOpen in new window

just a note that you're updating field: date in table Nov, without additional condition. you may consider if this logic is correct.

just a note that you're updating field: date in table Nov, without additional condition. you may consider if this logic is correct.

Thank for the note ~

$sql = "UPDATE nov set date = str_to_date('".substr($value,0,8)."','%m-%d-%y') ";

actually i just tried this coding its not working

you get:

$value = "11-01-17 VM Configuration Summary List View Report...";
$sql = "UPDATE nov set date = str_to_date('".substr($value,0,8)."','%m-%d-%y') ";
echo $sql."<br>";

Select allOpen in new window

output:

UPDATE nov set date = str_to_date('11-01-17','%m-%d-%y') 

Select allOpen in new window

which should worked by updating date to 1 Nov 17

$sql = "UPDATE nov set date = str_to_date(left(f_name, 8),'%m-%d-%y') ";

This one work actually in my config.php .. i just tried in my upload.php file .. its okay i just used in config.php

$value = "11-01-17 VM Configuration Summary List View Report...";
$sql = "UPDATE nov set date = str_to_date('".substr($value,0,8)."','%m-%d-%y') ";
echo $sql."<br>";

sorry to tell that i was using automatic detect file name .. so i cannot set the $value value

This one work actually in my config.php .. i just tried in my upload.php file .. its okay i just used in config.php

ok, so what's next need to be done?

sorry to tell that i was using automatic detect file name .. so i cannot set the $value value

what do you mean by "using automatic detect file name"? isn't it that $value was being assigned in your codes and saved to f_name in database?

ok, so what's next need to be done?

Its done thank ..