Search on MSSQL smalldatetime error

[simsar78]

Summary of problem or feature request

Hello, i have formatted a smalldate time to d-m-Y

Display and sort it's ok but, when i search returning nothing. No rows data.

This is my code:

Controller

$users = DelSind::
        selectRaw("users.Codice, users.Nome, users.DataNasc")
        ->leftJoin('users','product.CodOpe','users.Codice')
        ->where('product', 1)
        ->whereNull('product.deleted)
        ->limit(120)
        ->get();

        return DataTables::of($users)
        ->editColumn('users.DataNasc', function ($model) {
            return [
                'display' =>($model->DataNasc && $model->DataNasc != '0000-00-00 00:00:00') ? with(new Carbon($model->DataNasc))->format('d-m-Y') : '',
                'timestamp' =>($model->DataNasc && $model->DataNasc != '0000-00-00 00:00:00') ? with(new Carbon($model->DataNasc))->timestamp : ''
            ];
        }
        )

        ->make(true);

Blade view

$(document).ready(function() {
            var table = $('#table_id').DataTable({
                processing: true,
                serverSide: true,
                type: 'POST',
                ajax: {
                    url: "http://localhost:8001/api/v1/users",
                    method: 'POST'
                },
                columns: [
                    {data: 'Codice', name: 'users.Codice'},
                    {data: 'Nome', name: 'users.Nome'},
                    {data: { _: 'users.DataNasc.display', sort: 'users.DataNasc' }, name: 'users.DataNasc.timestamp', type: 'date'},

                ],
                columnDefs: [{
                defaultContent: "<span class='text-disabled'>-</span>",
                targets: "_all"
            }],
            });
        });

System details

• Operating System OS X
• PHP Version 8.1
• Laravel Version 9
• Laravel-Datatables Version 10.1.2

[simsar78]

I have customized my code with this:

Controller:

 $operai_CU = DelSind::selectRaw("Operai.Codice, Operai.Cognome + ' ' + Operai.Nome as nominativo, Operai.DataNasc, Operai.LuogoNasc, Operai.Sesso, Operai.CodFisc, Operai.Cellulare, Operai.CodPaese, Operai.CodCheck, Operai.CodCin, Operai.CodAbi, Operai.CodCab, Operai.CodCC,
        ( SELECT ragSoc FROM Imprese WHERE Codice=dbo.LastAziOpe(Operai.Codice) ) AS UltImpresa,
        ( SELECT TOP 1 Codice FROM CU WHERE CodOpe=Operai.Codice ) AS CodCU")
            ->leftJoin('Operai', 'DelSind.CodOpe', 'Operai.Codice')
            ->where('CodSind', 1)
            ->whereNull('DelSind.DataFineVal');
            // ->limit(12000)
            // ->query();


        return DataTables::of($operai_CU)
            // NOMINATIVO
            // ->editColumn('nominativo', function ($model) {
            //     return $model->Cognome . ' ' . $model->Nome;
            // })
            ->filterColumn('nominativo', function($query, $keyword) {
                $query->whereRaw("CONCAT(Operai.Cognome, ' ', Operai.Nome) LIKE ?", ["%{$keyword}%"]);
            })
            // IBAN
            ->editColumn('iban', function ($model) {
                if ($model->CodPaese != '' && $model->CodCheck != '' && $model->CodCin != '' && $model->CodAbi != '' && $model->CodCab != '' && $model->CodCC != '') {
                    $iban = '<span class="badge bg-success">iban</span>';
                    return $iban;
                } else {
                    $iban = '<span class="badge bg-danger">iban</span>';
                    return $iban;
                }
            })
            // DATA NASCITA
            ->editColumn('Operai.DataNasc', function ($model) {
                return [
                    'display' => ($model->DataNasc && $model->DataNasc != '0000-00-00 00:00:00') ? with(new Carbon($model->DataNasc))->format('d-m-Y') : '',
                    'timestamp' => ($model->DataNasc && $model->DataNasc != '0000-00-00 00:00:00') ? with(new Carbon($model->DataNasc))->timestamp : ''
                ];
            })
            // ULTIMA IMPRESA
            // ->editColumn('ultima_imp', function ($model) {
            //     return $model->UltImpresa;
            // })
            ->filterColumn('UltImpresa', function($query, $keyword) {
                $query->whereRaw("( SELECT ragSoc FROM Imprese WHERE Codice=dbo.LastAziOpe(Operai.Codice) ) LIKE ?", ["%{$keyword}%"]);
            })
            // C U
            ->editColumn('CodCU', function ($model) {
                $cod_cu = $model->CodCU;
                if ($cod_cu == null) {
                    $cod_cu = '<button class="btn btn-sm btn-outline-secondary disabled" disabled><i class="fas fa-file-alt"></i></button>';
                    return $cod_cu;
                } else {
                    $cod_cu = '<button data-id="' . $model->Codice . '" class="btn_list_cu btn btn-sm btn-info" ><i class="fas fa-file-alt"></i></button>';
                    return $cod_cu;
                }
            })
            ->filterColumn('CodCU', function($query, $keyword) {
                $query->whereRaw("( SELECT TOP 1 Codice FROM CU WHERE CodOpe=Operai.Codice ) LIKE ?", ["%{$keyword}%"]);
            })
            ->rawColumns(['iban', 'CodCU'])
            ->make(true);

Blade View:

 $(document).ready(function() {
            var table = $('#table_id').DataTable({
                processing: true,
                serverSide: true,
                ajax: {
                    url: "http://localhost:8045/api/v1/sindacato/v2/iscritti/list",
                    method: 'POST'
                },
                columns: [
                    {data: 'Codice', name: 'Codice'},
                    {data: 'nominativo', name: 'nominativo'},
                    {data: { _: 'Operai.DataNasc.display', sort: 'Operai.DataNasc' }, name: 'Operai.DataNasc.timestamp', type: 'date'},
                    {data: 'LuogoNasc', name: 'Operai.LuogoNasc'},
                    {data: 'Sesso', name: 'Operai.Sesso'},
                    {data: 'CodFisc', name: 'Operai.CodFisc'},
                    {data: 'Cellulare', name: 'Operai.Cellulare'},
                    {data: 'iban', name: 'Operai.CodPaese'},
                    {data: 'UltImpresa', name: 'UltImpresa'},
                    {data: 'CodCU', name: 'CodCU'}
                ],
                columnDefs: [{
                defaultContent: "<span class='text-disabled'>-</span>",
                render: function(data,type,row,meta) {
                    if( data === null || ( data.length === 0 ) ){
                        return "<span class='text-disabled'>-</span>"
                    }
                    return data;
                },
                targets: "_all"
            }],
            });
        });

Now have problem to sort and search.

Error:

SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot call methods on smalldatetime. (SQL: select top 10 Operai.Codice, Operai.Cognome + ' ' + Operai.Nome as nominativo, Operai.DataNasc, Operai.LuogoNasc, Operai.Sesso, Operai.CodFisc, Operai.Cellulare, Operai.CodPaese, Operai.CodCheck, Operai.CodCin, Operai.CodAbi, Operai.CodCab, Operai.CodCC,
        ( SELECT ragSoc FROM Imprese WHERE Codice=dbo.LastAziOpe(Operai.Codice) ) AS UltImpresa,
        ( SELECT TOP 1 Codice FROM CU WHERE CodOpe=Operai.Codice ) AS CodCU from [DelSind] left join [Operai] on [DelSind].[CodOpe] = [Operai].[Codice] where [CodSind] = 1 and [DelSind].[DataFineVal] is null order by [Operai].[DataNasc].[timestamp] asc)

[github-actions[bot]]

This issue is stale because it has been open for 30 days with no activity.

[yajra]

Seems like a generated SQL Issue, try using filterColumn.

[github-actions[bot]]

This issue is stale because it has been open for 30 days with no activity. Remove stale label or comment or this will be closed in 7 days.

[github-actions[bot]]

This issue was closed because it has been inactive for 7 days since being marked as stale.