Mysql strip time component from datetime

Don't Go Peeling Dates Just Yet...

Here's when you may not want to use DATE():

• Your data is time-sensitive.
• Precise timestamps are needed.
• The time zone cannot be ignored.

_[//]: # "//Dry humor: DATE() is like a time machine; it only goes one way – to the past."

More Than a One-Trick Pony: DATE_FORMAT()

-- Let's accessorize that date!
SELECT DATE_FORMAT(datetime_col, '%Y-%m-%d') AS formatted_date FROM table;


DATE_FORMAT() serves up dates in style (custom formats).

Smarter Date Comparisons

Just like in relationships, avoid being too clingy (no redundant function calls):

-- Dear Diary, I met '2023-03-15' today...
SELECT * FROM table WHERE DATE(datetime_col) = '2023-03-15';


Be elegant and efficient – let MySQL do some of the work:

-- Seek and you shall find... Within bounds.
SELECT * FROM table WHERE datetime_col >= '2023-03-15' AND datetime_col < '2023-03-16';


Time Zones: When Minutes Matter

Adjust date with respect to time zones:

-- Pack your bags; we're timezone traveling!
SELECT CONVERT_TZ(datetime_col, '+00:00', 'SYSTEM') AS user_timezone_date FROM table;


We just converted datetime_col to the system's time zone.

NULL Isn't Always Null and Void

When datetime_col may contain NULL:

-- NULL? Nah, let's default.
SELECT IFNULL(DATE(datetime_col), 'DefaultValue') FROM table;


You'll get a default date instead of a NullPointer ruining your day.

The Good, the Bad, and the (Sort of) Ugly: Legacy Data

Deal with dates stored in non-standard formats:

-- DATE() is also a time-traveling archeologist!
SELECT STR_TO_DATE(string_date_col, '%Y-%m-%d %H:%i:%s') FROM table;


This is how you retrieve a date buried in a string column.