whspider
asked on
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?
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?
Loganathan Natarajan
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...
you can convert your user input date into mysql format date... and can be displayed as you wish....
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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($
?>
<table width="100%">
<tbody>
<tr>
<td bgcolor="#c10000" colspan="4">
<p align="center"><font color="#ffffff"><b>PROFILE
</td>
</tr>
<tr>
<td bgcolor="#99ccff"><b> 
<td bgcolor="#99ccff"><? echo $rows1['name']; ?></td>
<td bgcolor="#99ccff"><b> 
<td bgcolor="#99ccff"><? echo $rows1['addr']; ?></td>
<td bgcolor="#99ccff"><b> 
<td bgcolor="#99ccff"><? echo $rows1['doj']; ?></td>
</tr>
</tbody>
</table>
D.O.J is in the format 2008-11-13 in the mysql table
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($
?>
<table width="100%">
<tbody>
<tr>
<td bgcolor="#c10000" colspan="4">
<p align="center"><font color="#ffffff"><b>PROFILE
</td>
</tr>
<tr>
<td bgcolor="#99ccff"><b> 
<td bgcolor="#99ccff"><? echo $rows1['name']; ?></td>
<td bgcolor="#99ccff"><b> 
<td bgcolor="#99ccff"><? echo $rows1['addr']; ?></td>
<td bgcolor="#99ccff"><b> 
<td bgcolor="#99ccff"><? echo $rows1['doj']; ?></td>
</tr>
</tbody>
</table>
D.O.J is in the format 2008-11-13 in the mysql table
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> name </b></td>
<td bgcolor="#99ccff"><? echo $rows1['name']; ?></td>
<td bgcolor="#99ccff"><b> addresss</b></td>
<td bgcolor="#99ccff"><? echo $rows1['addr']; ?></td>
<td bgcolor="#99ccff"><b> D.O.J </b></td>
<td bgcolor="#99ccff"><? echo $rows1['doj']; ?></td>
</tr>
</tbody>
</table>
ASKER
Thank you so much its working.....
ASKER
thank you
Anytime..
Thanks,
Umesh
Thanks,
Umesh
ASKER
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
$result=mysql_query($sql);
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
$result=mysql_query($sql);
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-
$result=mysql_query($sql);
$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-
$result=mysql_query($sql);
ASKER
no its not working....
What is the error you are getting???
ASKER
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
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);
ASKER
I have tried removing the single quotes but its not workin. I pasted your code also still the default value only is getting stored
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')";ASKER
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',
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',
Seems to be some other issue...I just ran below query and it shown the proper dates
select STR_TO_DATE('Nov-16-2008',
2008-11-16 2008-11-15
Pls try running above query from commandline or thru any gui client and see what happens
select STR_TO_DATE('Nov-16-2008',
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');ASKER
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
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
You are most welcome buddy..
Thanks,
Umesh
Thanks,
Umesh