What is FORMAT() Function in SQL?

Introduction

If you are a data scientist or analyst, data formatting in SQL is a crucial skill to know. It helps you present your data in a more readable and user-friendly manner, making it easy for stakeholders to understand. The FORMAT() function in the SQL server helps you customize the appearance of dates, times, numbers, and currency. In this article we will explore the FORMAT function in detail, while exploring its various uses.

If you’re just starting out to explore SQL, here’s a beginner’s guide to help you: SQL For Data Science: A Beginner Guide

Overview

• Understand what the FORMAT() function in SQL is.
• Learn the syntax of the FORMAT() function.
• Learn how to format dates, times, numbers, and currency using the FORMAT function in SQL.
• Explore some of the most common practical applications of the FORMAT function.

What is the Format Function in SQL?

The FORMAT() function in SQL helps in converting various types of data into specific string formats. This data could be in the form of dates, times, numbers, or currency. It is particularly useful when you need to present data in a particular format that aligns with regional settings or user preferences.

Syntax

The basic syntax of the FORMAT() function is as follows:

FORMAT (value, format [, culture])

Here,

• value: The value to be formatted (can be a date, time, number, or currency).
• format: A string that defines the format to apply to the value.
• culture (optional): A string that specifies the culture in which the value is formatted.

Note that the format string in the FORMAT() function is case-sensitive. For example, ‘MM’ represents months, while ‘mm’ represents minutes.

How to Use the FORMAT() Function in SQL

Let’s how how we can use the FORMAT() function on the SQL server to format dates, times, numbers, and currencies.

Formatting Dates

When dealing with dates, the FORMAT function allows you to display dates in various formats.

Here are some examples:

SELECT FORMAT(GETDATE(), 'yyyy-MM-dd') AS FormattedDate;

SELECT FORMAT(GETDATE(), 'dddd, MMMM dd, yyyy') AS FormattedDate;

Let’s suppose the date is 4th July 2024. The first example would format it as 2024-07-04, while in the second example, it is formatted as Thursday, July 04, 2024.

Formatting Time

Formatting time works similarly to dates. Here are some examples:

SELECT FORMAT(GETDATE(), 'HH:mm:ss') AS FormattedTime;

SELECT FORMAT(GETDATE(), 'hh:mm tt') AS FormattedTime;

Here, the first query formats the time in a 24-hour format, whereas the second one uses a 12-hour format with AM/PM notation. So, if the time is 2:30 in the afternoon, the first format would show it as 14:30:15 while the second format would show it as 02:30 PM.

Formatting Numbers

The FORMAT function is also highly useful for formatting numbers. For instance:

SELECT FORMAT(1234567.89, 'N') AS FormattedNumber;

SELECT FORMAT(1234567.89, '0.00') AS FormattedNumber;

In the first query, the number is formatted with commas as thousands separators (1,234,567.89). Whereas, in the second query, the number is formatted to two decimal places (1234567.89)

Formatting Currency

Formatting currency values is another common use of the FORMAT function:

SELECT FORMAT(1234567.89, 'C', 'en-US') AS FormattedCurrency;

SELECT FORMAT(1234567.89, 'C', 'fr-FR') AS FormattedCurrency;

Here, the first query formats the number as US currency ($1,234,567.89), and the second one formats it as French currency (1 234 567,89 €)

Practical Applications

The FORMAT function is immensely practical in various scenarios, such as:

• Generating Reports: Customize the appearance of data in reports to match regional formats or user preferences.
• Data Export: Format data before exporting it to ensure consistency and readability.
• User Interfaces: Display data in applications with the appropriate formatting for better user experience.

Conclusion

The FORMAT function in SQL is a versatile and powerful tool for data formatting. Whether you’re dealing with dates, times, numbers, or currency, this function helps you present your data in a clear and culturally appropriate manner. By mastering the FORMAT function, you can enhance the readability and professionalism of your data presentations. Moreover, it ensures that everybody you present to, can read and understand your data.

Learn More: SQL: A Full Fledged Guide from Basics to Advanced Level

Frequently Asked Questions

K.C. Sabreena Basheer

Sabreena Basheer is an architect-turned-writer who's passionate about documenting anything that interests her. She's currently exploring the world of AI and Data Science as a Content Manager at Analytics Vidhya.