Change date_format but nothing display

$sql = "UPDATE nov set date = date_format(f_name,'%M %d %Y')";
if ($db->query($sql) === TRUE) {
    echo "  ";
}

Open in new window


output : all 0000-00-00
Scott Yong Asked:
Who is Participating?
 
Ryan ChongCommented:
hence, try this instead:

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

Open in new window

1
 
Julian HansenCommented:
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.
0
 
Jim RiddlesPrepress/OMS SpecialistCommented:
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"?
1
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Dave BaldwinFixer of ProblemsCommented:
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.
0
 
Scott Yong Author Commented:
database format can't be change ? just to display ?
0
 
Scott Yong Author Commented:
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
0
 
Scott Yong Author Commented:
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
0
 
Ryan ChongCommented:
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??
0
 
Scott Yong Author Commented:
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 ..
0
 
Ryan ChongCommented:
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') ";

Open in new window

1
 
Scott Yong Author Commented:
i think you should use function: str_to_date instead of date_format.
I try first this coding ..
0
 
Dave BaldwinFixer of ProblemsCommented:
I would use the 'standard' format of YYYY-MM-DD because it will sort properly as a string.  All the other formats don't.
0
 
Scott Yong Author Commented:
$f_name = "11-01-17";
$sql = "UPDATE nov set date = str_to_date('$f_name','%m-%d-%y') ";

Its not matching the result

The output didnt match
0
 
Ryan ChongCommented:
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".
0
 
Scott Yong Author Commented:
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 "  ";
                }
            }

Open in new window


I do it at upload.php
0
 
Scott Yong Author Commented:
$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
0
 
Scott Yong Author Commented:
Thank this work
0
 
Ryan ChongCommented:
i guess you need:

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

Open in new window

1
 
Ryan ChongCommented:
to do the tranformation at mysql end, use:
 $sql = "UPDATE nov set date = str_to_date(left(f_name, 8),'%m-%d-%y') ";

Open 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') ";

Open 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.
1
 
Scott Yong Author Commented:
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 ~
0
 
Scott Yong Author Commented:
$sql = "UPDATE nov set date = str_to_date('".substr($value,0,8)."','%m-%d-%y') ";
actually i just tried this coding its not working
0
 
Ryan ChongCommented:
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>";

Open in new window


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

Open in new window


which should worked by updating date to 1 Nov 17
1
 
Scott Yong Author Commented:
$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
0
 
Ryan ChongCommented:
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?
1
 
Scott Yong Author Commented:
ok, so what's next need to be done?
Its done thank ..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.