While writing SQL queries, it is important to get the logic right behind that query because the readability and formatting make a significant difference while working on large queries. The queries that are well formatted are easier to understand, debug, and maintain. When your queries are well-organized, it is much easier to communicate your work with team members.
In this blog, we will discuss how to format SQL statements, the functions used for formatting data, and how you can make the data look better. So let’s start!
Table of Contents:
There are two important aspects of formatting in SQL: First, format the code clearly, and second, format the result data that is returned by your queries. Both these aspects are equally important for writing efficient and professional SQL queries.
Formatting in SQL means arranging the statements by indenting them properly, using proper casing, and logical structure. This helps you to make your queries easy to understand. Also, formatting the output can be useful for focusing on how the data is presented. It helps you to generate reports, export data, and prepare information for the end-users. It presents the result in a readable and structured format so that it becomes easier for you to understand.
Syntax for FORMAT() function:
The syntax for the FORMAT() function is given below:
FORMAT(value, format, [culture])
where,
- value: It refers to the number or date you want to format.
- format: It refers to the string that is used to define the pattern of formatting. (e.g., ‘N2‘ is used for two decimal places, ‘yyyy-MM-dd‘ is used for the date format)
- culture (optional): It is basically a locale code that is used to apply culture-specific formatting (e.g., ‘en-US‘, ‘de-DE‘).
Given below are a few reasons why it is important to format SQL code:
- Improves readability: A code that is well-structured is easier to understand, especially while you are dealing with complex or deeply nested logic. This helps you to go with the flow without getting confused.
- Reduces Debugging Time: When your code is organized properly, it becomes easy for you to point out mistakes.
- Ensures Consistency: To make your code look clean, you should apply proper casing, spacing, and naming to your SQL code. It also helps to avoid errors.
- Enhances Maintainability: Formatting of SQL code allows you to make changes in the future without any effort.
- Minimizes Need for Comments: When your SQL code is clear, you do not need to depend on the comments. The structure of the code shows the logic of the query.
Learn SQL, Oracle, MongoDB & more from top trainers
Master Databases Online
Handling date and time values is an important task while you are working with databases. It is important to have a good understanding of formatting date and time values in SQL because it will help you to filter records by date, format dates for reports, and display timestamps in a readable format.
SQL consists of several built-in functions and formatting techniques for converting raw dates and time stamps into formats that can be easily understood by the user. This technique is called SQL date format or datetime formatting.
- It helps to present the results in a way that can be easily read by the user.
- Date and Time Formatting help you break a date into year, month, or day. This helps you to organize your search data more easily.
- It can also be useful for showing dates in a format that is common in your country or specific to your application, like 06/06/2025 instead of 2025-06-06.
- It can also be useful while you are preparing data for reports, dashboards, or exporting to Excel/CSV.
Different functions are provided by different databases for date formatting. The most common ones are listed below in a tabular format:
| Database |
Function Used |
| MySQL |
DATE_FORMAT() |
| SQL Server |
FORMAT() or CONVERT() |
| PostgreSQL |
TO_CHAR() |
Some examples regarding the implementation of Date and Time formatting in SQL Server are given below:
- Basic Date Formatting using FORMAT()
Example:
-- Step 1: Create a table with a DATE column
CREATE TABLE orders (
order_id INT,
customer_name VARCHAR(50),
order_date DATE
);
-- Step 2: Insert sample data
INSERT INTO orders (order_id, customer_name, order_date) VALUES
(1, 'C1', '2025-06-06'),
(2, 'C2', '2025-05-30');
-- Step 3: Format the order_date as DD-MM-YYYY using FORMAT()
SELECT
order_id,
customer_name,
FORMAT(order_date, 'dd-MM-yyyy') AS formatted_date
FROM
orders;
Output:
Explanation:
The above SQL query is used to retrieve order details and then formats the order_date column into DD-MM-YYYY format. This is done using the DATE_FORMAT() function.
- Including Time with DATETIME
Example:
-- Step 1: Create the table with DATETIME column
CREATE TABLE logs (
log_id INT,
message VARCHAR(100),
log_time DATETIME
);
-- Step 2: Insert sample data
INSERT INTO logs (log_id, message, log_time) VALUES
(1, 'User logged in', '2025-06-06 09:15:30'),
(2, 'User logged out', '2025-06-06 17:45:00');
-- Step 3: Format datetime as DD/MM/YYYY HH:MM AM/PM
SELECT
log_id,
message,
FORMAT(log_time, 'dd/MM/yyyy hh:mm tt') AS formatted_log_time
FROM
logs;
Output:
Explanation:
The above query is used to retrieve the login and logout details and then formats the log_time column to display the date and time in the DD/MM/YYYY HH:MM:SS AM/PM format. This is done using the DATE_FORMAT() function.
- Extracting Date Parts in SQL
Sometimes, you might just need to extract the year, month, or day from a full date. The query to achieve that is given below:
Example:
-- Step 1: Create the 'orders' table with a DATE column
CREATE TABLE orders (
order_id INT,
customer_name VARCHAR(50),
order_date DATE
);
-- Step 2: Insert sample data into the 'orders' table
INSERT INTO orders (order_id, customer_name, order_date) VALUES
(1, 'C1', '2025-06-06'),
(2, 'C2', '2025-05-30');
-- Step 3: Extract the year, month, and day from the order_date
SELECT
order_id,
customer_name,
YEAR(order_date) AS order_year,
MONTH(order_date) AS order_month,
DAY(order_date) AS order_day
FROM
orders;
Output:
Explanation:
The above query is used to retrieve order details and breaks the order_date into separate columns for year, month, and day by using the date functions in SQL.
Get 100% Hike!
Master Most in Demand Skills Now!
While retrieving data from a database, raw values do not always appear in a user-friendly format. You have to restructure, format, or combine data by using various SQL functions. This helps you to improve the presentation of your code and also improve its readability.
Given below are some common formatting techniques that will help you to understand the concept better.
- Formatting Dates using FORMAT()
You can convert dates to a human-readable format like DD-MM-YYYY or include time as well.
Example:
-- Step 1: Create a table with a DATE column
CREATE TABLE orders (
order_id INT,
customer_name VARCHAR(50),
order_date DATE
);
-- Step 2: Insert sample data
INSERT INTO orders (order_id, customer_name, order_date) VALUES
(1, 'C1', '2025-06-06'),
(2, 'C2', '2025-05-30');
-- Step 3: Format order_date as DD-MM-YYYY
SELECT
order_id,
customer_name,
FORMAT(order_date, 'dd-MM-yyyy') AS formatted_date
FROM
orders;
Output:
Explanation:
The above query is used to retrieve order details and then formats the order_date column. This helps to display dates in the DD-MM-YYYY format by using the FORMAT() function.
- Formatting Date and Time using DATETIME
You can also show the time in AM/PM format while working with DATETIME. The sample code is given below.
Example
-- Step 1: Create the logs table
CREATE TABLE logs (
log_id INT,
message VARCHAR(100),
log_time DATETIME
);
-- Step 2: Insert data
INSERT INTO logs (log_id, message, log_time) VALUES
(1, 'User logged in', '2025-06-06 09:15:30'),
(2, 'User logged out', '2025-06-06 17:45:00');
-- Step 3: Format datetime as DD/MM/YYYY HH:MM:SS AM/PM
SELECT
log_id,
message,
FORMAT(log_time, 'dd/MM/yyyy hh:mm:ss tt') AS formatted_log_time
FROM
logs;
Output:
Explanation:
The above query is used to retrieve the login and logout details and then formats the log_time column to display the date and time in the DD/MM/YYYY HH:MM:SS AM/PM format. This is done using the DATE_FORMAT() function.
- Combining Strings with CONCAT()
Example:
-- Step 1: Create the table
CREATE TABLE employees (
emp_id INT,
first_name VARCHAR(50),
last_name VARCHAR(50)
);
-- Step 2: Insert data
INSERT INTO employees VALUES
(1, 'FN1', 'LN1'),
(2, 'FN2', 'LN2');
-- Step 3: Combine the first and last names
SELECT
emp_id,
first_name + ' ' + last_name AS full_name
FROM
employees;
Output:
Explanation:
The above SQL query is used to create a table named employees. After that, you have to insert sample data and retrieve the employee ID of each of the employees along with their full name. You can do this by combining the first and last names by using the CONCAT function.
- Formatting numbers using the FORMAT() function
If you want to show numbers with commas, you can use the FORMAT() function.
Example:
-- At first, you have to create a sales table
CREATE TABLE sales (
sale_id INT,
amount DECIMAL(10,2)
);
-- Insert data
INSERT INTO sales VALUES
(1, 123456.789),
(2, 98765.4321);
-- After that, you have to format amount with 2 decimals and a comma separator
SELECT
sale_id,
FORMAT(amount, 2) AS formatted_amount
FROM
sales;
Output:
Explanation:
The above SQL query is used to create a table named sales. After that, data is inserted into it, and the sale ID is retrieved with the amount formatted to 2 decimal places. Commas are also used for better readability.
- Padding Numbers or Strings using RIGHT() and LEFT() combined with REPLICATE()
Example:
-- Step 1: Create the table with sample strings
CREATE TABLE sample_strings (
id INT,
val1 VARCHAR(10),
val2 VARCHAR(10)
);
-- Step 2: Insert data
INSERT INTO sample_strings (id, val1, val2) VALUES
(1, '123', 'AB'),
(2, '45', 'CD');
-- Step 3: Use padding logic to replicate LPAD and RPAD
SELECT
id,
-- Left pad val1 with zeros to make it 5 characters
RIGHT(REPLICATE('0', 5) + val1, 5) AS left_padded,
-- Right pad val2 with asterisks to make it 5 characters
LEFT(val2 + REPLICATE('*', 5), 5) AS right_padded
FROM
sample_strings;
Output:
Explanation:
The above SQL query is used to pad val1 on the left with zeros and pad val2 on the right with asterisks.
While working with numeric data in SQL, it is important to format numbers to get better clarity and readability. That is why numeric and currency formatting is important in SQL. Some of the example use cases of Numeric and Currency Formatting are given below:
- Using the FORMAT() function
The FORMAT() function is used to format numbers as you wish.
Example:
-- Step 1: Create the sales table
CREATE TABLE sales (
sale_id INT,
amount DECIMAL(12, 4)
);
-- Step 2: Insert data
INSERT INTO sales VALUES
(1, 123456.789),
(2, 98765.4321);
-- Step 3: Format numbers with commas and 2 decimal places
SELECT
sale_id,
FORMAT(amount, 'N2') AS formatted_amount
FROM
sales;
Output:
Explanation:
The above query is used to retrieve the ID of each sale. After that, it formats the amount with commas and 2 decimal places so that it becomes easier to read.
- Currency Symbol Formatting
Although SQL doesn’t consist of built-in currency symbols in most databases, they can be concatenated manually.
Example:
-- Step 1: At first, you have to create the 'sales' table
CREATE TABLE sales (
sale_id INT,
amount DECIMAL(12, 2)
);
-- Step 2: After that, you have to insert sample data into the 'sales' table
INSERT INTO sales (sale_id, amount) VALUES
(1, 123456.78),
(2, 98765.43),
(3, 5000.00);
-- Step 3: After that, you have to write a query to format the amount as currency
SELECT
sale_id,
CONCAT('$', FORMAT(amount, 2)) AS formatted_currency
FROM
sales;
Output:
Explanation:
The above query is used to store sales data. After that, it uses the CONCAT and FORMAT functions to show the amount field in a currency format.
- Controlling Decimal Places
The ROUND() function can be used to round off numbers rather than formatting them.
Example:
-- Step 1: At first, you have to create the 'sales' table
CREATE TABLE sales (
sale_id INT,
amount DECIMAL(10, 3)
);
-- Step 2:After that, you have to insert sample data into the 'sales' table
INSERT INTO sales (sale_id, amount) VALUES
(1, 123.456),
(2, 98.765),
(3, 45.123);
-- Step 3: After that, you have to write the query to round the amount to 1 decimal place
SELECT
sale_id,
ROUND(amount, 1) AS rounded_amount
FROM
sales;
Output:
Explanation:
The above query shows the numeric formatting. It uses the ROUND() function, which helps in reporting and simplifying data presentation.
- Truncating Decimal
Example:
-- Step 1: Create the 'sales' table
CREATE TABLE sales (
sale_id INT,
amount DECIMAL(10, 4)
);
-- Step 2: Insert sample data
INSERT INTO sales (sale_id, amount) VALUES
(1, 123.4567),
(2, 98.7654),
(3, 45.1299);
-- Step 3: Truncate amount to 2 decimal places (no rounding)
SELECT
sale_id,
ROUND(amount, 2, 1) AS truncated_amount
FROM
sales;
Output:
Explanation:
In the above query, the ROUND(amount, 2, 1) function is used in the SQL server to truncate the amount to 2 decimal places.
- Padding Numbers
Example:
-- Step 1: Create the 'sales' table
CREATE TABLE sales (
sale_id INT,
amount DECIMAL(10, 2)
);
-- Step 2: Insert sample data
INSERT INTO sales (sale_id, amount) VALUES
(1, 1500.50),
(23, 2399.99),
(456, 875.00);
-- Step 3: Pad sale_id to 5 digits with leading zeros
SELECT
sale_id,
RIGHT(REPLICATE('0', 5) + CAST(sale_id AS VARCHAR), 5) AS padded_id
FROM
sales;
Output:
Explanation:
The above query is used to pad each sale_id with leading zeros. This ensures that the output is 5 digits long using REPLICATE(), CAST(), and RIGHT().
Learn SQL, MySQL, MongoDB & more at zero cost
Free Database Courses Online
Conclusion
It is important to master SQL output formatting because it will help you to create readable, understandable, and professional SQL queries. It involves formatting dates, numbers, and strings, and aligning outputs for reports. Application of these formatting techniques will help you to enhance the clarity of your data and also to streamline your data with both systems and users. Hence, it is important for you to develop this skill because it will help you to write efficient SQL queries and make your outputs more effective and user-friendly.
1. How can formatting in SQL be improved by consistent indentation?
Straight and regular indentation makes it easy to distinguish the sections of your code from one another. This makes it easier to read SQL and debug.
2. Do I have to place each SQL clause on a new line?
Yes, since it will allow you to make your code simpler and better organized.
3. Is it better to use spaces or tabs for SQL formatting in SQL?
Yes, because they provide the same appearance across multiple editors.
4. How can formatting help while I am working with complex SQL queries?
Formatting is responsible for breaking down complex queries into simple parts. This makes the queries easier to understand.
5. Are there any industry standards for formatting in SQL?
No, but many people follow conventions like writing keywords in uppercase and aligning the clauses.