SQL query not working in OLEDB
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
24-02-22 02:19 AM
Hi, all.
We're trying to use OLEDB to read data from the excel.
The intention is to selectively query data items based on the datetime as below.
The current query we're using is: (but failed)
'select [Order ID], [Status], [Date Created], [Supplier Name] from [sheet1$] where (select CONVERT([Date Created], GETDATE(), 1)) >='&[Data2]
// Data2 is a BP's datetime variable.
Could you correct us with the right query syntax? or Is there anything else to consider?
Best regards
We're trying to use OLEDB to read data from the excel.
The intention is to selectively query data items based on the datetime as below.
The current query we're using is: (but failed)
'select [Order ID], [Status], [Date Created], [Supplier Name] from [sheet1$] where (select CONVERT([Date Created], GETDATE(), 1)) >='&[Data2]
// Data2 is a BP's datetime variable.
Could you correct us with the right query syntax? or Is there anything else to consider?
Best regards
------------------------------
HongJoo Choi
------------------------------
4 REPLIES 4
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
24-02-22 06:24 AM
Hi @HongJooChoi
I think the main problem is with the CONVERT function that yo are using in the WHERE.
Just to clarify, the CONVERT function is another way to cast a value into a specific data type. In this case, the correct syntax for this is: CONVERT(date type, field or expression, style). In your case: CONVERT(datetime, [Date Created], 1)
Try with this query:
select [Order ID]
, [Status]
, [Date Created]
, [Supplier Name]
from [sheet1$]
where CONVERT(datetime, [Date Created], 1) >="&[Data2]
You can also try with a CAST function, but you will problably have some problems with the date format.
Hope this helps you!
See you in the community, bye 🙂
------------------------------
Pablo Sarabia
Architect
Altamira Assets Management
Madrid
634726270
------------------------------
I think the main problem is with the CONVERT function that yo are using in the WHERE.
Just to clarify, the CONVERT function is another way to cast a value into a specific data type. In this case, the correct syntax for this is: CONVERT(date type, field or expression, style). In your case: CONVERT(datetime, [Date Created], 1)
Try with this query:
select [Order ID]
, [Status]
, [Date Created]
, [Supplier Name]
from [sheet1$]
where CONVERT(datetime, [Date Created], 1) >="&[Data2]
You can also try with a CAST function, but you will problably have some problems with the date format.
Hope this helps you!
See you in the community, bye 🙂
------------------------------
Pablo Sarabia
Architect
Altamira Assets Management
Madrid
634726270
------------------------------
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
24-02-22 02:07 PM
@HongJooChoi,
There are a lot of Transact-SQL functions that the Jet or ACE engines don't support and Convert (and Cast) is not supported. You have to look more to the VBA functions when converting data through the SQL query.
------------------------------
Micheal Charron
Senior Manager
RBC
America/Toronto
------------------------------
There are a lot of Transact-SQL functions that the Jet or ACE engines don't support and Convert (and Cast) is not supported. You have to look more to the VBA functions when converting data through the SQL query.
------------------------------
Micheal Charron
Senior Manager
RBC
America/Toronto
------------------------------
Micheal Charron
RBC
Toronto, Ontario
Canada
RBC
Toronto, Ontario
Canada
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
25-02-22 04:11 AM
Dear Micheal
As you pointed out, it seems the Convert and Cast are not supported by the OLEDB objects downloaded from the DX.
Do you mean that I need to find another way to convert the data by using such as BP's embedded functions (as shown in calculation stage) or other business objects?
Regards
------------------------------
HongJoo Choi
------------------------------
As you pointed out, it seems the Convert and Cast are not supported by the OLEDB objects downloaded from the DX.
Do you mean that I need to find another way to convert the data by using such as BP's embedded functions (as shown in calculation stage) or other business objects?
Regards
------------------------------
HongJoo Choi
------------------------------
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
25-02-22 03:16 PM
@HongJooChoi
The OLEDB VBO is the correct one to use. The functions used in the SQL query are dependent on the provider being used. In this case I assume you are using the Jet or ACE providers.

Given the example Excel worksheet shown above, you would have to convert your value passed from Blue Prism to a date value using a VBA function like the following:
"SELECT * FROM [Sheet1$] WHERE [Date Created] >= DATEVALUE('" & [Data2] & "')"
The DATEVALUE function only returns the date portion of the DateTime passed into it so you are telling the query to give you everything from midnight on, for the day specified in the Data2 data item. In the example below, Data2 contained a DateTime of February 20, 2022 2:13 AM.
The OLEDB VBO is the correct one to use. The functions used in the SQL query are dependent on the provider being used. In this case I assume you are using the Jet or ACE providers.
Given the example Excel worksheet shown above, you would have to convert your value passed from Blue Prism to a date value using a VBA function like the following:
"SELECT * FROM [Sheet1$] WHERE [Date Created] >= DATEVALUE('" & [Data2] & "')"
The DATEVALUE function only returns the date portion of the DateTime passed into it so you are telling the query to give you everything from midnight on, for the day specified in the Data2 data item. In the example below, Data2 contained a DateTime of February 20, 2022 2:13 AM.
While the query itself is not affected, somewhere in the conversion from DataTable to collection Blue Prism will convert DateTimes brought into collections to UTC so you will have to do some post manipulation if the DateTimes are relevant to your process.
------------------------------
Micheal Charron
Senior Manager
RBC
America/Toronto
------------------------------
Micheal Charron
RBC
Toronto, Ontario
Canada
RBC
Toronto, Ontario
Canada
