Binu Thayamkery - 309 - Feb 22, 2014

[4.0 UPGRADE ISSUE] I am an error “operand type clash bigint is incompatible with time” when i try to insert an object with a timespan column to table with corresponding time datatype column. 

Did a bit of digging, looks like SqlServerOrmLiteDialectProvider in 3.0 uses TIME dbtype and in 4.0 it is changed to INTEGER -> I think this breaks stuff!!

Has anyone had issues similar to this? Solutions ? Workarounds? 
Appreciate your help - Thanks

Yeah TIME work very inconsistently across DB’s and as a result TimeSpan was supported poorly. It was changed to use ticks for all db providers.

Binu Thayamkery:

Is there a way to workaround my error?

So you’d want to change the column data type to be INT…

The way I would do it is create a new INT column in SqlServer, then use the low-level ADO.NET API to read the original column as a TimeSpan, then for each row create a manual update statement to populate the new column with the (TimeSpan).Ticks value. If that all ran smoothly, delete the old TIME column and rename the new INT column with the old ones name.

Binu Thayamkery:

I was afraid you were going to say this (that was my hail mary pass :wink:
Thanks anyway. 

FYI, created a simple example of what I meant in code:

As seen in the example, you can create a POCO in OrmLite that’s only a subset of the Table definition and OrmLite will be able to happily work with the subset, so you can keep the old SqlServer column around if you exclude it from OrmLite’s POCO data model.

Binu Thayamkery:

Thank you!