how to change the date format

Hi,

I want to get the departure date from the user and store it in the database. I am using a calender code to enable them to choose date(written in java script).
the format is Nov-13-2008 in the calender. When i try inserting this value mysql puts the default value
'0000-00-00'.

The problem is mysql server takes the format yyyy-mm-dd.
when i give the date as 2008-11-13, the value is getting inserted.
I tried giving 13-11-2008 but that did not work. Only the default value 0000-00-00 is geting stored.

how do i change the format?
whspiderAsked:
Who is Participating?
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.

Loganathan NatarajanLAMP DeveloperCommented:
you cannot change the mysql date field type as you wish. but you can process your date with mysql date format, use split/explode function manipulate and insert it...
Loganathan NatarajanLAMP DeveloperCommented:
you can convert your user input date into mysql format date... and can be displayed as you wish....
UmeshSenior Principal Technical Support EngineerCommented:
As said in prev comment better make use of in-built function to convert the date and store as-is in MySQL compatible..later while displaying you can show whatsoever the format required..

At the time of insertion... say input is 'Nov-13-2008' and you want to make it MySQL compatible then make use of below function..

STR_TO_DATE('Nov-13-2008','%b-%d-%Y')

this should convert it as "2008-11-13"

Likewise when pulling

select DATE_FORMAT('2008-11-13','%b-%d-%Y');
OUTPUT
Nov-13-2008

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
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

whspiderAuthor Commented:
Thank you very much. Am able to convert and  insert the date.....

now how do i retrieve through php...as am retrieving name,adress also along with date.....

I gave select * and displayed initially. Now that i have converted and stored in mysql, where and how shd i use this DATE_FORMAT.Pls help as am new to both php and mysql.

For ex:

$sql1="SELECT * FROM $tbl_name1;
$result1=mysql_query($sql1);
$rows1=mysql_fetch_array($result1);
?>

   <table width="100%">
        <tbody>
            <tr>
                <td bgcolor="#c10000" colspan="4">
                <p align="center"><font color="#ffffff"><b>PROFILE DETAILS</b></font></p>
                </td>
            </tr>            
            <tr>
                <td bgcolor="#99ccff"><b>&nbsp;name </b></td>
                <td bgcolor="#99ccff"><? echo $rows1['name']; ?></td>
                <td bgcolor="#99ccff"><b>&nbsp;addresss</b></td>
                <td bgcolor="#99ccff"><? echo $rows1['addr']; ?></td>
                <td bgcolor="#99ccff"><b>&nbsp;D.O.J </b></td>
                <td bgcolor="#99ccff"><? echo $rows1['doj']; ?></td>              
            </tr>
</tbody>
</table>

D.O.J is in the format 2008-11-13 in the mysql table
UmeshSenior Principal Technical Support EngineerCommented:
Try this...




$sql1="SELECT name,addr, DATE_FORMAT(doj,'%b-%d-%Y')AS doj FROM $tbl_name1";
$result1=mysql_query($sql1);
$rows1=mysql_fetch_array($result1);
?>
 
   <table width="100%">
        <tbody>
            <tr>
                <td bgcolor="#c10000" colspan="4">
                <p align="center"><font color="#ffffff"><b>PROFILE DETAILS</b></font></p>
                </td>
            </tr>            
            <tr>
                <td bgcolor="#99ccff"><b>&nbsp;name </b></td>
                <td bgcolor="#99ccff"><? echo $rows1['name']; ?></td>
                <td bgcolor="#99ccff"><b>&nbsp;addresss</b></td>
                <td bgcolor="#99ccff"><? echo $rows1['addr']; ?></td>
                <td bgcolor="#99ccff"><b>&nbsp;D.O.J </b></td>
                <td bgcolor="#99ccff"><? echo $rows1['doj']; ?></td>              
            </tr>
</tbody>
</table>

Open in new window

