format date inside query

[tagteamcomputing]

I am trying to format the date from a timestamp to m/d/Y inside the query.

 

What I am trying ends up returning a blank result for the date

stmt = $conn->prepare("SELECT orderid, DATE_FORMAT(date, '%m/%d/%Y') as orderdate, email, b_state, s_country, subtotal FROM xcart_orders where date BETWEEN $BeginDate AND $EndDate ");
 

I am a newbie to php and even more so to php 7 and PDO so i apologize if this is a simple one.

[ginerjm]

I'm going to guess that your fieldname of "date" is being mistaken for something else as it is probably a reserved work in MySQL. You should not have used it but since you already have it defined, wrap it in back-ticks and try again.

[Barand]

I assume when you say "timestamp" you are referring to a unix timestamp (integer) and not a mysql timestamp (yyyy-mm-dd hh:ii:ss).

 

In which casa you need to first convert to mysql datetime format before you can use date_format(). Store date/time info in a database using DATE, DATETIME or TIMESTAMP type columns, not integer. They are readable and wor with the functions.

,,, DATE_FORMAT(FROM_UNIXTIME(date), '%m/%d/%Y') as orderdate

[tagteamcomputing]

the date is the name of the field in the table (an ecommerce app I did not create) and yes it is a unix time stamp.

 

I tried what you suggested and it worked great.