Date Format

[RidgeandGable]

Hi Guys

 

In MYSQL the date format seems to be YYYY-MM-DD, when I use a dynamic table in Dreamweaver the date is also displayed this way, is there a simple way to change this to UK format DD-MM-YYYY?

[mac_gyver]

you can either do this in the database query, using mysql's DATE_FORMAT() function, or you can do this in your php code, using php's datetime class, format() method.

[RidgeandGable]

Thanks, can you give example for doing it via mysql? I've checked the manual for this but couldn't quiet understand where to put it

[Barand]

SELECT DATE_FORMAT(mydatefield, '%d-%m-%Y') as formattedDate

 

If you are sorting or comparing dates, use the original and not the formatted version

[RidgeandGable]

This is what I have in php 

Displaying the Field: 
<td><div align="center"><?php echo $row_Invoices['duebydate']; ?></div></td>

Select Statement
mysql_select_db($database_Harry, $Harry);
$query_Invoices = sprintf("SELECT * FROM invoices WHERE username = '%s' ORDER BY invoicedate DESC", $colname_Invoices);
$query_limit_Invoices = sprintf("%s LIMIT %d, %d", $query_Invoices, $startRow_Invoices, $maxRows_Invoices);
$Invoices = mysql_query($query_limit_Invoices, $Harry) or die(mysql_error());
$row_Invoices = mysql_fetch_assoc($Invoices);

How would I use your code in this?

[Barand]

Don't use SELECT *.

Specify the fields you need, then it becomes simple to add in the DATE_FORMAT()... as invdate

[RidgeandGable]

So it would become Select Date_Format("[iD],[username],[Date,'%d-%m-%Y]")   ?

 

Edited November 21, 2014 by RidgeandGable

[RidgeandGable]

SELECT id,username,InvoiceNumber,DATE_FORMAT(invoicedate,%d-%m-%y)

Edited November 21, 2014 by RidgeandGable

[RidgeandGable]

When I use the above code, I get an error for using sprintf

[Barand]

$query_Invoices = sprintf("SELECT id,username,InvoiceNumber,DATE_FORMAT(invoicedate,%d-%m-%y) as date

         FROM invoices

         WHERE username = '%s'

         ORDER BY invoicedate DESC", $colname_Invoices);

[RidgeandGable]

Thanks Sen

 

Have tried what you put but still puts the same error out

 

mysql_select_db($database_Harry, $Harry);

$query_Invoices = sprintf("SELECT id,username,DATE_FORMAT(invoicedate,%d-%m-%y) as date,DATE_FORMAT(duebydate,%d-%m-%y) as date,description,invoicenumber,download,paid,DATE_FORMAT(datepaid,%d-%m-%y) as date

         FROM invoices

         WHERE username = '%s'

         ORDER BY invoicedate DESC", $colname_Invoices);

$query_limit_Invoices = sprintf("%s LIMIT %d, %d", $query_Invoices, $startRow_Invoices, $maxRows_Invoices);

$Invoices = mysql_query($query_limit_Invoices, $Harry) or die(mysql_error());

$row_Invoices = mysql_fetch_assoc($Invoices);

This is row 20 -  ORDER BY invoicedate DESC", $colname_Invoices);

Warning: sprintf(): Too few arguments in C:\xampp\htdocs\rgroofing\Invoices.php on line 20
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 0, 10' at line 1

Edited November 21, 2014 by RidgeandGable

[CroNiX]

Well, yes. sprintf() is converting the %[identifiers], which mysql is also using. So don't use sprintf() when using those mysql functions that contain %[identifier]. If you use prepared statements for your queries, this will be a non-issue plus they are way better to use anyway.

[RidgeandGable]

Ok, so is it as easy as removing all the sprintf statements?

[Barand]

Sorry, I copied your code in my last post. The format definition should be in quotes, as in my original post (#4)

 

DATE_FORMAT(invoicedate,'%d-%m-%y').

 

And don't give them all the same alias (" as date") as you cannot then access them with $row['date']

[RidgeandGable]

ok did that with the quotes but still got the error. Think I'll just leave it lol, I dont mind it being in reverse just gotta remember it when I'm entering the data. Cheers though

[mac_gyver]

if you are going to use sprintf() to build your query string, you would need to double the %% in the DATE_FORMAT() format string so that they are treated as literal % instead of a sprintf() format specifier.

 

however, if you are using sprintf() to build your query strings, you might as well just switch to prepared queries. the %s, %d, ... sprintf() format specifiers are where you would put the prepared query place-holders, so all you would have to do is change them to ?, prepare the query, bind any input data (which are the argument(s) in the sprintf() statement), and execute the query.