I came across this situation and because I couldn’t find a straight answer, I thought to paste here - what I consider to be - the solution.
SQL doesn’t know what type UiPath is pushing. SQL will try to cast to the type it’s required at the time of the table creation.
So for example, let’s say you have a table in SQL, with a column of DateTime type. When you push it through UiPath, you can do:
Type String, variable: Now().ToString
or, you can push a DateTime Type (SQL is not aware at this point), and a variable like: Today()
I’ve just invented 2 scenarios above. SQL will try to parse the input (the above from UiPath) into its own data type.
in the first scenario you might push a ‘6/5/2020’ as May 6, but SQL to parse it as June 5th, because it depends on your regional and config settings. So you have to be careful with the conversion.
in the second scenario above, everything is converted successfully (direct) way.
Now, back to your point, about the NULLs: If you take care of your instantiations carefully in UiPath, why not pushing it as an Object type (as remember, SQL will do the rest)
If you are absolutely sure that you don’t have NULLs in your DateTime, you can keep the type set as ‘DateTime’.
If you know that you might have missing dates and you want the NULL in your DB, then make it Object.
I wanted to prove a point - there are many examples we can give. The idea is to make sure you are pushing a correct value for SQL to parse. Don’t just assume the date is correct, because Object will not through you an error.
The same goes with other types, like I did above with Arg3 and Arg4. So if you know that you might have NULLs, the DB will take the value pushed by UiPath and try to cast it to float let’s say. So, in case you have myDouble sent as null, you will have in your DB the following
- 0 (zero) (if Type is Double - Arg3)
- NULL (if Type is Object - Arg4)
I tend to write a lot - so apologies - but if you pay attention, you should be able to implement it successfully.
The example stored procedure syntax (in the image) helps me in handling 2 scenarios:
1. Handling optional parameters (e.g., if I have 5 parameters but only want to pass in 2 of the 5) - with the syntax shown I can simply not pass in a value for a parameter within UiPath Studio and it will be ignored
2. Being able to search for “Null” values - with the syntax shown I can search for nulls by passing in an empty string (e.g., “”)
For a more advanced use case - one where the parameters are passed in from a UiPath Form, and you therefore are always passing in a value to the query - there is another pattern you can use (shown in the image):
The ISNULL statement lets us pass in a blank string, and if we do then it will return all the results
The IIF statement lets us pass in a string called “NULL” and it will return all values that are explicitly labeled NULL. It also covers the use case where you don’t pass in a paremeter at all.
Appreciate the solution provided however how would I apply this when reading in data from Excel and storing as a datatable in UIPath before then inserting into SQL Server?
I have tried using the Build Data Table first to alow me to set the Data Type for my datetime columns to Object but still the Insert activity throws an error : “Insert: Failed to convert parameter value from a String to a DateTime.” I assume when I read in my data and output it to the same data table built before it just resets my Data Types?