How to select only date from datetime column?

user_6349318 0
Viewed 658

I have a column of type VARCHAR with values like:

05:47 PM 09/15/2017.

I would like to extract date from datetime.

I have this: 

(05:47 PM 09/15/2017)

but I want this:

(09/15/2017)

in mysql.

datetime mysql php
2 Answers

You can use STR_TO_DATE to convert the value into a date MySQL recognises, and then DATE_FORMAT to output the date in the format you desire e.g.

SELECT DATE_FORMAT(STR_TO_DATE('05:47 PM 09/15/2017', '%h:%i %p %m/%d/%Y'), '%m/%d/%Y')

Output

09/15/2017
user_94737640

It's better to save dateTime values as DateTime type in mySQL which allows you to select date like this

SELECT date(yourColumnName) FROM yourTable

But in this case, you will need to parse your string either with php or with mysql. You can use substring method for mysql if every date is formatted likewise.

SELECT SUBSTRING(yourColumnName, 10) FROM yourTable

Where 10 marks the begining of the date in your string. You can do the same with php, or you can explode your string and then retrieve your date like this: 

$explodedString = explode(" ", $dateTimeStringFromDB);
echo $explodedString[2];
user_98392860
Related