Help
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Blue Prism changes extracted date to previous date while reading from Excel(using OLEDB)

FarisAhamed
Level 2

‎18-03-21 06:30 AM

Hello Everyone,

I am trying to read date from a particular cell(Date format) in Excel(using OLEDB). Whenever the date is read, in the output the date changes to its previous day.

Eg:- If the date in the cell is 17/02/2021 after reading in Blue Prism the output shows 16/02/2021.
If the date in the cell is 15/02/2021 after reading in Blue Prism the output shows 14/02/2021.

Would highly appreciate if someone could help find a solution to the above issue.
FYI I am using Blue Prims v6.10.0
Thanks in advance.
0 Likes
4 REPLIES 4

John__Carter
Staff
Staff

‎18-03-21 06:04 PM

Could be UTC/timezone/daylight saving issue. If you are using the JET driver maybe switch to the ACE, or maybe try including the Format function in the query to force the timezone offset. For example SELECT Format([MyDate], "YYYY-MM-DD hh:mm:ss+03:00") As MyDate FROM [Sheet1$]
35025.png
0 Likes

AndreyKudinov
Level 10

‎19-03-21 11:43 AM

I had this issue with "Get Collection" action of Data - OLEDB VBO (getting data from Oracle DB) and my solution is to change code stage (made an action copy) to something like this below. This also needs System.Data.DataSetExtensions.dll reference in VBO, .Field() is an extension.

Dim oDataAdapter As New OLEDBDataAdapter(SQL,moConnection)
Dim oDataTable As New DataTable

oDataAdapter.Fill(oDataTable)

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

Results = oDataTable​

0 Likes

RohiniPatil
Level 3

‎04-03-22 12:27 PM

is this resolved. even i am facing same issue? if date is from nov onwards then it is giving same date . but if its before Nov then it is giving previous day's date. My system's Time is in UTC zone.
0 Likes

michaeloneil
MVP

‎07-03-22 03:50 PM

hi faris this is because of the difference in times when doing oledb actions you will lose an hour from the time. So your date of 17/02/2021 should take everything from 17/02/2021 00:00:01 until 17/02/2021 23:59:59 but instead you lose 1 hour making the date you select more like 16/02/2021 23:00:00. You can correct this in the query you write to get the date see the example below, note that example MY_DATE is the column where your date or date time is stored, also the format YYYY-MM-DD  can also be edited to whatever date format your need as well. Hope this helps :)

SELECT TO_CHAR(FROM TZ(CAST(MY_DATE AS TIMESTAMP), 'UTC') AT LOCAL, 'YYYY-MM-DD') AS MY_DATE from My_database
0 Likes