Page 1 of 1
Converting dates from one format to another
Posted: Tue Dec 14, 2004 10:15 am
by primate
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.
Posted: Tue Dec 14, 2004 10:34 am
by Weirdan
If the db in question is MySQL you might want to check their man page on [mysql_man]date_format[/mysql_man] function
Posted: Tue Dec 14, 2004 10:46 am
by primate
Its MS SQL
Posted: Tue Dec 14, 2004 11:21 am
by Weirdan
then you may find
this article useful
Posted: Wed Dec 15, 2004 3:21 am
by primate
OK, thanks.
So if my query currently reads:
Code: Select all
<?php
$query = "SELECT * FROM Posts WHERE (PostID='{$_GET['PostID']}')";
?>
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?
Posted: Wed Dec 15, 2004 3:40 am
by AGISB
* means you read every table column so if the date is in the table you already got it.
Posted: Wed Dec 15, 2004 3:40 am
by Weirdan
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']}
Posted: Wed Dec 15, 2004 4:45 am
by primate
I need all the information from the table thats why I used * so in order to get the rest of the columns, can I include the column names where you've put ....... in your example weirdan?
Posted: Wed Dec 15, 2004 5:13 am
by Weirdan
I need all the information from the table thats why I used * so in order to get the rest of the columns, can I include the column names where you've put ....... in your example weirdan?
certainly, that's what was meant.
Posted: Thu Dec 16, 2004 4:21 am
by primate
Thanks, got that working. Can't get the alphanumeric characters after the day numbers it seems but otherwise its fine.