Changing date format when doing MySQL query? 
Author Message
 Changing date format when doing MySQL query?

Here is my code:

$sql = "SELECT * FROM SECURE_SERVICES_customers WHERE
customer_number=$MM_Username";

$result = mysql_query($sql) or die("Query failed: " . mysql_error());

$row = mysql_fetch_array($result);

$company_name = "$row[company_name]";
$date = "$row[date]";

The date comes out as 2003-01-24. What is the correct syntax of the
"$row[date]" bit that makes the date a European 24-01-2003?

Thanks,
Lee.



Wed, 13 Jul 2005 23:49:29 GMT  
 Changing date format when doing MySQL query?

Quote:

> Here is my code:

> $sql = "SELECT * FROM SECURE_SERVICES_customers WHERE
> customer_number=$MM_Username";

> $result = mysql_query($sql) or die("Query failed: " . mysql_error());

> $row = mysql_fetch_array($result);

> $company_name = "$row[company_name]";
> $date = "$row[date]";

> The date comes out as 2003-01-24. What is the correct syntax of the
> "$row[date]" bit that makes the date a European 24-01-2003?

> Thanks,
> Lee.

Hi,

Use MySQL DATE_FORMAT() function :

http://www.mysql.com/doc/en/Date_and_time_functions.html#IDX1281

Frederic Maybaum



Wed, 13 Jul 2005 18:25:58 GMT  
 Changing date format when doing MySQL query?


Quote:
> The date comes out as 2003-01-24. What is the correct syntax of the
> "$row[date]" bit that makes the date a European 24-01-2003?

You can set the format in the SQL-query, before PHP sees it.

SELECT DATE_FORMAT(date,'%e-%c-%Y') FROM ...

See <http://www.mysql.com/doc/en/Date_and_time_functions.html>
for the official docs.

JP

--




Thu, 14 Jul 2005 01:44:43 GMT  
 Changing date format when doing MySQL query?


Quote:
> Here is my code:

> $sql = "SELECT * FROM SECURE_SERVICES_customers WHERE
> customer_number=$MM_Username";

> $result = mysql_query($sql) or die("Query failed: " . mysql_error());

> $row = mysql_fetch_array($result);

> $company_name = "$row[company_name]";
> $date = "$row[date]";

> The date comes out as 2003-01-24. What is the correct syntax of the
> "$row[date]" bit that makes the date a European 24-01-2003?

> Thanks,
> Lee.

If I recall correctly you can instruct MySQL to output a date/time field as
a string in any manner you wish:

SELECT DATE_FORMAT(date,'%d %m %Y') FROM table

which would displayin the format you refer to, ie 2 digit day of month, 2
digit month, 4 digit year

check the follwoing URL for more info:
http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.h...
e_and_time_types



Thu, 14 Jul 2005 02:14:16 GMT  
 Changing date format when doing MySQL query?


Quote:

> $sql = "SELECT * FROM SECURE_SERVICES_customers WHERE
> customer_number=$MM_Username";

> $result = mysql_query($sql) or die("Query failed: " . mysql_error());

> $row = mysql_fetch_array($result);

> $company_name = "$row[company_name]";
> $date = "$row[date]";

> The date comes out as 2003-01-24. What is the correct syntax of the
> "$row[date]" bit that makes the date a European 24-01-2003?

First, you need to understand that what you've got right now is
really not a date (meaning, not a Unix timestamp); it's a string.  
So if you want a simple workaround, you can do this:

$date_array = explode ('-', $row['date']);
$date = $date_array[2].'-'.$date_array[1].'-'.$date_array[0];

Alternatively, you can do this:

$date = date ('d-m-Y', strtotime ($row['date']));

Cheers,
NC



Thu, 14 Jul 2005 05:25:49 GMT  
 Changing date format when doing MySQL query?

Quote:
>First, you need to understand that what you've got right now is
>really not a date (meaning, not a Unix timestamp); it's a string.  
>So if you want a simple workaround, you can do this:

>$date_array = explode ('-', $row['date']);
>$date = $date_array[2].'-'.$date_array[1].'-'.$date_array[0];

>Alternatively, you can do this:

>$date = date ('d-m-Y', strtotime ($row['date']));

>Cheers,
>NC

Cheers NC, the explode function worked a treat.

Bye,
Lee.



Fri, 15 Jul 2005 02:08:35 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. MySQL query in PHP: Not query zero amounts also removes null amounts from query

2. Mysql query prob - dates

3. Unix timestamp, formatted date and MySQL

4. Formatting dates from MySQL

5. mysql and DATE format

6. CW2.003 File copy changes the date.....need a way to copy w/o date change

7. Change date format in exe

8. Changing a Date Format

9. date format change

10. Programmatically change time/date format on a graph/chart

11. Invalid dates - 215 using Btrieve/Clipper DATE format

12. Date format in itk date widgets

 

 
Powered by phpBB® Forum Software