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
Yong Scott Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Yong Scott Author Commented:
database format can't be change ? just to display ?
0
Yong Scott 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
Yong Scott 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 ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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
Yong Scott 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 ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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
Yong Scott 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
Yong Scott 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 ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
hence, try this instead:

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

Open in new window

1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Yong Scott 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
Yong Scott 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
Yong Scott Author Commented:
Thank this work
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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 ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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
Yong Scott 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
Yong Scott 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 ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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
Yong Scott 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 ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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
Yong Scott Author Commented:
ok, so what's next need to be done?
Its done thank ..
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.