Mysql strip time component from datetime

SqlMysqlDatetime

Sql Problem Overview

I need to do a date comparison in Mysql without taking into account the time component i.e. i need to convert '2008-11-05 14:30:00' to '2008-11-05'

Currently i am doing this:

SELECT from_days(to_days(my_date))

Is there a proper way of doing this?

Sql Solutions

Solution 1 - Sql

Yes, use the date function:

SELECT date(my_date)

Solution 2 - Sql

select date(somedate) is the most common.

If you need to accommodate other formats, you can use:

SELECT DATE_FORMAT(your_date, '%Y-%m-%d');

Solution 3 - Sql

In PostgreSQL you use the TRUNC() function, but I'm not seeing it for MySQL. From my brief Googling, it looks like you'll need to cast your DATETIME value to be just DATE.

date_col = CAST(NOW() AS DATE)

See http://dev.mysql.com/doc/refman/5.0/en/date-and-time-types.html

Solution 4 - Sql

Just a simple way of doing it date("d F Y",strtotime($row['date'])) where $row['date'] comes from your query

Solution 5 - Sql

You could use ToShortDateString();

Categories
Recommended Sql Solutions
Recommended Mysql Solutions
Recommended Datetime Solutions

Previous Solution:

$.getJSON returning cached data in IE8

Jqueryasp.net MvcInternet Explorer-8Getjson

Next Solution:

What’s the best way to check if a file exists in C++? (cross platform)

C++FileFile Io

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionabaraxView Question on Stackoverflow
Solution 1 - SqlRobert GambleView Answer on Stackoverflow
Solution 2 - SqlView Answer on Stackoverflow
Solution 3 - SqlAndy LesterView Answer on Stackoverflow
Solution 4 - SqlRajeev KumarView Answer on Stackoverflow
Solution 5 - SqlJupirao BoladoView Answer on Stackoverflow