Example of date formatting of mysql practical operation

In MySQL, you can useDATE_FORMAT()Function to format dates. The DATE_FORMAT() function is usually used for formattingDATETIME or TIMESTAMPField of type. This function allows you to display dates and times in the specified format.

Here are some common date formatting examples:

Show year-month-day:
```
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');
```
Show month/day/year:
```
SELECT DATE_FORMAT(NOW(), '%m/%d/%Y');
```

Show full date and time:

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');

Show hours and minutes:
```
SELECT DATE_FORMAT(NOW(), '%H:%i');
```

Show the day of the week:

SELECT DATE_FORMAT(NOW(), '%W'); -- What day of the week（Sunday=0, ..., Saturday=6）

Show the name of the month:

SELECT DATE_FORMAT(NOW(), '%M'); -- The name of the month（January, ..., December）

Show the abbreviation of the month:

SELECT DATE_FORMAT(NOW(), '%b'); -- Abbreviation of the month（Jan, ..., Dec）

Show the last two digits of the year:
```
SELECT DATE_FORMAT(NOW(), '%y');
```
Show AM or PM:
```
SELECT DATE_FORMAT(NOW(), '%p');
```
Display seconds:
```
SELECT DATE_FORMAT(NOW(), '%s');
```

These areDATE_FORMAT()Some basic usages of functions. You can create a custom date format by combining different formatting options as needed.

If you try to use DATE_FORMAT() on a field that is not a datetime type, such as INT or VARCHAR, MySQL returns an error because it cannot interpret these types of data as dates or times.

If you have a field that is not a datetime type but you know it contains date or time information, you may need to convert it to the DATETIME type before using the DATE_FORMAT() function. For example, if a VARCHAR field contains a datetime string, you can convert it using the STR_TO_DATE() function:

SELECT DATE_FORMAT(STR_TO_DATE(your_varchar_column, '%Y-%m-%d %H:%i:%s'), '%Y-%m-%d %H:%i:%s');

Here, the STR_TO_DATE() function converts the string to the DATETIME type, and the DATE_FORMAT() function formats it to the desired format.

Summarize

This is the end of this article about date formatting of mysql practical operation. For more related content on date formatting of mysql, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!