The Web Design Group

... Making the Web accessible to all.

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> Date format issue while fetching data
shankar from vizag
post May 14 2019, 09:33 AM
Post #1


Advanced Member
****

Group: Members
Posts: 149
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
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
CharlesEF
post May 14 2019, 11:10 AM
Post #2


Programming Fanatic
********

Group: Members
Posts: 1,619
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.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
shankar from vizag
post May 15 2019, 08:11 PM
Post #3


Advanced Member
****

Group: Members
Posts: 149
Joined: 18-June 13
Member No.: 19,316



QUOTE(CharlesEF @ May 14 2019, 09:40 PM) *

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.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
CharlesEF
post May 16 2019, 12:32 AM
Post #4


Programming Fanatic
********

Group: Members
Posts: 1,619
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
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
shankar from vizag
post May 17 2019, 09:28 AM
Post #5


Advanced Member
****

Group: Members
Posts: 149
Joined: 18-June 13
Member No.: 19,316



QUOTE(CharlesEF @ May 16 2019, 11:02 AM) *

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)
Attached Image
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
CharlesEF
post May 17 2019, 09:49 AM
Post #6


Programming Fanatic
********

Group: Members
Posts: 1,619
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?
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
CharlesEF
post May 17 2019, 12:00 PM
Post #7


Programming Fanatic
********

Group: Members
Posts: 1,619
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.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
CharlesEF
post May 18 2019, 01:09 PM
Post #8


Programming Fanatic
********

Group: Members
Posts: 1,619
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>
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
shankar from vizag
post May 20 2019, 10:32 AM
Post #9


Advanced Member
****

Group: Members
Posts: 149
Joined: 18-June 13
Member No.: 19,316



QUOTE(CharlesEF @ May 18 2019, 11:39 PM) *

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.


Attached File  testondatepicker.php ( 1.77k ) Number of downloads: 45
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
CharlesEF
post May 20 2019, 11:26 AM
Post #10


Programming Fanatic
********

Group: Members
Posts: 1,619
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'.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
shankar from vizag
post May 21 2019, 02:23 AM
Post #11


Advanced Member
****

Group: Members
Posts: 149
Joined: 18-June 13
Member No.: 19,316



QUOTE(CharlesEF @ May 20 2019, 09:56 PM) *

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.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
shankar from vizag
post May 21 2019, 09:43 AM
Post #12


Advanced Member
****

Group: Members
Posts: 149
Joined: 18-June 13
Member No.: 19,316



QUOTE(CharlesEF @ May 20 2019, 09:56 PM) *

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
}
?>


User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
CharlesEF
post May 21 2019, 10:26 AM
Post #13


Programming Fanatic
********

Group: Members
Posts: 1,619
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.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
CharlesEF
post May 21 2019, 07:12 PM
Post #14


Programming Fanatic
********

Group: Members
Posts: 1,619
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.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
shankar from vizag
post May 22 2019, 09:10 AM
Post #15


Advanced Member
****

Group: Members
Posts: 149
Joined: 18-June 13
Member No.: 19,316



QUOTE(CharlesEF @ May 22 2019, 05:42 AM) *

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.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
CharlesEF
post May 22 2019, 02:41 PM
Post #16


Programming Fanatic
********

Group: Members
Posts: 1,619
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).
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
CharlesEF
post May 22 2019, 06:44 PM
Post #17


Programming Fanatic
********

Group: Members
Posts: 1,619
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.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post

Reply to this topicStart new topic
3 User(s) are reading this topic (3 Guests and 0 Anonymous Users)
0 Members:

 



- Lo-Fi Version Time is now: 25th June 2019 - 11:40 AM