Problem with datetime format in sqlserver

Richi Ras commented on Mar 21, 2018

Hi, datetime field get converted in object and can't pass to any kind of pipe:

protected function setup()
{
    $this->src('sqlserver')
    ->query("
		SELECT mydatetimefield, textfield FROM mytable		
	")
	->pipe(new TimeBucket(array("mydatetimefield"=>"quarter")))		
    ->pipe(new Group(array("by"=>"mydatetimefield")))
    ->pipe($this->dataStore('amb_mod'));
}

Here comes the error:

Fatal error: Uncaught exception 'Exception' with message 'DateTime::construct() expects parameter 1 to be string, object given __

Printing out the $data received by timebucket results in something like this:

Array (

[mydatetimefield] => DateTime Object
    (
        [date] => 2018-03-09 00:00:00.000000
        [timezone_type] => 3
        [timezone] => Europe/Belgrade
    )

[textfield ] => 'text field content'

)

KoolReport commented on Mar 21, 2018

Since your datetime containing the milliseconds so you need to specify the format Y-m-d H:i:s.u like following:

$this->src('sqlserver')
->query("
    SELECT mydatetimefield, textfield FROM mytable		
")
->pipe(new ColumnMeta(array(
    "mydatetimefield"=>array(
        "type"=>"datetime",
        "format"=>"Y-m-d H:i:s.u"
    )
)))
->pipe(new TimeBucket(array("mydatetimefield"=>"quarter")))		
->pipe(new Group(array("by"=>"mydatetimefield")))
->pipe($this->dataStore('amb_mod'));

Let me know if it solves the issue.

Richi Ras commented on Mar 21, 2018

Thank you for your suggestion, it doesn't works, but I temporarly fixed by casting the field to VARCHAR(10). Is the format correct as you typed ?

Richi Ras commented on Mar 21, 2018

Also can't format the date using my fix. I think this is due to a non standard db setting, but I can't change it

KoolReport commented on Mar 21, 2018

Please try this:

$this->src('sqlserver')
->query("
    SELECT mydatetimefield, textfield FROM mytable		
")
->pipe(new Custom(function($data){
    $data["mydatetimefield"] = $data["mydatetimefield"]->format("Y-m-d H:i:s");
    return $data;
}))
->pipe(new ColumnMeta(array(
    "mydatetimefield"=>array(
        "type"=>"datetime",
        "format"=>"Y-m-d H:i:s"
    )
)))
->pipe(new TimeBucket(array("mydatetimefield"=>"quarter")))		
->pipe(new Group(array("by"=>"mydatetimefield")))
->pipe($this->dataStore('amb_mod'));

Basically since you received $data["mydatetimefield"] is DateTime object so you just need to convert to string before going to TimeBucket.

Richi Ras commented on Apr 4, 2018

Ok, thank you for your help !! It solves to display data table, but I'm still getting error to create chart.

ColumnChart::create(array(
    "dataStore"=>$this->dataStore('amb_mod'),  
    "columns"=>array(
		"mydatetimefield"=>array(
			"label"=>"DATA",
			"type"=>"datetime",
                       "format"=>"Y-n",
                       "displayFormat"=>"F, Y",			
			),		
        "myquantityfield"=>array(
            "label"=>"myquantityfield",
            "type"=>"number",
            "prefix"=>"N"
        )
		
    ),
    "width"=>"100%",
));

It gives me:

Data column(s) for axis #0 cannot be of type string

KoolReport commented on Apr 5, 2018

What is the format of the myquantityfield, if possible, please make sure it is number, we do the type conversion in the Custom process like following

->pipe(new Custom(function($data){
    $data["mydatetimefield"] = $data["mydatetimefield"]->format("Y-m-d H:i:s");
    $data["myquantityfield"] = intval($data["myquantityfield"]);
    return $data;
}))

Note: above code will convert to myquantityfield to integer, if the type of myquantityfield should be float, you use floatval()

Richi Ras commented on Apr 5, 2018

Great, it works !! Thank you !!

KoolReport's Forum

Problem with datetime format in sqlserver #232

Build Your Excellent Data Report