Trouble with a date 2

[kupe]

I used 'Date' for the date field in a database. I try to date_format it, but it will only let me have the date with the year first etc.

Is this the way it is with the Date property?

Each of the tutors I'm using seems to have a different preference - Date, Timestamp etc. Is there a best property to use for dates, please, experts?

 

[TonyGroves]

You're right, DATE is the correct field type to use for dates. And as you've noticed, to store a value in a DATE field, you must supply the value in the form 'yyyy-mm-dd' or 'yyyymmdd' or yyyymmdd. However, when you retrieve a DATE value, you can format any way you want with the DATE_FORMAT function.

 

[kupe]

I would have expected DATE_FORMAT() to manage it. But it doesn't seem to. It doesn't want to when working through php.

So I tried in the database, and it won't work there either. For instance -

SELECT TIME_FORMAT(newsdate, '%a %e %b %Y') from news;

- gives me NULL on each row. Yet summoning newsdate shows each row has a date.

Thanks for helping out again, Tony.

 

[TonyGroves]

If you're using a DATE field, then DATE_FORMAT should do it. TIME_FORMAT doesn't seem to work with DATE fields.

 

[kupe]

Sorry! I'd be rich if I could be sponsored for each time I've used time instead of date. Hopefully, you've shamed me out of it. Many thanks.

 

[kupe]

Tony, I wonder if you are involved with php too. Just in case, do let me ask you this, please.

I want to add that date_format to the following code which draws the dateline of a story from the mysql database. For some reason which you'll spot right away, I expect, php can't/won't handle it.

$result = @mysql_query('SELECT newsdate FROM news WHERE newsid="1"');

if (!$result) {
exit('Error performing query: ' . mysql_error() );
}// Displaying text as paragraph
while ($row = mysql_fetch_array($result)) {
echo $row['newsdate'];

It seems that the first line would read $result = @mysql_query('SELECT DATE_FORMAT(newsdate, '%b %e') FROM news WHERE newsid="1"');
But it won't accept it.

 

[TonyGroves]

I'm afraid I've no PHP experience, but I don't think that's an issue here.

You would need to alias the DATE_FORMAT field, something like:[tt]
SELECT DATE_FORMAT(newsdate, '%b %e') nd
FROM news WHERE newsid="1"'[/tt]
You could then refer to[tt] $row['nd'] [/tt].

 

[ericbrunson]

Your problem is with the quoting (always include the error message).
Change:
'SELECT DATE_FORMAT(newsdate, '%b %e') FROM news WHERE newsid="1"'
to:
'SELECT DATE_FORMAT(newsdate, \'%b %e\') FROM news WHERE newsid="1"'

 

[kupe]

I'll bet that is the answer, thanks so much, Tony. I wondered about aliases but thought as I only wanted the contents of the row to appear, rather than a - well - query, I dismissed it. Will try tonight. Very much obliged. Cheers paul

 

[kupe]

Good to hear from you, Eric. I may be wrong, but those "\"s don't seem to behave in windoze. But I'll give it a try to tonight. Thank you.

 

[ericbrunson]

Or you can change the whole thing to:
"SELECT DATE_FORMAT(newsdate, '%b %e') FROM news WHERE newsid='1'

 

[kupe]

Thanks, Tony, absolutely right. Very grateful.

Cheers, Eric, yes, changing the quotes around was important. With or without an alias, it allows the page not to be stopped with error messages.

But without an alias, it won't let the date appear. Thanks for your help.