whspiderAuthor Commented:
Thank you so much its working.....
whspiderAuthor Commented:
thank you
UmeshSenior Principal Technical Support EngineerCommented:
Anytime..

Thanks,
Umesh
whspiderAuthor Commented:
I have one more issue
am able to update and retrieve.
am not able to insert when i use the following code. I think am goin wrong in query

$name=$_GET['name'];
        $fname=$_GET['fname'];
        $st=$_GET['st'];
        $city=$_GET['city'];
        $state=$_GET['state'];
        $pin=$_GET['pin'];
        $phno=$_GET['phno'];
        $dob=$_GET['dob'];
        $doj=$_GET['doj'];
        $gender=$_GET['gender'];
        $age=$_GET['age'];
        $ms=$_GET['ms'];
        $email=$_GET['email'];
        $type=$_GET['type'];
        $sum=$_GET['sum'];
        $mp=$_GET['mp'];
        $pt=$_GET['pt'];
        $mt=$_GET['mt'];
        $issue=$_GET['issue'];
        $nom=$_GET['nom'];
        $rel=$_GET['rel'];
        $incode=$_GET['incode'];
        $inrank=$_GET['inrank'];
        $chkcode=$_GET['incode'];
        $count=2;
                                                 
// Insert data into mysql


$sql="INSERT INTO $tbl_name5(name, fname, st, city, state, pin, phno, doj, gender, age, ms, email, dob, plan, mode, modamt, tamount, term, issuedate, nominee, relation, introcode, introrank, count, paid)VALUES('$name', '$fname', '$st', '$city', '$state', '$pin', '$phno', 'STR_TO_DATE('$doj','%b-%d-%Y')', '$gender', '$age', '$ms', '$email', STR_TO_DATE('$doj','%b-%d-%Y')', '$type', '$mt', '$mp', '$sum', '$pt', '$issue', '$nom', '$rel', '$incode', '$inrank', '$count', '$mp')";
$result=mysql_query($sql);
UmeshSenior Principal Technical Support EngineerCommented:
This shud work..

$sql="INSERT INTO $tbl_name5(name, fname, st, city, state, pin, phno, doj, gender, age, ms, email, dob, plan, mode, modamt, tamount, term, issuedate, nominee, relation, introcode, introrank, count, paid)VALUES('$name', '$fname', '$st', '$city', '$state', '$pin', '$phno', STR_TO_DATE('$doj','%b-%d-%Y'), '$gender', '$age', '$ms', '$email', STR_TO_DATE('$doj','%b-%d-%Y')', '$type', '$mt', '$mp', '$sum', '$pt', '$issue', '$nom', '$rel', '$incode', '$inrank', '$count', '$mp')";
$result=mysql_query($sql);
whspiderAuthor Commented:
no its not working....
UmeshSenior Principal Technical Support EngineerCommented:
What is the error you are getting???
whspiderAuthor Commented:
am not gettin any errors.the record is getting inserted. All the fields are inserted correctly except for the date value.default value 0000-00-00 is stored. I have set  the default value for date while creating the table
UmeshSenior Principal Technical Support EngineerCommented:
Pls try this... extra single quote was there in query,,, no removed..
$sql="INSERT INTO $tbl_name5(name
                            ,fname
                            ,st
                            ,city
                            ,state
                            ,pin
                            ,phno
                            ,doj
                            ,gender
                            ,age
                            ,ms
                            ,email
                            ,dob
                            ,plan
                            ,mode
                            ,modamt
                            ,tamount
                            ,term
                            ,issuedate
                            ,nominee
                            ,relation
                            ,introcode
                            ,introrank
                            ,count
                            ,paid)
                     VALUES('$name'
                          , '$fname'
                          , '$st'
                          , '$city'
                          , '$state'
                          , '$pin'
                          , '$phno'
                          , STR_TO_DATE('$doj','%b-%d-%Y')
                          , '$gender', '$age', '$ms'
                          , '$email'
                          , STR_TO_DATE('$doj','%b-%d-%Y')
                          , '$type', '$mt', '$mp', '$sum', '$pt', '$issue', '$nom', '$rel', '$incode', '$inrank', '$count', '$mp')";
