Printable Version of Topic

Click here to view this topic in its original format

HTMLHelp Forums _ Client-side Scripting _ Date format issue while fetching data

Posted by: shankar from vizag May 14 2019, 09:33 AM

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

Posted by: CharlesEF May 14 2019, 11:10 AM

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 https://stackoverflow.com/questions/1328025/jquery-ui-datepicker-change-date-format, it may help you.

Posted by: shankar from vizag May 15 2019, 08:11 PM

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 https://stackoverflow.com/questions/1328025/jquery-ui-datepicker-change-date-format, 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.

Posted by: CharlesEF May 16 2019, 12:32 AM

The jQuery site provides a working example, found https://jqueryui.com/resources/demos/datepicker/date-formats.html. 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>

Posted by: shankar from vizag May 17 2019, 09:28 AM

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

The jQuery site provides a working example, found https://jqueryui.com/resources/demos/datepicker/date-formats.html. 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 https://jqueryui.com/resources/demos/datepicker/date-formats.html. 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.


Attached image(s)
Attached Image

Posted by: CharlesEF May 17 2019, 09:49 AM

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?

Posted by: CharlesEF May 17 2019, 12:00 PM

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.

Posted by: CharlesEF May 18 2019, 01:09 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>

Posted by: shankar from vizag May 20 2019, 10:32 AM

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: 68

Posted by: CharlesEF May 20 2019, 11:26 AM

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'.

Posted by: shankar from vizag May 21 2019, 02:23 AM

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.

Posted by: shankar from vizag May 21 2019, 09:43 AM

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



Posted by: CharlesEF May 21 2019, 10:26 AM

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.

Posted by: CharlesEF May 21 2019, 07:12 PM

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.

Posted by: shankar from vizag May 22 2019, 09:10 AM

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.

Posted by: CharlesEF May 22 2019, 02:41 PM

Glad you got it working. Before I read about DATE_FORMAT I would have coded it the same way (in the while loop).

Posted by: CharlesEF May 22 2019, 06:44 PM

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.

Powered by Invision Power Board (http://www.invisionboard.com)
© Invision Power Services (http://www.invisionpower.com)