datetime MySQL parsing the date

I have a column in my BigQuery Table “date”. I need to parse the values of this column in MySql. I currently have the following format for the dates in that column: "2019-10-24T07:00:00"

And I only need the year – month – date “2019-10-24”

How would I extract the date only in the correct format?

Thank you!

Answer

MySQL recognize datetimes in this format, as explained in the documentation:

MySQL recognizes DATETIME and TIMESTAMP values in these formats:

• As a string in either 'YYYY-MM-DD hh:mm:ss' or 'YY-MM-DD hh:mm:ss' format.

[…]

The date and time parts can be separated by T rather than a space. For example, '2012-12-31 11:30:45' '2012-12-31T11:30:45' are equivalent.

So you can use directly date functions on your input string. To remove the time part of a date, you can use function date():

select date('2019-10-24T07:00:00')

Demo on DB Fiddle:

select date('2019-10-24T07:00:00') mydate

| mydate     |
| :--------- |
| 2019-10-24 |