Date format issue while fetching data |
Date format issue while fetching data |
shankar from vizag |
May 14 2019, 09:33 AM
Post
#1
|
Advanced Member Group: Members Posts: 202 Joined: 18-June 13 Member No.: 19,316 |
Greetings
In my php page, I have used JQuery datepicker and it is working fine when the function as follows: <script> $("#myDatepicker").datepicker({dateFormat: 'yy-mm-dd'}); </script> But, I need the format as dd-mm-yy. When I tried to change the format as below, <script> $("#myDatepicker").datepicker({dateFormat: 'dd-mm-yy'}); </script> the date is saving in table as 0000-00-00. Kindly help me out to overcome this issue. regards |
CharlesEF |
May 14 2019, 11:10 AM
Post
#2
|
Programming Fanatic Group: Members Posts: 1,981 Joined: 27-April 13 From: Edinburg, Texas Member No.: 19,088 |
Ok, I'm back. Yesterday was a long day, glad it's over. As for your problem, I don't do jQuery but read this thread, it may help you.
|
shankar from vizag |
May 15 2019, 08:11 PM
Post
#3
|
Advanced Member Group: Members Posts: 202 Joined: 18-June 13 Member No.: 19,316 |
Ok, I'm back. Yesterday was a long day, glad it's over. As for your problem, I don't do jQuery but read this thread, it may help you. Greetings Charles ji, as per your advice, I tried the following, <!doctype html> <html> <head> <link rel="stylesheet" type="text/css" href="css/bootstrap.min.css"> <link rel="stylesheet" href="w3.css"> <link rel="stylesheet" href="js/jquery-ui/jquery-ui.min.css"> </head> <body> Enter Date: <input type = "text" id="datepicker"> <script src="js/bootstrap.min.js"></script> <script src = "js/jquery.js"></script> <script src = "js/jquery-ui/jquery-ui.js"></script> <script> $('#datepicker').datepicker(); var date = $('#datepicker').datepicker({ dateFormat: 'dd-mm-yy' }).val(); </script> </body> </html> But, I am getting the display as mm-dd-yy rather dd-mm-yy[i]. I had gone through the attached reference, nowhere I found the solution for dd-mm-yy format. Kindly guide me. |
CharlesEF |
May 16 2019, 12:32 AM
Post
#4
|
Programming Fanatic Group: Members Posts: 1,981 Joined: 27-April 13 From: Edinburg, Texas Member No.: 19,088 |
The jQuery site provides a working example, found here. While it doesn't have 'dd-mm-yy' as an option you should be able to 'View source' and look at the javascript to figure out how it works. Also, load all your javascript in the <head>...</head> section.
I just played with the datepicker example and found 'dd-mm-yy' will display '16-05-2019' while 'dd-mm-y' will display '16-05-19'. So, this code should work: CODE <!doctype html> <html> <head> <link rel="stylesheet" type="text/css" href="css/bootstrap.min.css"> <link rel="stylesheet" href="w3.css"> <link rel="stylesheet" href="js/jquery-ui/jquery-ui.min.css"> <script src="js/bootstrap.min.js"></script> <script src = "js/jquery.js"></script> <script src = "js/jquery-ui/jquery-ui.js"></script> </head> <body> Enter Date: <input type = "text" id="datepicker"> <script> $('#datepicker').datepicker({ dateFormat: 'dd-mm-yy' }); </script> </body> </html> This post has been edited by CharlesEF: May 16 2019, 12:47 AM |
shankar from vizag |
May 17 2019, 09:28 AM
Post
#5
|
Advanced Member Group: Members Posts: 202 Joined: 18-June 13 Member No.: 19,316 |
The jQuery site provides a working example, found here. While it doesn't have 'dd-mm-yy' as an option you should be able to 'View source' and look at the javascript to figure out how it works. Also, load all your javascript in the <head>...</head> section. I just played with the datepicker example and found 'dd-mm-yy' will display '16-05-2019' while 'dd-mm-y' will display '16-05-19'. So, this code should work: CODE <!doctype html> <html> <head> <link rel="stylesheet" type="text/css" href="css/bootstrap.min.css"> <link rel="stylesheet" href="w3.css"> <link rel="stylesheet" href="js/jquery-ui/jquery-ui.min.css"> <script src="js/bootstrap.min.js"></script> <script src = "js/jquery.js"></script> <script src = "js/jquery-ui/jquery-ui.js"></script> </head> <body> Enter Date: <input type = "text" id="datepicker"> <script> $('#datepicker').datepicker({ dateFormat: 'dd-mm-yy' }); </script> </body> </html> Thank you Charles ji I have gone through the link provided by you here. I had worked on all referred date formats. But except yy-mm-dd, all formats saving 0000-00-00 in the mysql database. Here, I want to mention that I am using Windows. Does it matter ? I am attaching the jpg file of the clip showing the saved data in mysql table. This post has been edited by shankar from vizag: May 17 2019, 09:29 AM Attached image(s) |
CharlesEF |
May 17 2019, 09:49 AM
Post
#6
|
Programming Fanatic Group: Members Posts: 1,981 Joined: 27-April 13 From: Edinburg, Texas Member No.: 19,088 |
Are you getting the date in the correct format? Is it only the database that shows zeros? You don't show enough code, I would need to see the code that adds the data to the database.
Does your date picker have a 'name' attribute? |
CharlesEF |
May 17 2019, 12:00 PM
Post
#7
|
Programming Fanatic Group: Members Posts: 1,981 Joined: 27-April 13 From: Edinburg, Texas Member No.: 19,088 |
Is your database column a DATE or DATETIME? If it is a DATE then a string date will work. If it is a DATETIME then you need to convert the date string into a date, then write that to the database.
It is a good idea to store dates as 'YYYY-MM-DD' in the database. When you pull it out then you can convert it to whatever date format you wish to use. |
CharlesEF |
May 18 2019, 01:09 PM
Post
#8
|
Programming Fanatic Group: Members Posts: 1,981 Joined: 27-April 13 From: Edinburg, Texas Member No.: 19,088 |
Since I've not heard back from you I'm not exactly sure what you need help with. Here is a working example. Remember, 'dd-mm-yy' is not well supported in Javascript but PHP seems to support it better. But, I would not count on it.
CODE <?php if(isset($_POST["submitit"])) { date_default_timezone_set("America/Chicago"); $date = new DateTime($_POST["datepicker"]); echo($date->format("Y-m-d")); } ?> <!doctype html> <html lang="en"> <head> <link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css"> <link rel="stylesheet" href="/resources/demos/style.css"> <script src="https://code.jquery.com/jquery-1.12.4.js"></script> <script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script> </head> <body> <form action="<?php echo($_SERVER["PHP_SELF"]); ?>" method="post" accept-charset="UTF-8"> <p> <label for="datepicker">Enter Date:</label><br> <input type="text" id="datepicker" name="datepicker"> </p> <p> <input type="submit" id="submitit" name="submitit" value="Submit"> </p> </form> <script> window.onload = function() { $("#datepicker").datepicker({ dateFormat: "dd-mm-yy" }); } </script> </body> </html> |
shankar from vizag |
May 20 2019, 10:32 AM
Post
#9
|
Advanced Member Group: Members Posts: 202 Joined: 18-June 13 Member No.: 19,316 |
Since I've not heard back from you I'm not exactly sure what you need help with. Here is a working example. Remember, 'dd-mm-yy' is not well supported in Javascript but PHP seems to support it better. But, I would not count on it. CODE <?php if(isset($_POST["submitit"])) { date_default_timezone_set("America/Chicago"); $date = new DateTime($_POST["datepicker"]); echo($date->format("Y-m-d")); } ?> <!doctype html> <html lang="en"> <head> <link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css"> <link rel="stylesheet" href="/resources/demos/style.css"> <script src="https://code.jquery.com/jquery-1.12.4.js"></script> <script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script> </head> <body> <form action="<?php echo($_SERVER["PHP_SELF"]); ?>" method="post" accept-charset="UTF-8"> <p> <label for="datepicker">Enter Date:</label><br> <input type="text" id="datepicker" name="datepicker"> </p> <p> <input type="submit" id="submitit" name="submitit" value="Submit"> </p> </form> <script> window.onload = function() { $("#datepicker").datepicker({ dateFormat: "dd-mm-yy" }); } </script> </body> </html> I am extremely sorry Charles ji Due to bad health, I could not able to reply. I tried your referred code but it is inserting the date value in mysql table as 0000-00-00. 1. My database column datatype is "DATE". 2. in html, name attribute is assigned. 3. I am attaching the php file for inserting the record values to the mysql table. Tried all possible references, but failing in inserting the date value in the database as dd-mm-YY. I need to save the date in database table as dd-mm-YY and also fetch in the same format. testondatepicker.php ( 1.77k ) Number of downloads: 486 |
CharlesEF |
May 20 2019, 11:26 AM
Post
#10
|
Programming Fanatic Group: Members Posts: 1,981 Joined: 27-April 13 From: Edinburg, Texas Member No.: 19,088 |
It seems MySQL supports ISO-8601 date/time values, and no others. This means you will have to convert your date to 'YYYY-MM-DD' format in order to store it in the database. When you pull the date out of the database you need to convert it back to 'DD-MM-YYYY' format. MySQL is converting your dates to '0000-00-00'.
|
shankar from vizag |
May 21 2019, 02:23 AM
Post
#11
|
Advanced Member Group: Members Posts: 202 Joined: 18-June 13 Member No.: 19,316 |
It seems MySQL supports ISO-8601 date/time values, and no others. This means you will have to convert your date to 'YYYY-MM-DD' format in order to store it in the database. When you pull the date out of the database you need to convert it back to 'DD-MM-YYYY' format. MySQL is converting your dates to '0000-00-00'. Charless ji I am not getting any idea how to do it. Any reference please. |
shankar from vizag |
May 21 2019, 09:43 AM
Post
#12
|
Advanced Member Group: Members Posts: 202 Joined: 18-June 13 Member No.: 19,316 |
It seems MySQL supports ISO-8601 date/time values, and no others. This means you will have to convert your date to 'YYYY-MM-DD' format in order to store it in the database. When you pull the date out of the database you need to convert it back to 'DD-MM-YYYY' format. MySQL is converting your dates to '0000-00-00'. Charless ji I tried the following code instead of inserting the dd-mm-yyyy to fetch from the database table to php with mysql query, but failed <?php $link = mysqli_connect("localhost","root",""); mysqli_select_db($link,"fts"); if(isset($_POST['search'])){ $frmdate=$_POST['frmdate']; $todate=$_POST['todate']; $q = "SELECT `file_no`, date_format(`sent_on`,'%d-%m-%Y'), FROM `filetrack` WHERE `sent_on` Between '$frmdate' and '$todate' order by `sent_on`"; $query = mysqli_query($link,$q); while($res=mysqli_fetch_array($query)){ ?> <tr> <td><?php echo $res['file_no'];?></td> <td><?php echo $res['sent_on'];?></td> <?php } ?> |
CharlesEF |
May 21 2019, 10:26 AM
Post
#13
|
Programming Fanatic Group: Members Posts: 1,981 Joined: 27-April 13 From: Edinburg, Texas Member No.: 19,088 |
Before I pick this code apart, please post the PHP code that puts the date into the database. After you changed it to convert 'DD-MM-YYYY' into 'YYYY-MM-DD'.
Hint: You can't mix PHP commands with MySQL commands. Your 'SELECT' query does just that. date_format is a PHP command and you can't format a date that has not been retrieved (also, it is used incorrectly between double quotes). The select needs to return `sent_on` but you need to convert it in the 'while' loop. |
CharlesEF |
May 21 2019, 07:12 PM
Post
#14
|
Programming Fanatic Group: Members Posts: 1,981 Joined: 27-April 13 From: Edinburg, Texas Member No.: 19,088 |
I did a little research and found that date_format() is also a MySQL command (I've never needed it so I didn't know about it). Based on what I have read I see no reason why this line of code would not work:
CODE $q = "SELECT `file_no`, date_format(`sent_on`,'%d-%m-%Y'), FROM `filetrack` WHERE `sent_on` Between '$frmdate' and '$todate' order by `sent_on`"; Maybe because of the extra comma after date_format? And you didn't use the CAST() function. Like this: CODE $q = "SELECT `file_no`, DATE_FORMAT(`sent_on`,'%d-%m-%Y') FROM `filetrack` WHERE `sent_on` BETWEEN CAST('$frmdate' AS DATE) AND CAST('$todate' AS DATE) ORDER BY `sent_on`"; Remember, since you have to store the dates as 'YYYY-MM-DD' format make sure $frmdate and $todate are in the same format. Also it is a good idea to put all MySQL commands in upper case. |
shankar from vizag |
May 22 2019, 09:10 AM
Post
#15
|
Advanced Member Group: Members Posts: 202 Joined: 18-June 13 Member No.: 19,316 |
I did a little research and found that date_format() is also a MySQL command (I've never needed it so I didn't know about it). Based on what I have read I see no reason why this line of code would not work: CODE $q = "SELECT `file_no`, date_format(`sent_on`,'%d-%m-%Y'), FROM `filetrack` WHERE `sent_on` Between '$frmdate' and '$todate' order by `sent_on`"; Maybe because of the extra comma after date_format? And you didn't use the CAST() function. Like this: CODE $q = "SELECT `file_no`, DATE_FORMAT(`sent_on`,'%d-%m-%Y') FROM `filetrack` WHERE `sent_on` BETWEEN CAST('$frmdate' AS DATE) AND CAST('$todate' AS DATE) ORDER BY `sent_on`"; Remember, since you have to store the dates as 'YYYY-MM-DD' format make sure $frmdate and $todate are in the same format. Also it is a good idea to put all MySQL commands in upper case.Thank you Charless Ji for your continuous support to me. At last, I succeeded after so much research of many references. Finally the following php code displaying the date as required i.e. dd-mm-YY. <?php $link = mysqli_connect("localhost","root",""); mysqli_select_db($link,"fts"); if(isset($_POST['search'])){ $frmdate=$_POST['frmdate']; $todate=$_POST['todate']; $q = "SELECT * FROM `filetrack` WHERE `sent_on` Between '$frmdate' and '$todate' order by `sent_on`"; $query = mysqli_query($link,$q); while($res=mysqli_fetch_array($query)){ ?> <tr> <td><?php echo $res['file_no'];?></td> <td><?php $my_date=$res['sent_on']; $date=DATE("d-m-Y",strtotime($my_date)); echo $date; ?></td> <?php } ?> Once again tons of thanks Charless ji. |
CharlesEF |
May 22 2019, 02:41 PM
Post
#16
|
Programming Fanatic Group: Members Posts: 1,981 Joined: 27-April 13 From: Edinburg, Texas Member No.: 19,088 |
Glad you got it working. Before I read about DATE_FORMAT I would have coded it the same way (in the while loop).
|
CharlesEF |
May 22 2019, 06:44 PM
Post
#17
|
Programming Fanatic Group: Members Posts: 1,981 Joined: 27-April 13 From: Edinburg, Texas Member No.: 19,088 |
FYI, as a test I put together a MySQL database table and inserted date data. The dates are stored in 'YYYY-MM-DD' format. I used this line of code (modified a little)
CODE $q = "SELECT `file_no`, DATE_FORMAT(`sent_on`,'%d-%m-%Y') FROM `filetrack` WHERE `sent_on` BETWEEN CAST('$frmdate' AS DATE) AND CAST('$todate' AS DATE) ORDER BY `sent_on`"; and it worked correctly. I learned from this exchange also, which is why help forums exist. |
Lo-Fi Version | Time is now: 13th May 2024 - 12:58 AM |