How to select between with two fields

posted 6 years ago

I have some problem with my code for select shift_id with just give time, looks my records enter image description here

$waktu = explode(" ", $datetime);
    $rs = DB::table('otc_shift')
            ->whereNull('deleted_at')
            ->select('shift_id')
            ->whereBetween($waktu[1],['shift_time_start','shift_time_end'])
            ->first();
    return $rs->shift_id;

This code error


QueryException in Connection.php line 647:
SQLSTATE[42S22]: Column not found: 1054 Unknown column '13:26:33' in 'where clause' (SQL: select `shift_id` from `otc_shift` where `deleted_at` is null and `13:26:33` between shift_time_start and shift_time_end limit 1)

With my original query is:

select otc_shift.* from otc_shift WHERE '10:00:00' BETWEEN shift_time_start and shift_time_end;

Last updated 3 years ago.

pardeepkumartech

replied 6 years ago

let's try this

DB::table('table')
    ->select('column1', 'column2', 'column3')
    ->get();

abinadi

replied 6 years ago

You are using whereBetween incorrectly. The first parameter should be the column name.

It would be better to just use two "where" clauses. You should put:

$waktu = explode(" ", $datetime);
$rs = DB::table('otc_shift')
    ->select('shift_id')
    ->whereNull('deleted_at')
    ->where('shift_time_start','<',$waktu[1])
    ->where('shift_time_end','>',$waktu[1])
    ->first();
return $rs->shift_id;

Last updated 6 years ago.

omysurya

replied 6 years ago

@Pardeeptech this olution not for my case @abinadi result is null

Iwan to shot this result my query to implemented on Laravel. I want implemented manual query to Laravel like this

omysurya

replied 6 years ago

Perfecto.. @tezlopchan with this code:

->whereRaw("'$waktu[1]' BETWEEN shift_time_start AND shift_time_end")

But if i have $waktu[1] = '23:00:00' result is null..

Moderators