Formatting Dates
Moderator: General Moderators
-
CoolAsCarlito
- Forum Contributor
- Posts: 192
- Joined: Sat May 31, 2008 3:27 pm
- Contact:
Formatting Dates
I'm trying to format my date in a database table. Is there a way to do it in the mysqladmin or how do you have to do? It appears right now like 2008-08-08 but I want it to appear like July 8, 2008
I have it in my php code as:
<p><b>Date Won</b><br>'.$row['datewon'].'
I have it in my php code as:
<p><b>Date Won</b><br>'.$row['datewon'].'
-
CoolAsCarlito
- Forum Contributor
- Posts: 192
- Joined: Sat May 31, 2008 3:27 pm
- Contact:
Re: Formatting Dates
Forgot to add what I put in the SELECT field.
$query = "SELECT *, DATE_FORMAT('datewon', '%M %e, %Y') FROM titles";
$query = "SELECT *, DATE_FORMAT('datewon', '%M %e, %Y') FROM titles";
Re: Formatting Dates
Don't format the date in the table. Format it in the output. Either:
1. Use PHP's time()
Use PHP's time() function to get the time and store it as a BIGINT value in the database. Use PHP's date() function to format the date/time when it's outputted. Using this makes it really easy to do queries including time (you can use < > - + and so on the dates in the queries).
2. User DATETIME
Use SQL's DATETIME.
If you absolutely wanna see the date as "July 8, 2008", use VARCHAR, and store a string representation (i.e. return value of PHP's date() function) in addition to the BIGINT value. You'll have one value for you to read and another value for your scripts to use. Your script don't wanna work on dates stored as "July 8, 2008".
1. Use PHP's time()
Use PHP's time() function to get the time and store it as a BIGINT value in the database. Use PHP's date() function to format the date/time when it's outputted. Using this makes it really easy to do queries including time (you can use < > - + and so on the dates in the queries).
2. User DATETIME
Use SQL's DATETIME.
If you absolutely wanna see the date as "July 8, 2008", use VARCHAR, and store a string representation (i.e. return value of PHP's date() function) in addition to the BIGINT value. You'll have one value for you to read and another value for your scripts to use. Your script don't wanna work on dates stored as "July 8, 2008".
-
CoolAsCarlito
- Forum Contributor
- Posts: 192
- Joined: Sat May 31, 2008 3:27 pm
- Contact:
Re: Formatting Dates
I'm lost.
Re: Formatting Dates
A BIGINT column is a very ill conceived solution for this. Please only use DATETIME, TIMESTAMP, TIME or DATE for a column which is supposed to store a date.
You are on the right track with formatting the date as it comes out, in your select statement. What happens when you run the query you posted?
You are on the right track with formatting the date as it comes out, in your select statement. What happens when you run the query you posted?
-
CoolAsCarlito
- Forum Contributor
- Posts: 192
- Joined: Sat May 31, 2008 3:27 pm
- Contact:
Re: Formatting Dates
Okay I changed it to DATE for my format in my phpmyadmin and when I go to the page where it shows the date it looks like this:
Date Won
2008-08-08
I want it to be July 8, 2008 but if that's not at all possible then I'll be okay with just 08-08-2008
Date Won
2008-08-08
I want it to be July 8, 2008 but if that's not at all possible then I'll be okay with just 08-08-2008
- EverLearning
- Forum Contributor
- Posts: 282
- Joined: Sat Feb 23, 2008 3:49 am
- Location: Niš, Serbia
Re: Formatting Dates
Your query should give you what you want, just don't enclose the field name in single quotes, MySql interprets it as a string, not a field name. Use backticks ``.CoolAsCarlito wrote:$query = "SELECT *, DATE_FORMAT('datewon', '%M %e, %Y') FROM titles";
Code: Select all
$query = "SELECT *, DATE_FORMAT(`datewon`, '%M %e, %Y') FROM titles
-
CoolAsCarlito
- Forum Contributor
- Posts: 192
- Joined: Sat May 31, 2008 3:27 pm
- Contact:
Re: Formatting Dates
Still didn't work.
- EverLearning
- Forum Contributor
- Posts: 282
- Joined: Sat Feb 23, 2008 3:49 am
- Location: Niš, Serbia
Re: Formatting Dates
I guess you're still echoing the $row['datewon']. That will display the unformatted date. Try this
If you still have trouble, you'll need to post a little more of your code for us to understand what the problem is.
Code: Select all
$query = "SELECT *, DATE_FORMAT(`datewon`, '%M %e, %Y') as formatted_date FROM titles";
//... and later in the code echo it
echo $row['formatted_date'];
Re: Formatting Dates
Can you share the code for the whole page?
-
CoolAsCarlito
- Forum Contributor
- Posts: 192
- Joined: Sat May 31, 2008 3:27 pm
- Contact:
Re: Formatting Dates
Code: Select all
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Outlaw Title History</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body bgcolor="black" text="white" link="red" vlink="red">
<?php
// Connects to your Database
$link = mysql_connect("?", "?", "?") or die(mysql_error());
mysql_select_db("?",$link) or die(mysql_error());
if (!mysql_select_db("?", $link)) {
echo 'Could not select database';
exit;
}
//Define the query
$query = "SELECT *, DATE_FORMAT(`datewon`, '%M %e, %Y') FROM titles";
if ($r = mysql_query ($query)){ // Run the query.
// Retrieve and print every record
while ($row = mysql_fetch_array ($r)){
print '<table border=0 width=575>';
print '<tr><td background="images/bg_bar3.gif" height=35 colspan=3> <a href="title.php?id=">'.$row['titlename'].'</td></tr>';
print '<tr><td valign=top width=200><a href=titlehistories.php?id=' . $row['id'] . ' title="View KOW '.$row['titlename'].' History"><img src="/images/' . $row['titleimage'] . '" width=200 height=200 border=0 alt="View KOW '.$row['titlename'].' History"></a></td>';
print '<td valign=top width=160><a href="bio.php?' . $row['champion'] . '" title="View History for ' . $row['champion'] . '"><img src=/images/' . $row['champion'] . ' height=200 width=160 border=0></a></td>';
print '<td valign=top><b>Current Champion</b><br><a href="bio.php?' . $row['champion'] . '"><b>' . $row['champion'] . '</b></a><p><b>Date Won</b><br>'.$row['datewon'].'<p><b>Contenders<br>1. <a href="bio.php?' . $row['contender1'] . '">' . $row['contender1'] . '</a><br>2. <a href="bio.php?' . $row['contender2'] . '">' . $row['contender2'] . '</a><br>3. <a href="bio.php?' . $row['contender3'] . '">' . $row['contender3'] . '</a><br></b></td></tr>';
print '</table>';
print '<img src=images/spacer.gif>';
print '</table>';
}
} else {
die ('<p>Could not retrieve the data because <b>' . mysql_error() . '</b>. The query was $query.</p>');
} //End of query IF
?>
</body>
</html>
Re: Formatting Dates
Do as EverLearning proposed, you need to change your query a little more.
-
CoolAsCarlito
- Forum Contributor
- Posts: 192
- Joined: Sat May 31, 2008 3:27 pm
- Contact:
Re: Formatting Dates
Thank you EverLearning what you told me to do did work. I appreciate your help.