timestamp formatting question

smurr

im having trouble formatting my timestamp correctly. For example, (im using the 'NOW()' function) it displays the timestamp as [20030117242424]. Im much rather like it to display it as '2003-01-17 24:24:24'.

I found the section in the manual that discusses this function, but i still can't get it to do what i want.

NOW()
SYSDATE()
CURRENT_TIMESTAMP
Returns the current date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context:
mysql> SELECT NOW();
-> '1997-12-15 23:50:26'
mysql> SELECT NOW() + 0;
-> 19971215235026

Note that NOW() is only evaluated once per query, namely at the start of query execution. This means that multiple references to NOW() within a single query will always give the same time.

thanks for any help
-Steve

Sxooter

It's a lot easier to get help if you post your actual query.

smurr

<?php

if ($submit == submit) {
// process form
$db = mysql_connect("localhost", "root", "");
mysql_select_db("smegpot_test",$db);
$sql = "INSERT INTO news SET
Subject='$subject',
Newstext='$newstext',
Newsdate=NOW()";
$result = mysql_query($sql);
} else{
exit();
}
?>

Sxooter

So what kind of field is Newsdate?

cgraz

Insert using NOW(), but to select, format it using DATE_FORMAT()

$query = mysql_query("SELECT DATE_FORMAT(column_name, '%d-%m-%y') AS DATE FROM your_table") or die(mysql_error());

Follow that link and search the page for date_format if you want different specifiers to format the date.

Cgraz

Sxooter

That doesn't answer my question. What type of field are you STORING the now() output into? i.e. if the newsdate field is numeric or int, then WHEN YOU RUN NOW() it will store the timestamp like:

2002020301230

If the newsdate field is of type text, then it will store it like:

'2002-02-03 12 22 00'

or something like that. The problem is the target field type is casting the output of NOW() when you store it.

smurr

YESSSS!!!!!

when making the table i previously set it as

"Newsdate TIMESTAMP NOT NULL" . . .

so i did

"Newsdate TEXT NOT NULL"

haha . . . i suck

thanks

later