topic RE: VBO Data - SQL Server - Date / Time-Columns in Product Forum

VBO Data - SQL Server - Date / Time-Columns

ChristianPanhan — Fri, 29 Nov 2019 09:54:00 GMT

Hello,

when the result set of a query does contain a date and/or time column, the data is not reflected correctly in die blue prism collection.
There is some utc calculation ongoing.
And so when the data in the database already is in utc the results are wrong.
How can I achieve, that the blue prism collection represents exactyl the query result?

And by that I do not mean, changing the queries in the database to return times in local time so that blue prism can convert it back to utc.
Columns with the SQL Server Data Type date cannot be converted to and from utc.

Thanks
Christian

RE: VBO Data - SQL Server - Date / Time-Columns

AndreyKudinov — Mon, 02 Dec 2019 10:46:00 GMT

I've copied an action in OLEDB VBO and added the following code before returning collection (oDataTable is the data), similar should work with SQL Server VBO I guess:

For Each column as DataColumn in oDataTable.Columns 
	If column.DataType = GetType(DateTime) then
		For i as Integer = 0 to oDataTable.Rows.Count-1:
			Dim dateObj as Object = Nothing
			dateObj = oDataTable.Rows(i).Field(Of Object)(column.ColumnName)
			If (dateObj <> Nothing) Then
				oDataTable.Rows(i)(column.ColumnName) = oDataTable.Rows(i).Field(Of DateTime)(column.ColumnName).ToLocalTime()
			End If
		Next
	end if
Next

------------------------------
Andrey Kudinov
Project Manager
MobileTelesystems PJSC
Europe/Moscow
------------------------------

RE: VBO Data - SQL Server - Date / Time-Columns

MichalSzumski — Mon, 02 Dec 2019 12:00:00 GMT

Hi Christian,

I don't know if this helps but I had similar case when downloading SharePoint list to BluePrism with OLEDB - all dates were shifted. Solution was to convert date format to text format already in the query. If needed You can revert it in BluePrism.

Best regards,
Michal Szumski

------------------------------
Michal Szumski
RPA developer
Rockwell Automation
Europe/Warsaw
------------------------------

RE: VBO Data - SQL Server - Date / Time-Columns

ChrisNewsom — Mon, 02 Dec 2019 16:54:00 GMT

Hi, we have experienced a similar issue and resolved it by casting the date in the SQL, e.g. Cast(DATE as Varchar(256)) as "Date"

------------------------------
Christopher Newsom
RPA Developer
Leeds Building Society
Europe/London
------------------------------