Real-World Data Formatting Examples

One of the most popular scenarios when working with databases is prepping data for reports. Imagine this: you work at a university, and your boss (who, of course, has zero clue about SQL) asks you to make a list of students with their first and last names combined, and also show their birth date in the DD-MM-YYYY format. The task is clear: make the data look nice. And SQL is our trusty sidekick here.

Example 1: Joining First and Last Name

Let’s start by joining the first_name and last_name of students from our students table.

SELECT
    CONCAT(first_name, ' ', last_name) AS full_name
FROM 
    students;

What’s going on here?

• CONCAT() joins strings together. We add a space between the first and last name to make it readable.
• The result is saved in a new column called full_name.

The result might look like this:

Example 2: Date Formatting

Now let’s add birth date formatting to our query.

Meet TO_CHAR() and AGE(). In this lecture we'll use two new functions:

• TO_CHAR(value, format) — converts a number, date, or interval to a string according to a template. The format template is a kind of mini-DSL: 'DD-MM-YYYY' for dates (day-month-year), 'FM$999,999.00' for money amounts (FM strips extra spaces, $ is the currency symbol, the comma is the thousands separator, the dot is the decimal point). The full list of templates is in the PG 17 docs.
• AGE(timestamp) — returns the interval between today and the given date (for example, a person's age from a birth date). To pull only the years out of the interval, wrap it in DATE_PART('year', ...).

SELECT
    CONCAT(first_name, ' ', last_name) AS full_name,
    TO_CHAR(birth_date, 'DD-MM-YYYY') AS formatted_birth_date
FROM
    students;

What’s new here:

• We use the TO_CHAR() function on the birth_date field.
• The 'DD-MM-YYYY' format turns the date into something nice and readable (like 25-12-2001).

Result:

Voilà! You just made a pretty report.

Formatting for Data Export

Let’s say one of your coworkers wants to export order data to a CSV file for Excel. But the data in the database is in a format that’s not convenient for them, and the sales folks want it to look a certain way. For example, instead of the total_price field that just shows the order price, they want it to look like: $100.00.

Example 3: Converting Numbers to Currency Format

Let’s format order data from the orders table for export:

SELECT
    order_id,
    TO_CHAR(total_price, 'FM$999,999.00') AS formatted_price
FROM 
    orders;

What does TO_CHAR() do here?

• FM (Fill Mode) removes extra spaces.
• $ adds the currency symbol.
• 999,999.00 sets the format with thousand separators and two decimal places.

Result:

Now your coworker can easily import the data into Excel and give you props at the next meeting.

Final Task

This is where things get interesting. Let’s combine all the skills you’ve picked up so far.

Task

Create a query for the students table that:

1. Joins first and last name into one column called full_name.
2. Converts the birth date to DD-MM-YYYY format.
3. Shows the student’s age as of today.

The query will look something like this:

SELECT
    CONCAT(first_name, ' ', last_name) AS full_name,
    TO_CHAR(birth_date, 'DD-MM-YYYY') AS formatted_birth_date,
    DATE_PART('year', AGE(birth_date)) AS age
FROM 
    students;

New stuff:

• AGE(birth_date) returns the interval between today and the birth date, but as years, months, and days.
• DATE_PART('year', AGE(birth_date)) pulls just the number of years from that interval.

Result:

This kind of report will satisfy even the pickiest coworkers.

Formatting for Specific Conditions

Sometimes you need to format data for conditions or filtering. For example, let’s pull out students whose birthday is in the current month.

Example 4: Filtering by Month

SELECT
    CONCAT(first_name, ' ', last_name) AS full_name,
    TO_CHAR(birth_date, 'DD-MM-YYYY') AS formatted_birth_date
FROM 
    students
WHERE 
    DATE_PART('month', birth_date) = DATE_PART('month', CURRENT_DATE);

How does this work?

• DATE_PART('month', birth_date) grabs the month from the birth date.
• CURRENT_DATE gives you today’s date. We grab the month from it using DATE_PART().

Combining Formatting and Sorting

Now let’s put it all together and add sorting. For example, let’s make a list of students sorted by their birth date.

Example 5: Sorting by Birth Date

SELECT
    CONCAT(first_name, ' ', last_name) AS full_name,
    TO_CHAR(birth_date, 'DD-MM-YYYY') AS formatted_birth_date
FROM 
    students
ORDER BY 
    birth_date ASC;

Sorting birth_date ASC starts from the earliest birth date, that is, from the oldest students (born earlier → they're older); DESC — from the youngest. Tip: if you get confused, use ORDER BY AGE(birth_date) DESC — it's unambiguously "from oldest to youngest."

Combining with Unique Values

And finally — a bonus task. Imagine our university has branches in several cities, and you need to make a list of unique cities where students study, sorted alphabetically.

Example 6: Unique Values and Sorting

SELECT DISTINCT
    city 
FROM 
    students
ORDER BY 
    city ASC;

What does DISTINCT do?

It removes duplicate values so that each city only shows up once in the results.

Why do you need all this?

Convenience and style. When your data looks good, it’s easier to work with, and your boss asks fewer questions.

Ready for real life. You’ll be able to auto-generate reports, work with exports, and make slick presentations.

Boost your value. SQL isn’t just about data. It’s about making it clear and useful.

Use these skills to not just write queries, but to create masterpieces! In the next lectures, we’ll keep diving into the magic of PostgreSQL.