topic RE: Database query returns DateTime when it's only Date in SQL database in Product Forum

Database query returns DateTime when it's only Date in SQL database

c202c0y — Tue, 11 Apr 2023 14:10:00 GMT

Hi,

I'm running a code stage to extract data from a sql table to a collection.
The colum in sql is defined as Date type. But when the the result is stored in a collection the date has become DateTime. And also since we are not in UTC time zone we get -2 hours.
So for example, 2023-05-17 becomes 16.05.2023 22:00:00.

Is there any way to just extract the date so that I don't need to convert it afterwars? I only need the date, not date and time..
We are using version 6.10.5

Thanks.

------------------------------
Rolf Syvertsen
------------------------------

RE: Database query returns DateTime when it's only Date in SQL database

plnarayana777 — Wed, 12 Apr 2023 07:09:00 GMT

Please refer to this thread
OLEDB Issue | Blue Prism Product

Regards

------------------------------
Lakshmi Narayana
------------------------------

RE: Database query returns DateTime when it's only Date in SQL database

michaeloneil — Wed, 12 Apr 2023 15:00:00 GMT

Hi @c202c0y

You will need to change you sql query to compensate for the difference e.g. Select TO_CHAR(fROM_TZ( CAST(ForfallsDato AS TIMESTAMP ), 'UTC' ) AT LOCAL, 'YYYY-MM-DD') AS ForfallsDato FROM YourDatabase

Substitute UTC for the timezone you are in. This will return the value as correct date

------------------------------
Michael ONeil
Technical Lead developer
NTTData
Europe/London
------------------------------

RE: Database query returns DateTime when it's only Date in SQL database

Babjee24 — Thu, 13 Apr 2023 11:07:00 GMT

Hi MichaelONeil,

I've a column in my excel which has number type as majority data hence, if I read the Data through OLEDB I'm getting the rows that has numbers as is, but the rows that has non numeric data rows are being read as null attaching the screenshot. Any idea on how to cast that column to text .

------------------------------
Babjee Vangipurapu
Senior RPA Developer
Wonderbotz
India
------------------------------

RE: Database query returns DateTime when it's only Date in SQL database

PvD_SE — Thu, 13 Apr 2023 11:19:00 GMT

Hi B,

Can you share the SQL you use?

------------------------------
Happy coding!
---------------
Paul
Sweden
------------------------------

RE: Database query returns DateTime when it's only Date in SQL database

Babjee24 — Thu, 13 Apr 2023 11:51:00 GMT

Hi Paul,

It's a basic SQL select statement select * from [Sheet1$]; If there is a way to cast I'll select individual columns from my Excel

------------------------------
Babjee Vangipurapu
Senior RPA Developer
Wonderbotz
India
------------------------------

RE: Database query returns DateTime when it's only Date in SQL database

PvD_SE — Thu, 13 Apr 2023 12:27:00 GMT

Hi B,

In Michaels response a bit earlier in this thread, he explains how to cast a date so it will be a date in your collection. Your 'select * from...' does not do that, hence you get a wrong format.

Your SQL must name all fields you want to select rather than using a 'select *'. Any date fields in the XL must be casted to be date formated in your collection. See Michaels answer for details.

------------------------------
Happy coding!
---------------
Paul
Sweden
------------------------------

RE: Database query returns DateTime when it's only Date in SQL database

michaeloneil — Thu, 13 Apr 2023 12:47:00 GMT

Hi @Babjee Vangipurapu

You could try doing a cast in your statement, if the columns are showing as numbers but there are text items in there not being returned in the collection then try something like this SELECT CAST(GL reference AS CHAR(10)). You will likely need to define all the columns in your query when you run it to make sure you get all the data you need.

There are more examples of casting here SQL CAST Function Explained with Examples (databasestar.com) which might be useful.

------------------------------
Michael ONeil
Technical Lead developer
NTTData
Europe/London
------------------------------

RE: Database query returns DateTime when it's only Date in SQL database

Babjee24 — Thu, 13 Apr 2023 13:44:00 GMT

Hi @Michael ONeil ,

Attaching the screenshot of the SQL query with individual column names in SQL query I'm still getting an error. I've tried without Square Brackets, without aliasing name, Aliasing name without Square brackets as well

Attaching the error screenshot

------------------------------
Babjee Vangipurapu
Senior RPA Developer
Wonderbotz
India
------------------------------

RE: Database query returns DateTime when it's only Date in SQL database

plnarayana777 — Thu, 13 Apr 2023 14:35:00 GMT

Hi Babjee
i am not sure of your requirement but check if makes any use for you
try setting try connection string "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="&[Path]&";Extended Properties=""Excel 12.0 Xml;HDR=NO;IMEX=1"";" Here HDR=NO will not give you headers but you can use utility - collection vbo to make first row as headers but the result will be in text format only

If you require to filter data while you want to get collection and you are sure of the position of column then try this
in query like "Select * From [Orders$] Where F5 = "Second Class"" here F5 is column index as 5th position based on that you can modify

if you are not sure of column index then try to fetch columns first then get index value of that particular column later you repeat the above step

Hope this helps you

------------------------------
Lakshmi Narayana
------------------------------

RE: Database query returns DateTime when it's only Date in SQL database

michaeloneil — Thu, 13 Apr 2023 17:08:00 GMT

Hi babjee

The column names are these veing referenced using data items? If not then you dont need to enclose them in brackets. You only need to enclose the sheet name in brackets so your query should look like this SELECT Action Date, CAST(GL reference As varchar(40)) FROM [Sheet1$]

------------------------------
Michael ONeil
Technical Lead developer
NTTData
Europe/London
------------------------------