Help on time stamp and reports

[at_rcc]

Hi i have the following sql which works perfectly in a form but when i try to use the same in report it is not working
secondly how do we compare date when the field is time stamp ?

SELECT *
FROM customer
INNER JOIN transaction on customer.customer_id = transaction.tra_customer_id
LEFT JOIN transaction_item on transaction_item.tri_transaction_id = transaction.transaction_id
LEFT JOIN tbl_batch on tbl_batch.batch_id = transaction_item.batch_id

===============================================>

$toDate = msql_date_nq('#vTo#');
$fromDate = msql_date_nq('#vFrom#');



$sql =
<<<EOSQL

CREATE TABLE #dataTable#
SELECT *
FROM customer
INNER JOIN transaction on customer.customer_id = transaction.tra_customer_id
LEFT JOIN transaction_item on transaction_item.tri_transaction_id = transaction.transaction_id
LEFT JOIN tbl_batch on tbl_batch.batch_id = transaction_item.batch_id


EOSQL;

nuRunQuery($sql);
---------------------------------------------->
help would be very much appreciated

[steven]

at_rcc,

I think your first problem might be because using * will use every field, but if you do this with CREATE TABLE, you might have the same named fields somewhere within the tables transaction_item, customer, tbl_batch or transaction, and you can't create a table with duplicate fieldnames.

Secondly use format_date() in mysql http://dev.mysql.com/doc/refman/5.1/en/ ... ate-format

SELECT DATE_FORMAT( '1997-10-04 22:23:00' , '%Y-%m-%d' )

Steven

[at_rcc]

THANKS STEEVEN FOUND SOLUTION INDEED