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 = &#123;$_GET&#1111;'id']&#125;

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.