Convert String Value to Date/Datetime in mySQL

Question

I was loading DB2 table into mysql and I was facing issue while loading data with Date format.

I converted those columns into VARCHAR(26) format and loaded the data successfully.

Now I want to convert 2003-01-13-16.50.32.000000 into Date format.

I introduced another column with Date datatype and tried to update it using origional (String) column.

update po_datamart
    set POH_APPROVED_DATE_TEMP=DATE_FORMAT(str_to_date(POH_APPROVED_DATE,'%Y-%c-%d-%H.%i.%s'), '%Y-%c-%d%H:%m:%s');

I am getting following error "Error Code: 1292. Truncated incorrect datetime value: '2003-01-13-16.50.32.000000'".

Would appreciate if anyone can help me with this VARCHAR to DATE conversion.

Please avoid blasting every answer with the same verbose comment. Instead, you could update your question. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jul 13, 2016 at 9:06

Tim Biegeleisen · Accepted Answer · 2016-07-13 08:24:58Z

1

Use STR_TO_DATE with an appropriate format mask:

SELECT STR_TO_DATE('2003-01-13-16.50.32.000000', '%Y-%m-%d-%H.%i.%s.%f')

If you wanted to update:

UPDATE po_datamart
SET POH_APPROVED_DATE_TEMP = STR_TO_DATE(POH_APPROVED_DATE, '%Y-%m-%d-%H.%i.%s.%f')

Follow the link below for a running demo:

SQLFiddle

edited Jul 13, 2016 at 8:24

answered Jul 13, 2016 at 7:52

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user2663104 Over a year ago

Hello Tim. Thanks for your reply. When I fire your select query, I get response as NULL value. When I run your update query, I get "Error Code: 1411. Incorrect datetime value: '2003-01-13-16.50.32.000000' for function str_to_date"

Tim Biegeleisen Over a year ago

@user2663104 Sorry I have updated and it works now. I was using %h for 12 hour time instead of %H for 24 hour time.

user2663104 Over a year ago

UPDATE po_datamart SET POH_APPROVED_DATE_TEMP = STR_TO_DATE(POH_APPROVED_DATE, '%Y-%m-%d-%H.%i.%s.%f') - This query worked but again ended with response - "Error Code: 1411. Incorrect datetime value: '+0.00000000000000E+000' for function str_to_date"

Tim Biegeleisen Over a year ago

What actual string data are you feeding in? It looks like you are using a non standard format.

user2663104 Over a year ago

I used .del file, It is a dump file of the old Data warehouse table which was there in DB2. I used LOAD INLINE functionality to upload the data in the table(MySQL).

|

piotrgajow · Accepted Answer · 2016-07-13 08:24:54Z

0

UPDATE      po_datamart
    SET     POH_APPROVED_DATE_TEMP = str_to_date(POH_APPROVED_DATE, '%Y-%m-%d-%H.%i.%s.%f');

answered Jul 13, 2016 at 8:24

piotrgajow

2,9501 gold badge24 silver badges24 bronze badges

2 Comments

user2663104 Over a year ago

UPDATE po_datamart SET POH_APPROVED_DATE_TEMP = STR_TO_DATE(POH_APPROVED_DATE, '%Y-%m-%d-%H.%i.%s.%f') - This query worked but again ended with response - "Error Code: 1411. Incorrect datetime value: '+0.00000000000000E+000' for function str_to_date"

piotrgajow Over a year ago

What version of MySQL are you using?

José M. Carnero · Accepted Answer · 2016-07-13 08:35:07Z

0

I think you only need and space, try with:

set POH_APPROVED_DATE_TEMP=DATE_FORMAT(str_to_date(POH_APPROVED_DATE,'%Y-%c-%d-%H.%i.%s'), '%Y-%c-%d %H:%m:%s');

I change the last string '%Y-%c-%d%H:%m:%s' to '%Y-%c-%d %H:%m:%s'.

Update for a date column:

set POH_APPROVED_DATE_TEMP=DATE_FORMAT(str_to_date(POH_APPROVED_DATE,'%Y-%c-%d-%H.%i.%s'), '%Y-%c-%d');

edited Jul 13, 2016 at 8:35

answered Jul 13, 2016 at 7:53

José M. Carnero

2631 silver badge8 bronze badges

7 Comments

user2663104 Over a year ago

Thanks for your reply. I got the response as "Error Code: 1292. Truncated incorrect date time value: '2003-01-13-16.50.32.000000'"

José M. Carnero Over a year ago

I try the code and seems to work correctly; what data type have POH_APPROVED_DATE_TEMP (date, datetime, other?). If the column is date type then the data is truncated, you use 2003-01-13 16:50:32 with a column that only admits 2003-01-13. I edit the answer accordingly.

user2663104 Over a year ago

I am using Date. Should I use datetime?

José M. Carnero Over a year ago

Read my answer, the first code if for datetime column, but I made an update for date column. Use as you wish.

user2663104 Over a year ago

UPDATE po_datamart SET POH_APPROVED_DATE_TEMP = STR_TO_DATE(POH_APPROVED_DATE, '%Y-%m-%d-%H.%i.%s.%f') - This query worked but again ended with response - "Error Code: 1411. Incorrect datetime value: '+0.00000000000000E+000' for function str_to_date"

|

Blank · Accepted Answer · 2016-07-13 08:36:34Z

0

Try this:

update po_datamart set POH_APPROVED_DATE_TEMP = str_to_date(POH_APPROVED_DATE, '%Y-%m-%d-%H.%i.%s.%f');

edited Jul 13, 2016 at 8:36

answered Jul 13, 2016 at 7:54

Blank

12.4k1 gold badge16 silver badges34 bronze badges

5 Comments

user2663104 Over a year ago

Thanks for your reply. Error Code: 1292. Truncated incorrect date time value: '2003-01-13-16.50.32.000000' Got above error.

Blank Over a year ago

Is POH_APPROVED_DATE's type is varchar or date?

user2663104 Over a year ago

POH_APPROVED_DATE's type is varchar

Blank Over a year ago

@user2663104 Updated my answer, please check it again.

user2663104 Over a year ago

UPDATE po_datamart SET POH_APPROVED_DATE_TEMP = STR_TO_DATE(POH_APPROVED_DATE, '%Y-%m-%d-%H.%i.%s.%f') - This query worked but again ended with response - "Error Code: 1411. Incorrect datetime value: '+0.00000000000000E+000' for function str_to_date"

Collectives™ on Stack Overflow

Convert String Value to Date/Datetime in mySQL

4 Answers 4

SQLFiddle

6 Comments

2 Comments

7 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

2 Comments

7 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related