For an instructor lead, in-depth look at learning SQL click below.
The FORMAT function is a built-in string function in SQL. It helps to format a specified value into a specific format. What does it mean? In simpler terms, it’s like getting a certain output in the form and structure we desire.
Now, the real question is, where is this function generally used? Well, the FORMAT function can be quite useful when you want to display data in a different format, especially date/time and number types. Let’s look at some practical examples of how it can be successfully used:
Using FORMAT Function for Date/Time Data Types
|
1 2 3 4 |
--Using FORMAT function to get date in 'dd-MM-yyyy' format SELECT FORMAT (GETDATE(), 'dd-MM-yyyy') AS 'Date' |
In the above SQL code, we are using the FORMAT function to change the format in which the current date is displayed. GETDATE() function is used to fetch the current date, and ‘dd-MM-yyyy’ is the format we want the date in.
Using FORMAT Function for Numeric Data Types
|
1 2 3 4 |
--Using FORMAT function to format numeric values SELECT FORMAT(12345, '###,###.00') as 'Numeric Format' |
This SQL code uses the FORMAT function to display a number (12345) in a specific format that includes a comma (,) as the thousand separator and a decimal point (.) for fractions. The output of this command will be ‘12,345.00’.
Formatting NULL values
|
1 2 3 4 5 |
-- Using FORMAT function to handle NULL values DECLARE @datevalue AS date; SELECT FORMAT(@datevalue,'dd/MM/yyyy') AS 'NULL Handling'; |
In this example, we have a null date value. When we run the code, the FORMAT function does not return any error, but an empty string (”) instead. This is particularly useful in avoiding runtime errors due to NULL values in our data.
Conclusion
The FORMAT Function in SQL is a versatile tool, aiding the user in customizing data presentation to specific requirements. It is most beneficial when dealing with date, time, and numeric data, but is not limited to those fields.
Remember, mastering SQL requires practice. Try out these examples and play around with the FORMAT function on your own to get a better feel of how it works.