Question 21216 in Mysql Question posted in
The official MySQL documentation can be found here.

Converting string to datetime split into date and time – Mysql

I am trying to convert string ‘2022-12-28T22:28:43.260781049Z’ to datetime format.

I have such query:

SELECT  date(str_to_date('2022-12-28T22:28:43.260781049Z','%Y-%m-%d')) as date,
        hour(str_to_date('2022-12-28T22:28:43.260781049Z',"%H:%M:%S")) as hour
FROM transaction

And such output:

date time
‘2022-12-28’ NULL

How to get time as well?

Tags:

2

Answers


  1. 0

    You can directly use a CAST on your string value to TIMESTAMP, then extract the date and the time with the hononimous DATE and TIME MySQL functions.

    SELECT DATE(CAST(timestamp_ AS DATETIME)) AS date_,
       TIME(CAST(timestamp_ AS DATETIME)) AS time_
FROM transactions;

    Check the demo here.

    Login or Signup to reply.
  2. 0

    Use timestamp instead of str_to_date:

    SELECT hour(timestamp('2022-12-28T22:28:43.260781049Z')) as hour
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search