CODEWITHSUNDEEP
javascriptphpmysqllaravelfullcalendar
Rachel
Rachel

Reputation: 63

SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value

I'm implementing full calendar API into my project .I have a problem when I try to save an event , I keep on getting the following error:

SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2017-07-1415:41:03' for column 'start' at row 1 (SQL: insert into `event3s` (`title`, `start`, `end`, `color`) values (New event, 2017-07-1415:41:03, , #9e1515))

I have changed to the right time(time zone) in phpmyadmin but I still got the same error.

This is my table in phpmyadmin : [![enter image description here][1]][1]

This is the calendar.blade.php class :

<!doctype html>
<html lang="{{ config('calendar.locale') }}">
<head>

    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <meta name="csrf-token" content="{{ csrf_token() }}">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>
    <title>Full Calendar</title>

    {!! Html::style('vendor2/seguce92/bootstrap/css/bootstrap.min.css') !!}
    {!! Html::style('vendor2/seguce92/fullcalendar/fullcalendar.min.css') !!}
    {!! Html::style('vendor2/seguce92/bootstrap-datetimepicker/css/bootstrap-material-datetimepicker.css') !!}
    {!! Html::style('vendor2/seguce92/bootstrap-colorpicker/css/bootstrap-colorpicker.min.css') !!}

</head>
<div id='app'></div>
<div class='container'></div>

{{ Form::open(['route' => 'user.events.store', 'method' => 'post', 'role' => 'form']) }}
<div id="responsive-modal" class="modal fade" tabindex="-1" data-backdrop="static"->

    <div class="modal-dialog">
        <div class="modal-content">

        <div class="modal-header">
        <h4>Registration of new Event</h4>

        </div>

        <div class="modal-body">

            <div class="form-group">
                {{Form::label('title','Date Start') }}
                {{Form::text('title',old('title'),['class' => 'form-control'])}}
            </div>

        <div class="form-group">
            {{Form::label('date_start','Date Start') }}
            {{Form::text('date_start',old('date_start'),['class' => 'form-control','readonly'=>'true'])}}
        </div>

            <div class="form-group">
                {{Form::label('time_start','Time Start') }}
                {{Form::text('time_start',old('time_start'),['class' => 'form-control'])}}
            </div>

            <div class="form-group">
                {{Form::label('date_end','Time Finish') }}
                {{Form::text('date_end',old('date_end'),['class' => 'form-control'])}}
            </div>

            <div class="form-group">
                {{Form::label('color','COLOR') }}
                <div class="input-group colorpicker">
                    {{ Form::text('color',old('color'),['class' => 'form-control']) }}
                    <span class ="input-group-addon">

                        <i></i>
                    </span>
                </div>
            </div>
        </div>

        <div class="modal-footer">
            <button type="button" class="btn btn-default" data-dismiss="modal">Cancel</button>
            {!! Form::submit('Save',['class' => 'btn-btn-success'])!!}

        </div>
    </div>
    </div>

</div>
{{Form::close()}}
<div id='calendar'></div>

</body>

{!! Html::script('vendor2/seguce92/jquery.min.js') !!}
{!! Html::script('vendor2/seguce92/bootstrap/js/bootstrap.min.js') !!}
{!! Html::script('vendor2/seguce92/fullcalendar/lib/moment.min.js') !!}
{!! Html::script('vendor2/seguce92/fullcalendar/fullcalendar.min.js') !!}
{!! Html::script('vendor2/seguce92/bootstrap-datetimepicker/js/bootstrap-material-datetimepicker.js') !!}
{!! Html::script('vendor2/seguce92/bootstrap-colorpicker/js/bootstrap-colorpicker.min.js') !!}
    <script type="text/javascript">
    var BASEURL="{{url('/')}}";
        $(document).ready(function() {

            $('#calendar').fullCalendar({
                header: {
                    left: 'prev,next today',
                    center: 'title',
                    right: 'month,basicWeek,basicDay'
                },

                navLinks: true, // can click day/week names to navigate views
                editable: true,
                selectable:true,
                selectHelper:true,

                select:function(start){
                    start = moment(start.format());

                    $('#date_start').val(start.format('YYYY-MM-DD'));
                    $('#responsive-modal').modal('show');
                },
                events: BASEURL+'/events'

            });

        });

        $('.colorpicker').colorpicker();

        $('#time_start').bootstrapMaterialDatePicker({

                date:false,
                shortTime: false,
                format: 'HH:mm:ss'
        });

    $('#date_end').bootstrapMaterialDatePicker({

        date:true,
        shortTime: false,
        format: 'YYYY-MM-DD'
    });

One thing to note is that I had to create the table through phpmyadmin ,I was not able to migrate table from my code .

Also I have tried to change YYYY-MM-DD to Y-m-d H:i:s

This is my migration class:

class CreateEvent3sTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('event3s', function (Blueprint $table) {
            $table->increments('id');
            $table->string('title');
            $table->datetime('start');
            $table->datetime('end');
            $table->string('color', 7);
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('event3s');
    }
}

This is my controller where the saving process is meant to happen:

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Event3;
class EventsController extends Controller
{
    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function index()
    {
        $data = Event3::get(['title','start','end','color']);

       return Response()->json($data);
    }

    /**
     * Show the form for creating a new resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function create()
    {
        //
    }

    /**
     * Store a newly created resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @return \Illuminate\Http\Response
     */
    public function store(Request $request)
    {
       $event = new Event3();
       $event->title = $request->title;
       $event->start = $request->date_start .''. $request->time_start;
       $event->end = $request->data_end;
       $event->color = $request->color;
        $event->save();

        return redirect('/');
    }

    /**
     * Display the specified resource.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function show($id)
    {
        //
    }

    /**
     * Show the form for editing the specified resource.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function edit($id)
    {
        //
    }

    /**
     * Update the specified resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function update(Request $request, $id)
    {
        //
    }

    /**
     * Remove the specified resource from storage.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function destroy($id)
    {
        //
    }
}

Upvotes: 0

Views: 10962

Answers (4)

Maizied Hasan Majumder
Maizied Hasan Majumder

Reputation: 1053

Try this ->

Easiest way to insert data.

$item->date= date('Y-m-d H:i:s', strtotime($request->date));

Upvotes: 1

Chris
Chris

Reputation: 3486

The problem lies within your store() method:

$event->start = $request->date_start .''. $request->time_start;

As you can see there is no whitespace in between the date_start and time_start. Simply change it to 

$event->start = $request->date_start .' '. $request->time_start;

There is also an issue with the date_end. Change this line:

$event->end = $request->data_end;

to 

$event->end = $request->date_end;

And it should enter into your database without an error!

Upvotes: 1

Rahul
Rahul

Reputation: 77896

MySQL retrieves and displays DATETIME values in YYYY-MM-DD HH:MM:SS. Looks like you are missing a space in your datetime value and it should be '2017-07-14 15:41:03' instead

Upvotes: 0

Cornelius Fillmore
Cornelius Fillmore

Reputation: 123

take a closer look at the error message ...datetime value: '2017-07-1415:41:03' for... The date and time values are not separated by a whitespace.

It would be very helpful to see the php-script which actually tries to write into the database

Upvotes: 1

Related Questions