date display format with mysql

[superkingkong]

Hi guys,

 

I've managed to insert the timestamp into mysql using now()

 

but to display the date on the report,  i would like it to be displayed in this format. would that be possible?

 

Thu, Apr 9, 2009 (CDT) at 02:49:52 PM

 

i need it this way because previously i was using a flat text db, and all records have this format as the 'datetime' field.

 

my second problem would be "importing" this text file into the same table.

 

btw, the current table has the 'datetime' field of timestamp type.

 

appreciate your help, thanks.

[Yesideez]

The DATETIME format is YYYY-MM-DD HH:MM:SS

 

If you want to format the date differently you can use the date() function like this:

date('xxxxx',strtotime($datetimevariable));

 

Just replace the xxxxx with codes from here:

http://www.php.net/date

 

EDIT: Replaced with "xxxxx" due to emotes

[superkingkong]

The DATETIME format is YYYY-MM-DD HH:MM:SS

 

If you want to format the date differently you can use the date() function like this:

date('xxxxx',strtotime($datetimevariable));

 

Just replace the xxxxx with codes from here:

http://www.php.net/date

 

EDIT: Replaced with "xxxxx" due to emotes

 

thanks for the fast reply.

 

so, the date(... ) will be replacing now() - during insertion into mysql

 

or

 

it's only for displaying the date? and i still use now() to insert date?

 

sorry, i'm new in php-mysql.

 

i know the datetime format in mysql. just that, i'm not sure, after it is inserted into mysql in YYYY-MM-DD HH:MM:SS format, can i still change/manipulate the format for displaying purpose?

 

thanks.

[Yesideez]

If you're inserting into a database and want to enter the current date/time then use:

CURDATE() for DATE fields

CURTIME() for TIME fields

NOW() for DATE, TIME and DATETIME fields

 

If you want to insert a specific date/time it must be formatted before inserting into the correct format.

DATE format is YYYY-MM-DD

TIME format is HH:MM:SS

DATETIME format is YYYY-MM-DD HH:MM:SS

This means to format use the following:

$sqlDate=date('Y-m-d',$myDate);
$sqlTime=date('H:i:s',$myTime);
$sqlDateTime=date('Y-m-d H:i:s',$myDateTime);

[superkingkong]

If you want to insert a specific date/time it must be formatted before inserting into the correct format.

DATE format is YYYY-MM-DD

TIME format is HH:MM:SS

DATETIME format is YYYY-MM-DD HH:MM:SS

This means to format use the following:

$sqlDate=date('Y-m-d',$myDate);
$sqlTime=date('H:i:s',$myTime);
$sqlDateTime=date('Y-m-d H:i:s',$myDateTime);

 

thanks. That means, if i want specific date and time format, i won't be able to use the timestamp/datetime for mysql, right?

 

in my case above, i will have to create a varchar field of 38?

 

thanks