Datetime? Date? Timestamp?

[Anon]

Me again ;-)
In my database I have a table with just 2 fields. One called updates and one called date.
The date-format I like to show is YY-MM-DD.
The type "date" should do that, but then it wont update automaticly when I create a new post. Hmmm...
With datetime I get it to update automaticly when I create a new post but then it inclueds the time. I don´t wan´t that...
I tried to use datetime and then set the lenght to 10, but no - that wasn´t accepted...
So... what do I do?
I guess this is a wery basic thing to do but I am at a very basic level ;-)

//Jessica

[Anon]

Err.. first I'm not english, so maybe you don't understand me perfectly.

Ok, I think you must to insert into the field date something with the content of the function now(). And when you want to get the date (without the hour), you make this select code:

SELECT * from table 'jessica' updates, date_format(date, '%Y/%m/%d ') as date;

Please remember the field date has datetime format... and I hope that's all 😉

[jnutter]

Your best bet (if you want to use the DATE type) is to just pre-gen today's date and stuff it into a variable then include that variable in your insert statement.

For example:

$date_today = date("Y-m-d");

This will give you today's date in YYYY-MM-DD format (what MySQL expects). Check out the date() function in the PHP manual for more information on how it works. If you don't specify any date (the optional second parameter) it will just use the current server time (ie. today).

Hope that helps.

• nutter

[Anon]

Does NOW() not work with date fields?

[jnutter]

Not that I know of... I believe it only works with TIMESTAMP and DATETIME fields...

NOW() returns a value in the format YYYYMMDDhhmmss

[[deleted]]

In a database you should always store the date in the most generic format possible: the datetime or timestamp.
Datetime doesn't auto-update, timestamp does. So use timestamp.
The fact that timestamp also stores the time is not bad, it's good.

MySQL's date_format() works for datetime and timestamp, so what's the problem?

[Anon]

I guess it´s my bad english that is a big problem here. Some of you dosn´t seem to understand how I mean.
Another try:
I have a table called updates. In there are one table called news with only two columns -one called "text" and one called "date".
In the news-column I write news on my site. In the date-column is the date when I created the post supposed to be in the format YY-MM-DD.
This is then my code:
<table border="1" cellspacing="0" width="90%" bordercolor="#FFFFFF" bordercolordark="#FFFFFF" bordercolorlight="#000000">
<?php
$connect=@mysql_connect("localhost","user","");
$database=mysql_select_db("updates");
$sql="SELECT * FROM news";
$result=mysql_query("$sql");
while($rad=mysql_fetch_array($result)) {
?><tr><td bgcolor="#99cc99" valign="top"><font size="1" face="verdana,arial"><b>
<?=$rad["date"]?></b></td><td bgcolor="#99cc99" valign="top"><font face="Verdana" size="1"><?=$rad["text"]?>
</td></tr>
<?}
mysql_close($anslut);
?>
</table>

So the question is:
1. What format should I use i my MySQL-database for the date-column?
2. If there are no date-format that can look like and do what I want, then how do I write in my code to make it look the way I want?

Please, forgive me for not understanding your answers. I´m very new to PHP and very bad at english = bad combination ;-)

//Jessica