Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 405
  • Last Modified:

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?
0
whspider
Asked:
whspider
  • 9
  • 9
  • 2
1 Solution
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 9
  • 9
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now