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...
0
Loganathan NatarajanLAMP DeveloperCommented:
you can convert your user input date into mysql format date... and can be displayed as you wish....
0
UmeshMySQL Principle 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
0

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
Get expert help—faster!

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

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
0
UmeshMySQL Principle 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

0
whspiderAuthor Commented:
Thank you so much its working.....
0
whspiderAuthor Commented:
thank you
0
UmeshMySQL Principle Technical Support EngineerCommented:
Anytime..

Thanks,
Umesh
0
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);
0
UmeshMySQL Principle 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);
0
whspiderAuthor Commented:
no its not working....
0
UmeshMySQL Principle Technical Support EngineerCommented:
What is the error you are getting???
0
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
0
UmeshMySQL Principle 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

0
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
0
UmeshMySQL Principle 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

0
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')
0
UmeshMySQL Principle 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

0
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
0
UmeshMySQL Principle Technical Support EngineerCommented:
You are most welcome buddy..

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

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.