TO_CHAR for date formatting in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the TO_CHAR function do when used with dates in PostgreSQL?

TO_CHAR converts a date or timestamp into a string formatted according to the pattern you specify.

beginner

How would you format a date to show only the year using TO_CHAR?

Use TO_CHAR(date_column, 'YYYY') to get the year as a four-digit string.

beginner

What format pattern would you use with TO_CHAR to display the full month name?

Use 'FMMonth' in the format string to display the full month name without padding, e.g., TO_CHAR(date_column, 'FMMonth').

intermediate

Explain the difference between 'MM' and 'Mon' in TO_CHAR date formatting.

'MM' returns the month as a two-digit number (01-12), while 'Mon' returns the abbreviated month name (Jan, Feb, etc.).

beginner

How can TO_CHAR help when you want to display a date in a custom format like 'DD-MM-YYYY'?

You specify the format string 'DD-MM-YYYY' in TO_CHAR to get the date as day-month-year with dashes, e.g., TO_CHAR(date_column, 'DD-MM-YYYY').

Which TO_CHAR format pattern returns the full name of the day of the week?

A 'Day'

B 'Dy'

C 'DD'

D 'D'

The pattern 'Day' returns the full day name (e.g., Monday). 'Dy' returns abbreviated day name.

What will TO_CHAR(current_date, 'YYYY-MM-DD') return?

A Month-Day-Year as numbers separated by dashes

B A timestamp with time included

C Day-Month-Year as numbers separated by dashes

D Year-Month-Day as numbers separated by dashes

The format 'YYYY-MM-DD' returns year, month, and day in that order separated by dashes.

If you want to show the month as a two-digit number, which format should you use?

A 'Mon'

B 'MM'

C 'Month'

D 'YY'

'MM' returns the month as a two-digit number (01 to 12).

Which TO_CHAR pattern returns the last two digits of the year?

A 'YYYY'

B 'Y'

C 'YY'

D 'Year'

'YY' returns the last two digits of the year, e.g., '23' for 2023.

What does TO_CHAR(timestamp, 'HH24:MI:SS') format?

A Hour in 24-hour format with minutes and seconds

B Hour in 12-hour format with minutes and seconds

C Only minutes and seconds

D Only hour and seconds

'HH24:MI:SS' formats time in 24-hour format with minutes and seconds.

Describe how TO_CHAR can be used to format a date to show the day, abbreviated month, and year.

Think about how to combine day, month abbreviation, and year in one format.

Explain the difference between 'MM' and 'Mon' in TO_CHAR date formatting and when you might use each.

Consider readability vs sorting needs.