Converting dates from one format to another

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
primate
Forum Commoner
Posts: 49
Joined: Fri Jun 18, 2004 4:38 am
Location: England, UK

Converting dates from one format to another

Post 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.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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
primate
Forum Commoner
Posts: 49
Joined: Fri Jun 18, 2004 4:38 am
Location: England, UK

Post by primate »

Its MS SQL
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

then you may find this article useful
primate
Forum Commoner
Posts: 49
Joined: Fri Jun 18, 2004 4:38 am
Location: England, UK

Post 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?
AGISB
Forum Contributor
Posts: 422
Joined: Fri Jul 09, 2004 1:23 am

Post by AGISB »

* means you read every table column so if the date is in the table you already got it.
Last edited by AGISB on Wed Dec 15, 2004 3:40 am, edited 1 time in total.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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;
primate
Forum Commoner
Posts: 49
Joined: Fri Jun 18, 2004 4:38 am
Location: England, UK

Post 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?
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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.
primate
Forum Commoner
Posts: 49
Joined: Fri Jun 18, 2004 4:38 am
Location: England, UK

Post by primate »

Thanks, got that working. Can't get the alphanumeric characters after the day numbers it seems but otherwise its fine.
Post Reply