Changing the date to UK Format?

[stublackett]

Hi, I've got a NOW() in an INSERT Command and I've spotted that it inserts the time into the Database no problems etc

 

But when it pulls it back out its in that silly  ;) American date format

 

Which is Month / Day / Year

 

Over here in the UK we have Day / Month / Year

 

Is there any way I can manipulate this to be in UK Format?

 

So it says Posted On : 10/3/2008 @ <Time>

 

Rather than Posted On : 3/10/2008, Because the way thats read over here is the 3rd of October....

[tippy_102]

This may work:

SELECT date_format(posted_date, '%d/%m/%Y') AS uk_date FROM mytable; 

 

Here's the date_format details:

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-format

[stublackett]

Whats the best way to query that then?

 

Heres my code

<?php #news-index.php By Stuart Blackett

include("dbconnect.php");

        $result = mysql_query("SELECT * FROM $db_table ORDER BY dtime DESC LIMIT 3",$connect);
        
        //lets make a loop and get all news from the database

        while($myrow = mysql_fetch_assoc($result))
                
             {//begin of loop

               //now print the results:

               echo "<b>Title:</b> ";

               echo $myrow['title'];

               echo "<br><br><b>News Posted On :</b> ";

               echo $myrow['dtime'];
		         
               echo "<br> <br>";
		         
		         echo "<b>News Description :</b><br>";
		         
               echo $myrow['text1'];
   			   echo "<br>";

               // Now print the options to (Read,Edit & Delete the news)

               echo "<br><a href=\"read_more.php?newsid=$myrow[newsid]\">Read Further</a>
			 ||  <a href=\"add_news.php\">Add News</a>
			   
                || <a href=\"edit_news.php?newsid=$myrow[newsid]\">Edit</a>

                 || <a href=\"delete_news.php?newsid=$myrow[newsid]\">Delete</a><br><br>";
			 				  
             }

?>

 

The row I want it to pull the data from is called dbtime

 

Cheers

[fenway]

Add the previous expression after your *.

[stublackett]

So your saying make the SQL Query like this :

$result = mysql_query("SELECT *  SELECT date_format(posted_date, '%d/%m/%Y') AS uk_date FROM $db_table ORDER BY time DESC LIMIT 3",$connect);

 

??? I've tried that, As it runs through the code its erroring my WHILE statement

[fenway]

$result = mysql_query("SELECT *, date_format(posted_date, '%d/%m/%Y') AS uk_date FROM $db_table ORDER BY time DESC LIMIT 3",$connect);

[stublackett]

$result = mysql_query("SELECT *, date_format(posted_date, '%d/%m/%Y') AS uk_date FROM $db_table ORDER BY time DESC LIMIT 3",$connect);

 

I've changed date_format to (Date) which is associated with my DB Table, It seems to go through without the error, But not change the format

So I put date_format back in and I was getting errors back to the WHILE statement

 

My query now looks like this

$result = mysql_query("SELECT *, time('%d/%m/%y') AS uk_date FROM $db_table ORDER BY time DESC LIMIT 3",$connect);

 

Still no joy with it though  :-\

[fenway]

Hold on a minute... are you outputting the uk_date field in your code?

 

Chaged to (date)? What does that means?

 

And TIME() won't work.

[stublackett]

I'll give you my scenario

 

The news is posted to the Database using NOW();

Which then gives the Time String in the Database as I set the DB Column to Date/Time also

 

So in the time string in the DB is the American Date Format, Which is for instance today 3/11/2008 the UK date is set as 11/3/2008

 

Here is an example of the Date string I have in my Database : 2008-03-11 13:22:58

 

So when you view the news on the main page it says Posted On : 2008-03-11 13:22:58

What I'd like to do is manipulate that time to say : 2008-11-03 13:22:58

 

Which is the UK Date Format

 

I changed to (date) As thats the column name in my Database

[fenway]

You mean your field is named 'date' -- that's going to be an issue too, since it's a reserved keyword.

 

And you'll need DATE_FORMAT( yourColumnName, '%Y-%m-%d %H:%i:%s' ) AS uk_date

[stublackett]

My field in my Database is now called "time"

 

DATE_FORMAT( time, '%Y-%m-%d %H:%i:%s' ) AS uk_date

 

Should be right??

But where do I put it??

 

The current PHP Script looks like :

<?php #news-index.php By Stuart Blackett

include("dbconnect.php");

        $result = mysql_query("SELECT *, DATE_FORMAT( time, '%Y-%m-%d %H:%i:%s' )AS uk_date FROM $db_table ORDER BY time DESC LIMIT 3",$connect);
                //Limit news items to 3

        while($myrow = mysql_fetch_assoc($result))
              
             {//begin of loop

               //now print the results:
              echo "<hr>";
              
              echo "<br>";
        
               echo "<b> News Title:</b> ";

               echo $myrow['title'];

               echo "<br><br><b>News Posted On :</b> ";

               echo $myrow['time'];
		         
               echo "<br> <br>";
		         
		         echo "<b><td>News Description :</b><br><br></td>";
		         
               echo $myrow['description'];
   			       
                echo "<br>";
                
                echo "<hr>";

               // Now print the options to (Read,Edit & Delete the news)

               echo "<br><a href=\"read_more.php?newsid=$myrow[newsid]\">Read Further</a>
			 ||  <a href=\"add_news.php\">Add News</a>
			   
                || <a href=\"edit_news.php?newsid=$myrow[newsid]\">Edit</a>

                 || <a href=\"delete_news.php?newsid=$myrow[newsid]\">Delete</a><br><br>";
			 				  
             }

?>    

[PFMaBiSmAd]

FYI: date (and now time) are data types, they only have special meaning in a table definition statement. They are not reserved keywords and you can use them as identifier names for tables and columns.

[fenway]

FYI: date (and now time) are data types, they only have special meaning in a table definition statement. They are not reserved keywords and you can use them as identifier names for tables and columns.

Perhaps, but you shouldn't anyway.

 

My field in my Database is now called "time"

 

DATE_FORMAT( time, '%Y-%m-%d %H:%i:%s' ) AS uk_date

 

Should be right??

But where do I put it??

 

The current PHP Script looks like :

<?php #news-index.php By Stuart Blackett

include("dbconnect.php");

        $result = mysql_query("SELECT *, DATE_FORMAT( time, '%Y-%m-%d %H:%i:%s' )AS uk_date FROM $db_table ORDER BY time DESC LIMIT 3",$connect);
                //Limit news items to 3

        while($myrow = mysql_fetch_assoc($result))
              
             {//begin of loop

               //now print the results:
              echo "<hr>";
              
              echo "<br>";
        
               echo "<b> News Title:</b> ";

               echo $myrow['title'];

               echo "<br><br><b>News Posted On :</b> ";

               echo $myrow['time'];
		         
               echo "<br> <br>";
		         
		         echo "<b><td>News Description :</b><br><br></td>";
		         
               echo $myrow['description'];
   			       
                echo "<br>";
                
                echo "<hr>";

               // Now print the options to (Read,Edit & Delete the news)

               echo "<br><a href=\"read_more.php?newsid=$myrow[newsid]\">Read Further</a>
			 ||  <a href=\"add_news.php\">Add News</a>
			   
                || <a href=\"edit_news.php?newsid=$myrow[newsid]\">Edit</a>

                 || <a href=\"delete_news.php?newsid=$myrow[newsid]\">Delete</a><br><br>";
			 				  
             }

?>    

Looks fine to me, just grab the value of this column back in your PHP code.