mercredi 23 novembre 2016

Laravel and SQL Server - Inserting datetime the right way

I have an PHP application that inserts in a SQL Server database. Everything is inserted perfect in a table. I have another application that have been wrote in .NET that reads the database (I dont have the source code of this app). When I insert a new record from Laravel, the .NET app dont see this record. But if I edit this record datetime fields manually in the database the record is shown in the .NET app. I am confused about why is this happening.

PHP code:

        $input = $request->all();
        $input['mark_day'] = Date('Ymd H:i:s', strtotime($input['date']." 00:00:00.000"));
        $input['mark_hour'] = Date('Ymd H:i:s', strtotime($input['date']." ".$input['hour']));
        $rArray = array("status"=>"","message"=>"");
        try{
            date_default_timezone_set('Europe/Madrid');
            $datetime_variable = new DateTime();
            $datetime_formatted = date_format($datetime_variable, 'Y-m-d\TH:i:s');

            if($input['function'] == "play"){
                $mark_type = 1;
            }elseif($input['function'] == "stop"){
                $mark_type = 0;
            }

            $id_customer = env('DB_DEFAULTS_CUSTOMER', 119);
            $id_center = env('DB_DEFAULTS_CENTER', 0);
            $id_door = env('DB_DEFAULTS_DOOR', 1);
            //$mark_day = DB::raw("CAST('".$input['mark_day']."' AS DATETIME2)");
            //$mark_date_time = DB::raw("CAST('".$input['mark_hour']."' AS DATETIME2)");
            $mark_day = "'".$input['mark_day']."'";
            $mark_date_time = "'".$input['mark_hour']."'";
            $source_type = 3;
            $id_actor = $input['actorID'];
            $id_enterprise = env('DB_DEFAULTS_ENTERPRISE', 1);
            $incidence_validated = env('DB_DEFAULTS_VALIDATED', 1);
            $is_active = 1;
            $id_cre = -1;
            $dt_cre = "'".Date('Ymd H:i:s')."'";
            $version = env('DB_DEFAULTS_VERSION', 1);
            $sql = "insert into [tpc_marks] ([id_customer], [id_center], [id_door], [mark_day], [mark_date_time], [source_type], [mark_type], [id_actor], [id_enterprise], [incidence_validated], [is_active], [id_cre], [dt_cre], [version], [notes]) values ($id_customer, $id_center, $id_door, $mark_day, $mark_date_time, $source_type, $mark_type, $id_actor, $id_enterprise, $incidence_validated, $is_active, $id_cre, $dt_cre, $version, '')"; 
            $insertResult = DB::connection('tecisa')->unprepared($sql);

            $rArray["status"]=true;
            $rArray["message"]="Introducido en la base de datos";
            $rArray['sentencia'] = $sql;
            return response()->json($rArray);
        }catch(Exception $e){
            $rArray["status"]=false;
            $rArray["message"]=$e;
            return response()->json($rArray);
        }

Before executing this code, .NET shows nothing. If I go to SQL Server and update the record (with the same datetime), .NET shows it. How can I fix this?



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire