Converting dates from one format to another
Moderator: General Moderators
Converting dates from one format to another
Hi,
I'm storing dates in a SQL database using smalldatetime.
When I pull the date out of the database and print it, it appears as eg. "2004-12-14 14:50:00"
I want it to look like "14th December 2004 2:50pm"
How the hell do I convert it? I've spent all afternoon looking through SQL forums and experimenting with the date() function but I am obviously missing something fundamental as I only get a load of nonsense whenever I try to convert it.
I'm storing dates in a SQL database using smalldatetime.
When I pull the date out of the database and print it, it appears as eg. "2004-12-14 14:50:00"
I want it to look like "14th December 2004 2:50pm"
How the hell do I convert it? I've spent all afternoon looking through SQL forums and experimenting with the date() function but I am obviously missing something fundamental as I only get a load of nonsense whenever I try to convert it.
then you may find this article useful
OK, thanks. 
So if my query currently reads:
How do I go about incorporating the extra information shown in that article?
Would it be easier to have a separate query to get the date or its it possible to do the whole thing at once?
So if my query currently reads:
Code: Select all
<?php
$query = "SELECT * FROM Posts WHERE (PostID='{$_GET['PostID']}')";
?>Would it be easier to have a separate query to get the date or its it possible to do the whole thing at once?
it's recommended to name the columns explicitly in a query, like this:
Code: Select all
select
post_id,
convert(char(9), post_date, 6) as post_date,
.............
from
posts
where
post_id = {$_GETї'id']}