January 5, 2023, 3:23pm
MySQL Table Column name and DataType:
Reading Excel sheet as DataTable in UiPath
I am using this insert command activity to insert data to my MySQL Table:
“INSERT INTO statcan (ID, Name, Start_Date, End_Date) VALUES(”+CurrentRow(“ID”).ToString+“, '”+CurrentRow(“Name”).ToString+“', “+CurrentRow(“Start_Date”).ToString+”, “+CurrentRow(“End_Date”).ToString+”);”
ERROR i am getting is:
Run command: ERROR [HY000] [MySQL][ODBC 8.0(a) Driver][mysqld-8.0.31]Incorrect datetime value: ‘02/28/2022 00:00:00’ for column ‘Start_Date’ at row 1
Need help to solve this issue.
Thank you in advance
January 5, 2023, 4:17pm
You need to match the value to the database date format…please check in datatabase in which format it is and use date conversion activity or datetime.Parseexact method to change the format to required format matching with db
And also the displace format and the format whole read are different sometimes in excel.check that
January 5, 2023, 4:23pm
Read Range has been loaded Start and End Date as invariant culture info (mm/dd/yyyy).
Let me know if this works for you
January 5, 2023, 4:26pm
I was able to insert data into database but facing one issue,
It enters date like: 2020-02-28
12:00:00 instead of 2020-02-28 00:00:00
I used this format in INSERT statement to enter date:
how can i solve this issue?
January 5, 2023, 4:34pm
Use the same, just put hh as HH (24 hour format)
January 5, 2023, 4:41pm
It enters date like: 2026-02-28
12:00:00 instead of 2026-02-28 00:00:00
January 5, 2023, 4:54pm
Try to make the column nullable. (Remove the NOT NULL constraint for Start_Date and End_Date column in your database
January 5, 2023, 4:59pm
It is already set to nullable, there are no constraint.
issue is with formatting i think because it enters date like “2026-02-28
12:00:00” the only issue is it should enter as 00:00:00
Could you please add your Insert Query into log message activity before execute DB query to print the actual Insert Query into DB then after we can see what is the value of each Column we can solve this issue
January 5, 2023, 5:08pm
just for curiosity, when you put other hour like 15:10 the database preserves the 24 hour format?
January 5, 2023, 5:26pm
As you mentioned, I used message box activity to see how it reads.
January 5, 2023, 5:32pm
after changing the time to 15:10:00
I tried to read value using message box activity it reads as
But when it goes to database it reads as
What about Print Full Query also did you try to add this Query manually from My SQL Console without UiPath Query is it work or no ? also please make sure from 24 Hours and 12 Hours format and check witch is done on your DB
January 5, 2023, 5:47pm
I think you database shows the date as 12 hour format.
Try to query start_date with select STR_TO_DATE(start_date,‘%T’) from your_database
%T to show date in 24 hour format
January 5, 2023, 5:58pm
Solved the issue i took two variables
And tried using second variable into my insert statement and it worked.
January 8, 2023, 5:59pm
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.