$result=mysql_query($sql);

Open in new window

whspiderAuthor Commented:
I have tried removing the single quotes but its not workin. I pasted your code also still the default value only is getting stored
UmeshSenior Principal Technical Support EngineerCommented:
Can you print and see what exactly is INSERT Query.. Pls post the output
print "INSERT INTO $tbl_name5(name
                            ,fname
                            ,st
                            ,city
                            ,state
                            ,pin
                            ,phno
                            ,doj
                            ,gender
                            ,age
                            ,ms
                            ,email
                            ,dob
                            ,plan
                            ,mode
                            ,modamt
                            ,tamount
                            ,term
                            ,issuedate
                            ,nominee
                            ,relation
                            ,introcode
                            ,introrank
                            ,count
                            ,paid)
                     VALUES('$name'
                          , '$fname'
                          , '$st'
                          , '$city'
                          , '$state'
                          , '$pin'
                          , '$phno'
                          , STR_TO_DATE('$doj','%b-%d-%Y')
                          , '$gender', '$age', '$ms'
                          , '$email'
                          , STR_TO_DATE('$doj','%b-%d-%Y')
                          , '$type', '$mt', '$mp', '$sum', '$pt', '$issue', '$nom', '$rel', '$incode', '$inrank', '$count', '$mp')";

Open in new window

whspiderAuthor Commented:
this is the output am getting

INSERT INTO customers(name ,fname ,st ,city ,state ,pin ,phno ,doj ,gender ,age ,ms ,email ,dob ,plan ,mode ,modamt ,tamount ,term ,issuedate ,nominee ,relation ,introcode ,introrank ,count ,paid) VALUES('sema' , 'fath' , 'fdg' , 'pondicherry' , 'pyutdy' , '605001' , '232' , STR_TO_DATE('Nov-16-2008','%b-%d-%Y') , 'Male', '21', 'Single' , 'anbarasan.v@gmail.com' , STR_TO_DATE('Nov-15-2008','%b-%d-%Y') , 'slpp1', 'Fixed', '0', '3000', '11year', 'Nov-03-2008', 'ref', 'etr', 'SL2100004', '9\'', '2', '0')
UmeshSenior Principal Technical Support EngineerCommented:
Seems to be some other issue...I just ran below query and it shown the proper dates

select STR_TO_DATE('Nov-16-2008','%b-%d-%Y'),STR_TO_DATE('Nov-15-2008','%b-%d-%Y') from dual;


2008-11-16            2008-11-15


Pls try running above query from commandline or thru any gui client and see what happens
INSERT INTO customers(name ,fname ,st ,city ,state ,pin ,phno ,doj ,gender ,age ,ms ,email ,dob ,plan ,mode ,modamt ,tamount ,term ,issuedate ,nominee ,relation ,introcode ,introrank ,count ,paid) VALUES('sema' , 'fath' , 'fdg' , 'pondicherry' , 'pyutdy' , '605001' , '232' , STR_TO_DATE('Nov-16-2008','%b-%d-%Y') , 'Male', '21', 'Single' , 'anbarasan.v@gmail.com' , STR_TO_DATE('Nov-15-2008','%b-%d-%Y') , 'slpp1', 'Fixed', '0', '3000', '11year', 'Nov-03-2008', 'ref', 'etr', 'SL2100004', '9\'', '2', '0');

Open in new window

whspiderAuthor Commented:
Am very sorry for takin ur time. I got it.
The thing is i my code i am having 2 inserts with slight variation.I was inserting on the wrong place.just found.thank you so muchhhh
UmeshSenior Principal Technical Support EngineerCommented:
You are most welcome buddy..

Thanks,
Umesh
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.