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

[RolfSyvertsen]

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
------------------------------

[LakshmiNarayan3]

Please refer to this thread
OLEDB Issue | Blue Prism Product

Regards

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

[michaeloneil]

Hi @RolfSyvertsen 

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
------------------------------

[Babjee24]

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
------------------------------

[PvD_SE]

Hi B,

Can you share the SQL you use?

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

[Babjee24]

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
------------------------------

[PvD_SE]

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
------------------------------

[michaeloneil]

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
------------------------------

[Babjee24]

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
------------------------------

[LakshmiNarayan3]

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
------------------------------