CODEWITHSUNDEEP
mysqlstr-to-date
Steven Cao
Steven Cao

Reputation: 1

issue with mysql STR_TO_DATE() function ending characters

I am trying to use the str_to_Date function to convert a string like

'30/09/2010 3:33:51 p.m.' and '30/09/2010 12:00:00 a.m.'(excluding quotations)

to convert into a date time.

However, I am having trouble adding the last characters being 'p.m.' or 'a.m.' to function

What I have tried is

SET datecolumn = STR_TO_DATE(left(datecolumn(23), '%d/%m%Y %h:%i:%s %_.m.')

<---- returns error code and

SET datecolumn = STR_TO_DATE(left(datecolumn(18), '%d/%m%Y %h:%i:%s')

<-- But this one excludes the 'a.m.' or 'p.m.'

TIA

Upvotes: 0

Views: 297

Answers (2)

forpas
forpas

Reputation: 164214

Use REPLACE() to remove the dots from am/pm and apply the correct format:

set datecolumn = str_to_date(
    replace(left(datecolumn, 24), '.', ''),
    '%d/%m/%Y %r'
)

I use the function LEFT() because it exists in your code, although I don't know if it is really needed.
See the demo.
Results:

| date                |
| ------------------- |
| 2010-09-30 15:33:51 |
| 2010-09-30 00:00:00 |

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133400

For manageAM/PM Use p%

SET datecolumn = STR_TO_DATE(left(datecolumn(23), '%d/%m%Y %h:%i:%s %p')

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format

Upvotes: 1

Related Questions