Billy Origin Billy Origin - 25 days ago 9
MySQL Question

Laravel how to convert from created_at (timestamp) to date with query builder?

I tried to convert created_date to date with query builder, but I have no luck.
I know I can convert it in view with blade, but I need this in the query because I want to use it for dataTables function.
I tried mysql code manually, it's work normally .

here my code:

$convert = \DB::raw("SELECT DATE_FORMAT((created_at), '%e %c %Y') AS tanggal");
$bonus = bonus_transaction::select('id', "$convert", 'transaction_type', 'amount_usd_out' , 'status_withdraw')
->where('transaction_type', 'Reinvest')->get();

I got error like this

"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'SELECT DATE_FORMAT((created_at), %e %c %Y)' in 'field list' (SQL: select `id`, `SELECT DATE_FORMAT((created_at), %e %c %Y)` as `tanggal`, `transaction_type`, `amount_usd_out`, `status_withdraw` from `bonus_transaction_log` where `transaction_type` = Reinvest)"

Answer Source

Try to remove SELECT as you are already uisng select() in laravel, change here

$convert = \DB::raw("DATE_FORMAT('created_at', '%e %c %Y') as tanggal");