unable to convert MySQL data/time value to System.DateTime

unable to convert MySQL data/time value to System.DateTime

  

Hi I'm trying to an external database I can see everything is working all the why to the "fetch of the database" when I'm under the view data.


This is the error I get:

unable to convert MySQL data/time value to System.DateTime


the MySql databse is the same format as the outsystems intergration 


anybody know how to fix this?

Hi Lasse,


I did a test similar to your description and I've got no error.


Can you provide more info? Do you have a stacktrace / error detail?


Here's how I did it:

DB (hosted at Amazon AWS)

MySQL Workbench table definition


Extension


Test (View Data)


Cheers,

Renato


Lasse Frederiksen wrote:

Hi I'm trying to an external database I can see everything is working all the why to the "fetch of the database" when I'm under the view data.


This is the error I get:

unable to convert MySQL data/time value to System.DateTime


the MySql databse is the same format as the outsystems intergration 


anybody know how to fix this?



Hi Renato thx for the fast answer and testing. 


Let me see if I can answer in a way that make sense I'm very newbe with this software :)


I use OutSystems Integration Studio 10.0 to import my external database with users. the connection in the ServiceCenter works! I can fetch data by connecting the OutSystems Integration Studio and then import it, in to my Mobil app. But in the MySql I have a column with dates. but some of the dates in the column is empty or have a 0000-00-00 I think that is the problem because if I don't import that column, I don't get an error can fetch the database. I search around on google and found that when people have that problem they have to add some code telling it to allow data's that is like mine: "Allow Zero Datetime" but I have no clue where I should write that command or if it will solve the problem.


I have also tried to set the DBMS to MySql but that give an error I have to use (all)


What are you using to make the connection between the service ServiceCenter and to outsystems service studio? 



Here you can see my connection path:



Then one column that is highlightet is the one that makes the problem:







here is the problem:





Thank you for trying to help me out :)


Best regards


Lasse

Ok, with your details I was able to reproduce your error  . Great!


I did that by inserting some invalid values:



If I convert the "0000-00-00" to "NULL" everything works fine:



Is it possible for you to to change the mysql original value "0000-00-00" to "1900-01-01" or to NULL? 


Cheers,

Renato


Lasse Frederiksen wrote:

Hi Renato thx for the fast answer and testing. 


Let me see if I can answer in a way that make sense I'm very newbe with this software :)


I use OutSystems Integration Studio 10.0 to import my external database with users. the connection in the ServiceCenter works! I can fetch data by connecting the OutSystems Integration Studio and then import it, in to my Mobil app. But in the MySql I have a column with dates. but some of the dates in the column is empty or have a 0000-00-00 I think that is the problem because if I don't import that column, I don't get an error can fetch the database. I search around on google and found that when people have that problem they have to add some code telling it to allow data's that is like mine: "Allow Zero Datetime" but I have no clue where I should write that command or if it will solve the problem.


I have also tried to set the DBMS to MySql but that give an error I have to use (all)


What are you using to make the connection between the service ServiceCenter and to outsystems service studio? 



Here you can see my connection path:



Then one column that is highlightet is the one that makes the problem:







here is the problem:





Thank you for trying to help me out :)


Best regards


Lasse



If you can't change the original values and don't need to write on the original table from OutSystems, you can always create a view that replaces "0000-00-00" with NULL.



In Integration Studio replace the table name by the view:



Cheers,

Renato


Renato Torres wrote:

Ok, with your details I was able to reproduce your error  . Great!


I did that by inserting some invalid values:



If I convert the "0000-00-00" to "NULL" everything works fine:



Is it possible for you to to change the mysql original value "0000-00-00" to "1900-01-01" or to NULL? 


Cheers,

Renato


Lasse Frederiksen wrote:

Hi Renato thx for the fast answer and testing. 


Let me see if I can answer in a way that make sense I'm very newbe with this software :)


I use OutSystems Integration Studio 10.0 to import my external database with users. the connection in the ServiceCenter works! I can fetch data by connecting the OutSystems Integration Studio and then import it, in to my Mobil app. But in the MySql I have a column with dates. but some of the dates in the column is empty or have a 0000-00-00 I think that is the problem because if I don't import that column, I don't get an error can fetch the database. I search around on google and found that when people have that problem they have to add some code telling it to allow data's that is like mine: "Allow Zero Datetime" but I have no clue where I should write that command or if it will solve the problem.


I have also tried to set the DBMS to MySql but that give an error I have to use (all)


What are you using to make the connection between the service ServiceCenter and to outsystems service studio? 



Here you can see my connection path:



Then one column that is highlightet is the one that makes the problem:







here is the problem:





Thank you for trying to help me out :)


Best regards


Lasse





Hello Lasse,

  • OutSystems recognizes "1900-01-01" as NULL (NullDate() returns "#1900-01-01#"). Try populating NULL values with that value. Here is the reference table.
  • Keep in mind that MySQL supports '1000-01-01' to '9999-12-31' as a valid DATE value range.

I hope this helps.

Hi all thanks for taking the time to help me (Y) perfect you pinpointed the problem.. 


I will try to do as you said and change the view by rewriting it. where should I do/create this view is it in the outsystems service or the outsystems integration

Thanks again :)


Best regards

Lasse



Hello Lasse,

Just to improve the answers already provided.

The limitation in the date comes indeed by the platform, as was told, even if it is probably possible to save dates prior to 1900 (down to 1753), in an SQL Server (and possible MySQL and Oracle also), due the Database data type used (datetime in SQL Server). 

But a database View can't be created "directly" into OutSystems (Service Center or Service Studio, doesn't matter). I think there is something in Forge that helps you with that, but usually Views are created directly into the database. You will have to have access to your MySQL database to create a view.

I didn't tested it, but I'm curious...  

In your specific case, maybe doing an SQL instead of using an Aggregate will work, as the error will happen only at the stage of the data being converted from the result set to the OutSystems output list, during the query execution.

You could do something like this (not sure if syntax for the date, using the apostrophe (') is correct:

SELECT 
    (CASE  WHEN {entity}.{date} = '0000-00-00' THEN NULL ELSE {entity}.{date} ENDIF),
    other fields here
FROM ...

If this works, you would not have to implement a view, or at least would have a way to "fix" the entity replacing the '0000-00-00' using an UPDATE in a query. After that the Aggregates would work.

Hope this helps.

Cheers,
Eduardo Jauch