VBO Data - SQL Server - Date / Time-Columns
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
29-11-19 09:54 AM
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
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
3 REPLIES 3
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
02-12-19 10:46 AM
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:
------------------------------
Andrey Kudinov
Project Manager
MobileTelesystems PJSC
Europe/Moscow
------------------------------
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
------------------------------
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
02-12-19 12:00 PM
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
------------------------------
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
------------------------------
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
02-12-19 04:54 PM
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
------------------------------
------------------------------
Christopher Newsom
RPA Developer
Leeds Building Society
Europe/London
------------------------------
