DATE_FORMAT function in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the DATE_FORMAT function do in MySQL?

It formats a date or datetime value according to a specified format string, returning the result as text.

beginner

How do you format a date to show only the year using DATE_FORMAT?

Use DATE_FORMAT(date, '%Y') to get the 4-digit year from a date.

intermediate

What format specifier would you use in DATE_FORMAT to display the month as a full name?

Use %M to display the full month name, like 'January' or 'February'.

intermediate

Write a DATE_FORMAT example to display a date as 'Day-Month-Year', like '05-April-2024'.

Use DATE_FORMAT(date, '%d-%M-%Y') to format the date as '05-April-2024'.

intermediate

Can DATE_FORMAT be used to format time parts like hours and minutes?

Yes, you can use specifiers like %H for hours and %i for minutes to format time.

Which format specifier in DATE_FORMAT returns the full month name?

A %M

B %m

C %b

D %d

The specifier %M returns the full month name, like 'January'. %m returns the month number, %b returns abbreviated month name, and %d returns the day of the month.

What will DATE_FORMAT('2024-06-15', '%Y-%m-%d') return?

A 15-06-2024

B 06/15/2024

C June 15, 2024

D 2024-06-15

The format '%Y-%m-%d' returns the date in 'year-month-day' numeric format, so it returns '2024-06-15'.

Which DATE_FORMAT specifier shows the hour in 24-hour format?

A %h

B %I

C %H

D %p

%H returns the hour in 24-hour format (00 to 23). %h and %I are 12-hour formats, and %p is AM/PM.

How would you format a date to show 'Friday, 05 April 2024' using DATE_FORMAT?

A %w, %d %m %Y

B %W, %d %M %Y

C %D, %d %M %Y

D %A, %d %M %Y

%W returns the weekday name, %d the day, %M the full month name, and %Y the year. %A is not a valid specifier in MySQL.

What type of value does DATE_FORMAT return?

A String

B Datetime

C Integer

D Date

DATE_FORMAT returns a string formatted according to the format specifiers.

Explain how the DATE_FORMAT function works and give an example of formatting a date to 'YYYY/MM/DD'.

Think about how you tell MySQL to show a date in a specific way.

List at least three different format specifiers used in DATE_FORMAT and what they represent.

Remember specifiers are like codes that tell MySQL what part of the date/time to show.