Excel DateTime Data Insert into Database error

Help Forum
, , , , ,
1

Excel data:

date
date1071×521 75.9 KB

MySQL Table Column name and DataType:

ID INT
Name VARCHAR(20)
Start_Date DATETIME
End_Date DATETIME

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

2

@mavsp8

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

Cheers

3

Hi

Read Range has been loaded Start and End Date as invariant culture info (mm/dd/yyyy).

Try this:

CurrentRow(“Start_Date”).ToString(“yyyy-MM-dd hh:mm:ss”)
CurrentRow(“End_Date”).ToString(“yyyy-MM-dd hh:mm:ss”)

Let me know if this works for you

4

Hi,

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:

‘“+Convert.ToDateTime(CurrentRow(“End_Date”)).ToString(“yyyy-MM-dd hh:mm:ss”)+”’

how can i solve this issue?

5

this won’t work

6

Use the same, just put hh as HH (24 hour format)

Convert.ToDateTime(CurrentRow(“End_Date”)).ToString(“yyyy-MM-dd HH:mm:ss”)

7

It enters date like: 2026-02-28 12:00:00 instead of 2026-02-28 00:00:00

8

Try to make the column nullable. (Remove the NOT NULL constraint for Start_Date and End_Date column in your database

9

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

10

Hi @mavsp8,
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 :slight_smile:

11

just for curiosity, when you put other hour like 15:10 the database preserves the 24 hour format?

12

d1

As you mentioned, I used message box activity to see how it reads.

13

after changing the time to 15:10:00

I tried to read value using message box activity it reads as 15:10:00

But when it goes to database it reads as 03:10:00

14

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

15

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

16

Solved the issue i took two variables

  • first variable i used - “Convert.ToDateTime(CurrentRow(“End_Date”)).ToString(“yyyy-MM-dd hh:mm:ss”)”

  • second variable i used variable1.ToString()

And tried using second variable into my insert statement and it worked.

1 Like
17

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.