Display timestamp as UK format date on Select?

[mkultron]

Hi guys,

 

I'm saving user input as a timestamp to my database but when I select it I want the date to display in the UK format date day/month/year. Ideally I'd like to display it like this "15th December 2011". It is not the current time but a time in the future the user selects for an event. Is this something I can do from a timestamp or do I need to save my data some other way? e.g. as a datetime. Also, I want to display a time but like "7:00 am" and have it saved to my database, what would be the best way to do this?

 

Any help would be greatly appreciated

Thanks

MK

[kickstart]

Hi

 

A timestamp is usually just that - a stamp on the record of the current time when it is updated.

 

To format it you would use something like:-

 

SELECT DATE_FORMAT(SomeDateTimeField, '%D %M %Y') 
FROM SomeTable

 

All the best

 

Keith

[mkultron]

Thanks kickstart -

 

Basically what I'm doing is adding the data via three differnet drop downs, one for date, day and year, joining these inputs together and inserting into the database as a timestamp. I'm guessing this is probably not the best way to do things. The timestamp is returning OK when I echo my select statement but it's obviously in the US format. Ideally I'd do something like "12th Dec 2011" though. Am I best to store the input as a VARCHAR or something to achieve this? I've tried to do as you suggested to draw my values out using the date_format like so:

 

$result = mysql_query("SELECT eventvenue, DATE_FORMAT(eventdate, '%D %M %Y') FROM mydatabase.events");

 

but I get an error like this "Warning: mysql_result() [function.mysql-result]: eventdate not found in MySQL result index 3..."

 

Any ideas?

 

Thanks

MK

[kickstart]

Hi

 

Err, are you using a column of type Timestamp to store it in? Or are you just storing the date in a character field?

 

Once it is in the table as a time stamp it should be easy to reformat it.

 

You will need to use STR_TO_DATE to convert your date string to dates to insert into the db

 

All the best

 

Keith

[mkultron]

Ah my bad, it is actually stored just as a "date".

[kickstart]

Hi

 

The code I posted earlier should work for a plain date field.

 

The error message you have got does suggest that the eventdate field isn't in that table though.

 

All the best

 

Keith

[PFMaBiSmAd]

To reference the value that the select/date_format produces, you would need to use an alias name for that term in the query or use a  fetch_row/fetch_array function to access the correct value. Otherwise you would need to do something like - $row["DATE_FORMAT(eventdate, '%D %M %Y')"] to access the selected value.

[fenway]

To reference the value that the select/date_format produces, you would need to use an alias name for that term in the query or use a  fetch_row/fetch_array function to access the correct value. Otherwise you would need to do something like - $row["DATE_FORMAT(eventdate, '%D %M %Y')"] to access the selected value.

Oh, please use an alias -- that's precisely what they're for.