Skip to content
Advertisement

DB:Raw not working for date format in postgres

I have the well executed postgres query.I have tried to convert this in laravel

but query not executed

select
    id,
    month,
    meeting_start_date,
    call_start_date
from mgl_report_targets  
where extract(
    Month from to_date(mgl_report_targets.month, '%Month-%YYYY')
) 
in (
    extract(MONTH from meeting_start_date), extract(MONTH from call_start_date)
);

try 1

$db = DB::table('mgl_report_targets as t');
$db = $db->where(function ($query) {
     return $query->whereIn(DB::Raw("extract(MONTH from meeting_start_date)"), DB::Raw("extract(Month from to_date('t.month', %Month-%YYYY))"))
     ->orWhereIn(DB::Raw("extract(MONTH from call_start_date)"), DB::Raw("extract(Month from to_date('t.month', %Month-%YYYY))"));
})
->select('id','month','meeting_start_date','call_start_date')->get();

But no luck.getting Parse error: syntax error, unexpected ‘}’, expecting ‘;’

Try 2

$db = DB::table('mgl_report_targets as t')
->where(DB::Raw("extract(Month from to_date(mgl_report_targets.month, '%Month-%YYYY')) in (extract(MONTH from meeting_start_date), extract(MONTH from call_start_date))"))
->select('id','month','meeting_start_date','call_start_date')
->get();

Nothing return from my try 2

Advertisement

Answer

I don’t know if the SQL itself is alright since I’m not that experienced with postgres, but your query could be written like this in the Query Builder. You can verify it outputs the exact same query by dumping the $sql variable.

$query = DB::table('mgl_report_targets')
->select('id', 'month', 'meeting_start_date', 'call_start_date')
->whereRaw("extract(month from to_date(mgl_report_targets.month, '%Month-%YYYY')) in (extract(month from meeting_start_date), extract(month from call_start_date))");

$results = $query->get();

$sql = $query->toSql();
User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement