SQL FORMAT() Function

A field is formatted using the SQL FORMAT () function to get it into a standard format that may be displayed.

The time and date are displayed using several layouts and representations in SQL using the date format.

• To format the date and time data types from a date column (data types date, datetime, datetime2, smalldatetime, datetimeoffset, etc.) in a table or a variable like GETDATE, use the FORMAT() function.
• Use SELECT FORMAT (getdate(), 'dd/MM/yyyy') as the date to obtain DD/MM/YYYY.
• Use SELECT FORMAT (getdate(), "MM-DD-YY" as the date) to obtain MM-DD-YY.

Syntax:

SELECT FORMAT (column_name, format) FROM table name;

The column name in this case indicates the area where formatting is necessary. The arrangement is also determined by the format.

As you can see, we made use of a variety of choices for formatting the date and time, which are given below.

Pattern Description:

• dd - this is day of month from 01-31
• dddd - this is the day spelled out
• MM - this is the month number from 01-12
• MMM - month name abbreviated
• MMMM - this is the month spelled out
• yy - this is the year with two digits
• yyyy - this is the year with four digits
• hh - this is the hour from 01-12
• HH - this is the hour from 00-23
• mm - this is the minute from 00-59
• ss - this is the second from 00-59
• tt - this shows either AM or PM
• d - this is day of month from 1-31 (if this is used on its own it will display the entire date)
• us - this shows the date using the US culture which is MM/DD/YYYY

Example 1: Table: Shopping

SELECT MerchandiseName, Cost, 
FORMAT (Now (),'YYYY-MM-DD') AS PerDate FROM Merchandise;

The Above example chooses any item from the column named as MerchandiseName and cost for the particular day’s date from the Shopping table.

Example 2: SQL Server FORMAT Examples for Formatting Dates:

SELECT FORMAT (getdate(), 'dd-MM-yy') as date
GO

If this was run for March 26, 2022 the output would be:

Example 3: Let's try another one:

SELECT FORMAT (getdate(), 'hh:mm:ss') as time
GO

Output:

Example 4: Below is a list of date and datetime formats with an example of the output. The current date used for all of these examples is "2021-03-21 11:36:14.840".

Example 5: The following example returns a simple date formatted for different cultures.

DECLARE @d DATE = '11/22/2020';
SELECT FORMAT( @d, 'd', 'en-US' ) 'US English'  
      ,FORMAT( @d, 'd', 'en-gb' ) 'Great Britain English'  
      ,FORMAT( @d, 'd', 'de-de' ) 'German'  
      ,FORMAT( @d, 'd', 'zh-cn' ) 'Simplified Chinese (PRC)';  
  
SELECT FORMAT( @d, 'D', 'en-US' ) 'US English'  
      ,FORMAT( @d, 'D', 'en-gb' ) 'Great Britain English'  
      ,FORMAT( @d, 'D', 'de-de' ) 'German'  
      ,FORMAT( @d, 'D', 'zh-cn' ) 'Chinese (Simplified PRC)';  

Result:

Example 6: You’d like to display a date value in another format in SQL Server.

Our database has a table named company with data in the columns id (primary key), name, and start_date.

For each company, let’s convert their start date to a new format, ‘YYYY/MM/DD’, where YYYY is a 4-digit year, MM is a 2-digit month, and DD is a 2-digit day.

Example 7: We’ll use the CONVERT() function. Here’s the query you’d write:

SELECT
  CONVERT(NVARCHAR, start_date, 111 ) AS new_date
FROM company;

Result:

Discussion: Use the CONVERT() function to change the format of a date from a given column or expression.

This function takes three arguments:

• The new data type (in our example, NVARCHAR).
• An expression or column name containing the date to format (in our example, the start_date column).
• An optional style code, as an integer. (In our example, the ‘111’ style displays the date using slashes to separate the parts.)

Example 8: The table below presents the most popular date style codes:

The T-SQL documentation contains a detailed list of format styles.

The aforementioned query converted Lisa Bank's 2019-01-20 date into a string that contained the date "2019/01/20."

The FORMAT() method can be used in SQL Server 2012 and later to modify date/time formats. To select the preferred format, use the following characters:

Here’s the query you would write using FORMAT():

SELECT
  FORMAT(start_date, ‘yyyy-MM-dd’ ) AS new_date
FROM company;

The datetime/date/time value to be formatted is the first input. The second is a string that contains the new format's pattern. The data type that this function outputs is NVARCHAR. If you use SQL Server 2012 or later and want to convert dates and times to strings that contain the formatted dates, use FORMAT().

Example 9: Culture is an additional FORMAT function option. It is possible to get regional formatting by using the culture option. The cultural codes to use with FORMAT are listed below.

For example in the USA, the format would be like:

SELECT FORMAT (getdate(), 'd', 'en-us') as date
GO

In the USA the format is month, day, year.

If this was run for March 21, 2018 the output would be: 3/21/2018.

Example 10: Another example where we will use the Spanish culture in Bolivia (es-bo):

SELECT FORMAT (getdate(), 'd', 'es-bo') as date
GO

In Bolivia the format is day, month, year.

If this was run for March 21, 2021 the output would be: 21/03/2021.

For values that include both a date and a time in SQL, the datetime data type is utilized. According to Microsoft, it is a date and time that is based on a 24-hour clock and contains fractional seconds.

The complexity of the situation is increased by SQL's several data types that combine date and time representations. The DATETIME is the one that is used the most frequently because it has been around since prior versions of SQL. The format for DATETIME values returned by SQL is "YYYY-MM-DD hh:mm:ss." From "1753-01-01 00:00:00" to "9999-12-31 23:59:59.997," the supported range is. Let's look more closely at the datetime type.

Please check the following table to know more about the SQL datetime type.

Note: The details above apply for datetime type in Transact-SQL and SQL Server.

Example 1: Implicit conversion from smalldatetime to datetime:

The hours and minutes are copied while conversion from smalldatetime type. The fractional seconds and seconds are both set to 0. The output of converting a smalldatetime value to a datetime value is displayed in the code below.

DECLARE @smalldatetime smalldatetime = '2020-12-01 12:32';  
DECLARE @datetime datetime = @smalldatetime;  
   
SELECT @datetime AS '@datetime', @smalldatetime AS '@smalldatetime';

Result:

Example 2: Use the cast () and convert () methods to explicitly convert other date and time types to data time types. To acquaint yourself with the formats of all date and time kinds, please see the table below.

The T-SQL FORMAT() method can be used to format numeric types. The number, the format, and an optional third argument, "culture," are all required arguments for this function.

It gives back a formatted nvarchar string.

A format string containing the format is provided. How the output should be formatted is specified by a format string.

Example:

SELECT FORMAT(1, 'N');

Result:

I used N as the second argument in this instance. This specifier for a number's conventional numeric format is used. The output is structured with integral and decimal digits, group separators, and a decimal separator with an optional negative sign when this specific format specifier (N) is used. Both N and n are acceptable because this argument is case-insensitive.

1. Decimal Places:

Example 1: Here’s another example, this time using N1 to specify that we only want one decimal place:

SELECT FORMAT(1, 'N1');

Result:

Example 2: But you can also increase the number of decimal places too:

SELECT FORMAT(1, 'N7');

Result:

In these examples, the 1 and 7 are known as precision specifiers.

Specifiers for precision are not required. They can range in value from 0 to 99, indicating the degree of precision of the outcome. Depending on the format specifier being used, it behaves differently. It will indicate the total number of digits in the result for some format specifiers, and the number of decimal places for others. Other times, it will be completely disregarded. To examine how accuracy specifiers affect the output from each format specifier, view the complete list of Common Numeric Format specifiers.

2. Percent:

Examples: The P parameter, which formats the number as a percentage value, is used in the examples that follow. The number is multiplied by 100 before a localized percent indicator is added.

SELECT FORMAT(1, 'P') AS 'Example 1', 
    FORMAT(.375, 'P') AS 'Example 2', 
    FORMAT(.0375, 'P', 'tr-tr') AS 'Example 3';

Result:

Keep in mind that "Example 3" has a third input that determines the output culture. For Turkish, I'm going to utilize tr-tr. As a result, the number is followed by the % sign (instead of appended, like the others). Additionally, it leads to the decimal separator being used as a comma.

If you don't make the "culture" argument, the current session's language is used.

You might expect that based on your current language or the merit of any "culture" argument, you might obtain completely diverse answers. For other examples, see How Language Settings can Affect your FORMAT() Results.

3. Currency:

Example 1: You can use C to return a currency value.

SELECT FORMAT(1, 'C');

Result:

Example 2: You can also add in the locale as a third parameter.

SELECT 
    FORMAT(1, 'C', 'fr-FR') AS France, 
    FORMAT(1, 'C', 'th-TH') AS Thailand, 
    FORMAT(1, 'C', 'ja-JP') AS Japan;

Result:

Example 3: Exponential (Scientific)

You can use E to specify exponential notation.

SELECT FORMAT(1234.56789, 'E');

Result:

4. Hexadecimal:

Example 1: You can even format a number as hexadecimal. To do this, use the X argument:

SELECT FORMAT(10, 'X');

Result:

Hexadecimal is base 16. Therefore, the count goes 1, 2, 3, 4, 5, 6, 7, 8, 9, A, B, C, D, E, F before starting again (e.g. 10 in hexadecimal is the equivalent of 16 in decimal, and 11 is the equivalent of 17, etc). That’s why the example here results in A, because in hexadecimal, A comes after 9.

Example 2: Here’s another example, this time with a larger number:

SELECT FORMAT(7145, 'X');

Result:

5. Custom Format:

A custom numeric format string can be used to build your own unique format. This is made up of a number of unique numeric format specifiers that give your number a pattern.

Example 1: This is easier explained with an example:

SELECT FORMAT(123456789, '##-###-####');

Result:

Example 2: In this case, I use the digit placeholder (#). The digit placeholder allows me to specify a format for the number, using the # symbol as a placeholder for each number.

SELECT FORMAT(123456789, '#,#');

Result:

6. Leading Zeros:

Example 1: Another custom numeric format specifier is zero (0). You can use this to pad a number with leading zeros:

SELECT
    FORMAT(7, '000') AS 'Example 1',
    FORMAT(123, '0000') AS 'Example 2',
    FORMAT(123, '00000') AS 'Example 3';

Result:

Example 2: You can also use it to remove zeros:

SELECT 
    FORMAT(0123, '000') AS 'Example 1',
    FORMAT(0123, '0000') AS 'Example 2',
    FORMAT(000123, '00000') AS 'Example 3';

Result:

7. Numeric Format Specifiers Reference:

The following articles contain all the numeric format specifiers you can use with the FORMAT() function, along with T-SQL examples.

• Standard Numeric Format Strings
• Custom Numeric Format Strings

These are the same format specifiers that are supported by the .NET Framework (the FORMAT() function relies on the .NET Framework).

Example 1: The Sales.CurrencyRate table in the AdventureWorks2012 database returns 5 rows in the illustration below. EndOfDateRate is stored in the table as a column of type money. The column is formatted in this instance after it is returned unformatted by using the.NET Number format, General format, and Currency format types. See Standard Numeric Format Strings for additional details on these and other numerical formats.

SELECT TOP(5) CurrencyRateID, EndOfDayRate  
   ,FORMAT(EndOfDayRate, 'N', 'en-us') AS 'Number Format'  
   ,FORMAT(EndOfDayRate, 'G', 'en-us') AS 'General Format'  
   ,FORMAT(EndOfDayRate, 'C', 'en-us') AS 'Currency Format'  
FROM Sales.CurrencyRate  
ORDER BY CurrencyRateID;  

Output:

Example 2: This example specifies the German culture (de-de).

SELECT TOP(5) CurrencyRateID, EndOfDayRate  
      ,FORMAT(EndOfDayRate, 'N', 'de-de') AS 'Numeric Format'  
      ,FORMAT(EndOfDayRate, 'G', 'de-de') AS 'General Format'  
      ,FORMAT(EndOfDayRate, 'C', 'de-de') AS 'Currency Format'  
FROM Sales.CurrencyRate  
ORDER BY CurrencyRateID;  

Result:

The T-SQL FORMAT() function in SQL Server can be used to format a time data type. However, you'll must use a different data type if you want to include the AM/PM designator. This is due to the fact that the time is represented in this way because the time data type is primarily based on a 24-hour clock.

Example 1: Comparing ‘time’ with ‘datetime’:

Here’s an example to demonstrate what happens if you try to format the ‘time’ data type with the AM/PM designator:

SELECT 
  FORMAT(CAST('11:28:15' AS datetime), 'hh:mm tt') 'datetime',
  FORMAT(CAST('11:28:15' AS time), 'hh\:mm tt') 'time';

Result:

You will receive NULL if you attempt to add the AM/PM designator to a "time" variable.

As a result, you must first convert a time data type to another data type before formatting it if you want to add AM or PM.

Keep in mind that the FORMAT() function still returns the outcome as a string (unless the result is NULL).

If you're thinking why there is a backslash in the second format string, it is used to escape the colon and is only necessary for the time data type (and any periods). Learn more about that here.

Example 1: Beginner SQL programmers frequently neglect to prepare their code properly. Look at the code below if you believe formatting can be safely disregarded:

SELECT id, FirstName, LASTNAME, 
c.nAme FROM people p left JOIN cities AS c on c.id=p.cityid;

This SQL query above has been written without using any formatting rules. Now compare that with the formatted query below, which is the same code:

SELECT p.PersonId,
  p.FirstName,
  p.LastName,
  c.Name
  FROM Person AS p
LEFT JOIN City AS c
  ON p.CityId = c.CityId;

Do you see the difference? Which is more readable? Which query is easier to understand?

It goes without saying that the first query is difficult to read. In addition to this, it is difficult to make quick adjustments to this code. It would be difficult to compare this query to an other one that is similar. Even though the second query uses the exact same code, it is completely different since it is simple to read, easy to correct, and simple to compare to other well-formatted code. SQL code should be formatted correctly to help programmers avoid mistakes.

How to Format SQL Code:

There are different ways to approach formatting code.

1. Naming Objects:

I start by going through some general guidelines for naming database objects. These are the most typical regulations:

• Do not use the plural form of a table or column's name. Instead of employees, it is preferable to use employees.
• Use an underscore to connect words if the name of the table or column must contain more than one, as in employee city. Instead, some experts use the so-called CamelCase form, such as EmployeeCity. For various relational database systems, a separate preferred style exists.
• Check that the name is not already used as a keyword in SQL.
• If the name is the same as an SQL keyword, enclose the name within quotation marks.
• The name of an object in a database for a table or a column should be unique and not too long. Avoid special characters in the name like $, &, * , etc. (use only letters, numbers, and underscores).
• Use an underscore in a name only if necessary.
• Don't start the name with an underscore.
• Use comments only if necessary.
• Avoid using acronyms, but if you must, make sure that they are clear.
• Avoid naming a table and a column the same thing.
• For aliases for columns or tables, follow the same naming conventions.
• When generating aliases, utilize the AS keyword to make the code easier to read.

Example 1: For the primary key column avoid the name id. A good idea is to combine id with the name of a table, for example: id_employee.

Most experts recommend first writing keywords on a new line to the left and then the rest of the code to the right, like this:

SELECT p.PersonId,
       p.FirstName,
       p.LastName,
       c.Name
  FROM Person AS p
  JOIN City AS c
    ON p.CityId = c.CityId;

2. Indentation:

The readability of a SQL query can be greatly improved by liberal use of newlines. Creating new lines for each distinct query and each distinct column following a comma is a good idea. Likewise to this, placing spaces around the equals operator, before or after apostrophes, and immediately following commas are all wise choices.

Be consistent when indenting; either set aside a number of spaces for tabs or use the same number of spaces each time. Tabs are simpler to use than spaces since they need fewer clicks, but spaces are simpler to handle when cutting and pasting SQL code.

Example:

Style 1: each clause begins on a new line, none is indented:

select s.businessentityid, e.jobtitle
from sales.salesperson as s
inner join humanresources.employee as e on e.businessentityid = 
s.businessentityid

Style 2: Each clause and each set of arguments begins on a separate line. Each statement subordinate to SELECT is also indented:

SELECT
  S.Businessentityid, E.Jobtitle
FROM
  Sales.Salesperson AS S
  INNER JOIN
  Humanresources.Employee AS E
  ON E.Businessentityid = S.Businessentityid;

Style 3: indent all list items equally, together with all columns and table names:

SELECT  S.Businessentityid,
    E.Jobtitle,
    S.Salesquota,
    S.Salesytd,
    S.Saleslastyear
FROM Sales.Salesperson AS S
    INNER JOIN
    Humanresources.Employee
    AS E
ON E.Businessentityid = S.Businessentityid;

3. Commenting

Keep your code comments to a minimum. Of course, there are situations in which comments are required, but it is often preferable to utilize multiple-line comments, which are denoted by the letters /* opening and */ closing. Instead of beginning on a line that contains code that is evaluated, it is advised to write this kind of comment at the beginning of a new line. Using the same indentation, the comment must be written above the pertinent line of SQL code.

• Block comments /*…*/
• Inline comments —

Here are a couple of guidelines for comments that should be followed:

• Write comments clearly and consistently.
• Don’t add too many comments.

Example 1:

SELECT p.PersonId,
       p.FirstName,
       p.LastName,
       /* Name column is the name of the city: */
       p.Name,
FROM Person AS p
WHERE p.Name = 'New York';

Example 2: One-line comments can also be added to SQL code. A double hyphen (--) at the start of the comment text designates this sort of comment. After these characters, all material is regarded as commentary.

SELECT -- we have to delete this column p.PersonId,
       p.FirstName,
       p.LastName,
       p.Name
FROM Person AS p;

4. SELECT Queries

SELECT is the first word in the command for this kind of query. It is preferable to separate any additional columns after SELECT by giving them their own line. Line breaks should be indented. Consistently put commas at the end of lines rather than the beginning.

Example 1:

SELECT p.PersonId,
       p.FirstName, 
       p.LastName,
       c.Name
FROM Person AS p;

Example 2: For the keywords FROM, WHERE, ORDER BY, GROUP BY, and HAVING, write each on a new line without indentation.

SELECT p.PersonId,
       p.FirstName,
       p.LastName,
       p.Name,
FROM Person AS p
WHERE p.Name = 'New York';

Example 3: If the WHERE statement has multiple conditions, each condition should be separated by a new, indented line. The AND or OR conditional operators should also appear on a new, indented line.

SELECT p.PersonId,
       p.FirstName,
       p.LastName,
       p.Name
FROM Person AS p
WHERE p.Name = 'New York'
OR p.Name = 'Chicago';

5. JOIN Statements

Use new lines when joining tables with the operators INNER JOIN, LEFT JOIN, etc. In the JOIN statement, add a new indented line for the ON operator. However, use a new indented line before the AND or OR conditional operator if there are many conditions.

Example:

SELECT p.PersonId,
       p.FirstName,
       p.LastName,
       c.Name
FROM Person AS p
JOIN City AS c
ON p.CityId = c.CityId;

6. A Long and Nested SQL Query

Subqueries can be found in lengthy queries. The subquery in this case needs to be on a new, indented line.

Put a new line between each WHEN and END in the CASE structure.

SELECT p.PersonId,
       p.FirstName,
       p.LastName,
       CASE
       WHEN p.Age < 18 THEN 'below 18'
       WHEN p.Age >= 18 THEN '18 or more'
       END AS Age
FROM Person AS p;

7. Other Types of SQL Queries

There are similar rules for queries that modify, insert, or delete data.

Use indent for VALUES in insert queries:

INSERT INTO Car(id_car, name, year) VALUES(1, 'Audi', 2010);

In the case where you insert more rows in one query, write every row as a new line with indentation:

INSERT INTO Car(id_car, name, year) VALUES
(1, 'Audi', 2010) ,
(2, 'Skoda', 2015) ;

In a similar way, in an UPDATE query use SET and WHERE as in a SELECT statement, with a new line without indentation:

UPDATE Car
SET year = 2012
WHERE Name = 'Audi';

or in a DELETE query:

DELETE FROM Car
WHERE Name = 'Audi';

8. How Bad Formatting of SQL Code Leads to Problems

Example 1: One example of how poor formatting leads to problems can be seen in the query below, in which commas are placed at the starting of each line:

SELECT /* we have to delete this column */ p.PersonId
     , p.FirstName
     , p.LastName
     , p.Name
FROM Person AS p
WHERE p.Name = 'New York';

This might make sense at first, but if you comment out the first column in order to remove it, then the query would return an error.

Example 2: Another error can occur if you don't use indentation and new lines.

Select person.id_person, person.name, 
person.age, person.description, person.city 
from person  where person.age>20 and person.city = ( select name from city where id_city>20);

It would be quite simple to accidentally delete the WHERE clause in the subquery when you meant to remove it in the main query if the code was badly structured.

If the query is properly formatted, many issues will be simple to identify, especially if the query is lengthy and contains hundreds of lines of code.

9. SQL Capitalization

Make sure a database doesn't have case-sensitive collation before defining the capitalisation standards because this might lead to serious issues when experimenting with capitalization. There should be guidelines for:

• Reserved/key words (e.g. SELECT, DECLARE, CREATE, ALTER). Upper case is recommended
• Data types (int, nvarchar, varchar)
• Object names – identifiers (table, view and stored procedure names)
• System and built-in functions (SUBSTRING, ABS, LEFT)
• Variables: This will change the case of the identifiers in your code (variables, table names, column names, etc). Changeable options include Upper, Lower, Init Cap, and Unchanged. If you change it, the main screen will display a preview.
• Case of keywords: This will control how your SQL keywords are written in your code. Changeable options include Upper, Lower, Init Cap, and Unchanged. If you change it, the main screen will display a preview.
• convert case only option: When you use the Format SQL command, only the case will be altered; none of the other formatting options will be used.

10. Parentheses

In SELECT statements, function arguments, DDL statements, mathematical formulas, etc., parentheses may be utilized.

An expression's beginning parenthesis may be positioned on a new line, followed immediately by a line break. Another option is to start a new line after the closing parenthesis, with or without a line break.

Example:

Style 1 – no line breaks after opening or closing parentheses:

CREATE TABLE Humanresources.Department (
        Departmentid SMALLINT IDENTITY ( 1, 1 )
                                   NOT NULL,
        Name dbo.Name NOT NULL,
        Groupname dbo.Name NOT NULL,
        Modifieddate DATETIME NOT NULL,
        CONSTRAINT Pk_Department_Departmentid 
        PRIMARY KEY CLUSTERED ( Departmentid ASC )
           WITH ( PAD_INDEX = OFF, 
           STATISTICS_NORECOMPUTE = OFF, 
           IGNORE_DUP_KEY = OFF, 
           ALLOW_ROW_LOCKS = ON, 
           ALLOW_PAGE_LOCKS = ON ) ON [Primary] )
ON [Primary];

Style 2 – a line break after the opening parenthesis:

CREATE TABLE Humanresources.Department (
     Departmentid SMALLINT IDENTITY ( 1, 1 )
     NOT NULL,
     Name dbo.Name NOT NULL,
     Groupname dbo.Name NOT NULL,
     Modifieddate DATETIME NOT NULL,
     CONSTRAINT Pk_Department_Departmentid 
     PRIMARY KEY CLUSTERED ( Departmentid ASC )
     WITH ( PAD_INDEX = OFF, 
     STATISTICS_NORECOMPUTE = OFF, 
     IGNORE_DUP_KEY = OFF, 
     ALLOW_ROW_LOCKS = ON, 
     ALLOW_PAGE_LOCKS = ON ) 
    ON [Primary]);

Style 3 – line breaks after both opening and closing parentheses:

CREATE TABLE Humanresources.Department
(
  Departmentid SMALLINT IDENTITY 
  ( 
   1, 1
  ) NOT NULL,
  Name dbo.Name NOT NULL,
  Groupname dbo.Name NOT NULL,
  Modifieddate DATETIME NOT NULL,
  CONSTRAINT Pk_Department_Departmentid 
  PRIMARY KEY CLUSTERED 
  ( 
  Departmentid ASC 
  )
  WITH 
  ( 
   PAD_INDEX = OFF, 
   STATISTICS_NORECOMPUTE = OFF, 
   IGNORE_DUP_KEY = OFF, 
   ALLOW_ROW_LOCKS = ON, 
   ALLOW_PAGE_LOCKS = ON
   )
) ON [Primary]

11. SQL CASE RULES

• Use CamelCase capitalization and do not separate name parts with underscores: TableName, instead of Table_name, or use lower case and underscores to separate name parts: table_name
• Set standard abbreviations for frequently used objects, such as tbl for tables, or sp for stored procedures
• Use single quotation for characters, strings, binary and Unicode
• Set a rule for naming aliases
• Use indenting to align wrapped long lines
• Use parentheses in complex mathematical expressions
• Be consistent with indentation – use either tab or space
• Don’t avoid using line breaks to improve readability
• Code grouping – keep the lines that execute a certain task in separate code blocks
• Limit line length – wrap the lines longer than approximately 80 characters

12. Deeply nest the statements

• Be consistent, use basic and straightforward naming, yet employ confusing names. Avoid using too many acronyms.
• Create a challenging execution flow in your writing.
• Find a simple mechanism to enforce the rules once they have been established. It takes a lot of time and is quite likely to result in errors to manually perform all the criteria.

SQL FORMATTING RULES:

The ideal answer is to design a unique SQL style guide. There are no style or formatting guidelines, so it's all a question of preference. This presents a challenge but also a potential benefit. Since there isn't a single "optimal" SQL formatting method, it is up to the user and team to establish a set of formatting guidelines that will be used to every SQL code that is developed. The best thing that can be done to improve SQL readability is to have the entire team consistently uphold the established standard. That way, everyone will be familiar with the formatting used in the organization and be able to comprehend the code fast. Here are some fundamental principles to follow:

Example 1: Explain what SQL does in comments. If existing SQL code is changed, include the author's name, the date, a description of the changes, and refrain from asking any questions. Don't go overboard and make apparent comments. Put a new line after each significant SQL statement:

USE Adventureworks2012;
SELECT
         S.Businessentityid,
         E.Jobtitle
FROM Sales.Salesperson
WHERE S.Businessentityid > 10

Example 2: Put SQL keywords such as SELECT and FROM, built-in function names such as SUM, AVG, CASE, and data types such as INT, CHAR, NTEXT in the uppercase:

CREATE TABLE Sales.Customer(
        Customerid INT IDENTITY(1, 1) NOT NULL,
        Personid INT NULL,
        Storeid INT NULL,
        Territoryid INT NULL,
        Modifieddate DATETIME NOT NULL,
        CONSTRAINT Pk_Customer_Customerid PRIMARY KEY 
        CLUSTERED(Customerid ASC)
            WITH(PAD_INDEX = OFF, 
            STATISTICS_NORECOMPUTE = OFF)ON [Primary])
ON [Primary]

Example 3:

Style 1 – Keywords, types and identifiers in lowercase:

create table humanresources.department(
    departmentid smallint identity(1, 1) not null,
    name dbo.Name not null,
    groupname dbo.Name not null,
    modifieddate datetime not null,
    constraint pk_department_departmentid 
primary key clustered(departmentid asc)
    with(pad_index = off, allow_page_locks = on)on [primary])
on [primary];

Style 2 – Keywords in upper case, types in lowercase, identifiers in proper case:

CREATE TABLE Humanresources.Department(
     Departmentid smallint IDENTITY(1, 1) NOT NULL,
     Name dbo.Name NOT NULL,
     Groupname dbo.Name NOT NULL,
     Modifieddate datetime NOT NULL,
     CONSTRAINT Pk_Department_Departmentid 
PRIMARY KEY CLUSTERED(Departmentid ASC)
     WITH(PAD_INDEX = OFF, ALLOW_PAGE_LOCKS = ON)ON [Primary])
ON [Primary];

Summary: When working with other programmers, breaking the standard for SQL formatting can be very problematic. When adding changes to your code, proper formatting makes your code legible and helps prevent errors. I discussed some of the expert-recommended guidelines for writing cleaner code in this article. Employers respect employees that have a high work ethic and write clean SQL code. Your code demonstrates your degree of professionalism and demonstrates that you approach work in a serious, contemporary manner. Take on the challenge, and develop your programming skills!

How would I modify this simple SQL query to format the returned value:

SELECT EmployeeID
FROM dbo.RequestItems
WHERE ID=0;

If EmployeeID is 7135, this query should return 007135.

Example: Change the number 6 to whatever your total length needs to be:

SELECT REPLICATE('0',6-LEN(EmployeeId)) + EmployeeId

If the column is an INT, you can use RTRIM to implicitly convert it to a VARCHAR

SELECT REPLICATE('0',6-LEN(RTRIM(EmployeeId))) + RTRIM(EmployeeId)

And the code to remove these 0s and get back the 'real' number:

SELECT RIGHT(EmployeeId,(LEN(EmployeeId) - PATINDEX('%[^0]%',EmployeeId)) + 1)

Examples of adding a leading zero to a number in SQL, using various DBMSs.

1. Oracle

Example 1: Oracle has a TO_CHAR(number) function that allows us to add leading zeros to a number. It returns its result as a string in the specified format.

SELECT TO_CHAR(7, '000')
FROM DUAL;

Result:

Example 2: The 0 format element is what outputs the leading zeros. If we didn’t want leading zeros, we could use 9.

Here’s a comparison between 0 and 9 to demonstrate what I mean:

SELECT 
    TO_CHAR(1234.456, '000G000D00') AS "0",
    TO_CHAR(1234.456, '999G999D99') AS "9"
FROM DUAL;

Result:

Both the G and the D stand for group separators and decimal separators, respectively. For a complete list, go to this list of number format elements.

Oracle also includes a function called LPAD() that allows us to add leading zeros to an integer (or any other character). For a sample, see Oracle: 2 Ways to Format a Number with Leading Zeros.

2. PostgreSQL

Example 1: PostgreSQL also has a TO_CHAR() function, and it works like Oracle’s function of the same name:

SELECT TO_CHAR(7, '000');

Result:

Example 2: And here’s a comparison between 0 and 9:

SELECT 
    TO_CHAR(1234.456, '000G000D00') AS "0",
    TO_CHAR(1234.456, '999G999D99') AS "9";

Result:

For a complete list of template patterns that can be used with this PostgreSQL function, see Template Patterns & Modifiers for Numeric Formatting in Postgres.

Similar to Oracle, PostgreSQL includes a function called LPAD() that allows us to pad numbers with leading zeros. For an illustration, see PostgreSQL: Two Methods for Adding Leading Zeros.

3. MySQL

Example 1: The LPAD() function in MySQL enables us to insert a single character or a string of characters to pad the left portion of a string or number. As a result, we can use it to add zeros to our number:

SELECT LPAD(7, 3, 0);

Result:

Fortunately, we can pass the number as a numeric type, so there’s no need to convert it to a string first.

4. MariaDB

Example 1: Similar to MySQL, MariaDB includes an LPAD() function that enables us to insert a single character or a string of characters to pad the left portion of a string or number:

SELECT LPAD(7, 3, 0);

Result:

There is no need to first convert the number to a string because, like with MySQL, we can pass the number as a numeric type.

For further information, see How to Pad a Number with Leading Zeros in MariaDB.

5. SQL Server

Example 1: SQL Server has a FORMAT() function that permits us to format numbers using a format string:

SELECT FORMAT(7, '000');

Result:

Example 2: Here it is with a group separator and decimal separator:

SELECT FORMAT(1234, '0,000.00');

Result:

See Add Leading & Trailing Zeros in SQL Server for more.

6. SQLite

Example 1: SQLite has a PRINTF() function that can be used to add leading zeros to a number:

SELECT PRINTF('%03d', 7);

Result:

Example 1: In this case, we are going to format the Currency values based on the specified culture.

DECLARE @Sales INT = 3325 
SELECT FORMAT(@Sales, 'c', 'en-US' ) AS 'USA Currency'
SELECT FORMAT(@Sales, 'c', 'ru-RU' ) AS 'Russian Currency'
SELECT FORMAT(@Sales, 'c', 'hi-IN' ) AS 'Indian Currency'
SELECT FORMAT(@Sales, 'c', 'de-DE' ) AS 'Indian Currency'

Example 2: This is how the Currency is formatted in this SQL Format function example. This method merely involves putting the country's currency symbols before the Money or Value.

DECLARE @Sales DECIMAL(8, 4) = 3325.2569 
SELECT FORMAT(@Sales, 'c' ) AS 'Result 1'
SELECT FORMAT(@Sales, 'c0' ) AS 'Result 2'
SELECT FORMAT(@Sales, 'c1' ) AS 'Result 3'
SELECT FORMAT(@Sales, 'c2' ) AS 'Result 4'
SELECT FORMAT(@Sales, 'c3' ) AS 'Result 5'
SELECT FORMAT(@Sales, 'c4' ) AS 'Result 6'

Example 3: In this example we are going to format the decimal values in Currency using this.

DECLARE @Sales DECIMAL(8, 4) = 3325.2569 
SELECT FORMAT(@Sales, 'c', 'en-US' ) AS 'Result 1'
SELECT FORMAT(@Sales, 'c0', 'hi-IN' ) AS 'Result 2'
SELECT FORMAT(@Sales, 'c1', 'ru-RU') AS 'Result 3'
SELECT FORMAT(@Sales, 'c2', 'fr-FR' ) AS 'Result 4'
SELECT FORMAT(@Sales, 'c3', 'de-DE') AS 'Result 5'
SELECT FORMAT(@Sales, 'c4', 'zh-CN') AS 'Result 6'

Microsoft Access: You can also use the Format function in a query in Microsoft Access.

For example: In this query, we have used the Format function as follows:

Expr1: Format(UnitPrice,"Currency")

The UnitPrice field, formatted as a currency value, will be returned by this query. Since no label was specified, the outcomes will be shown in a column referred to as Expr1. A more appropriate column name can be used in place of Expr1.

For example:

FormattedValue: Format(UnitPrice,"Currency")

The results would now be displayed in a column called FormattedValue.

The FORMAT() function can be used to format numbers using commas. The number and a format string are passed when using this function. The format string specifies how the returned number will be formatted.

Based on the values we supply, the FORMAT() method returns a formatted string representation of the number.

Example 1: We have a quick and simple method for formatting numbers with commas placed where necessary thanks to SQL Server. 1234.56, for instance, may become 1,234.56. Alternatively, if that's the locale you're using, it can become 1.234,56.

Here’s an example to demonstrate:

SELECT 
    FORMAT(123456.789, 'N') AS "Number",
    FORMAT(123456.789, 'P') AS "Percent",
    FORMAT(123456.789, 'C') AS "Currency";

Result:

This example demonstrates three of the standard numeric format strings:

• N can be used if all we need is the formatted number.
• Use P to format it as a percentage if that's what we want.
• We can use C as money.

Based on the language being used in the current session, SQL Server is intelligent enough to recognize your current locale and formats the result accordingly. This is significant since not everyone resides in a nation where the thousands separator is a comma and the decimal separator is a full stop.

Example 2: You can explicitly select the locale using the third optional "culture" argument that the FORMAT() function takes.

SELECT 
    FORMAT(123456.789, 'N', 'de-de') AS "Number",
    FORMAT(123456.789, 'P', 'de-de') AS "Percent",
    FORMAT(123456.789, 'C', 'de-de') AS "Currency";

Result:

The findings will be presented in accordance with German standards because I chose de-de as the culture in this instance. As can be seen, the decimal separator is a comma, and the group separator is full stops.

The language of the current session is utilized if the third parameter is left out.

Example 3: Large numbers can occasionally benefit from having thousand separators (commas) added to make them simpler to view. For an int or bigint, there is no straightforward way to do this in SQL, but it can be done by first converting to a money type. The outcome is achieved via the following solution:

DECLARE @BigNumber BIGINT
SET @BigNumber = 1234567891234

SELECT REPLACE(CONVERT(VARCHAR,CONVERT(MONEY,@BigNumber),1), '.00','')

Output:

This works by converting the money number to a VARCHAR with style 1, which adds the commas as well as a decimal point and two decimal places, using the CONVERT function. I removed the decimal places using the REPLACE function because we don't actually need them.

Although a User Defined Function (UDF) might be preferred if performance is a concern, a CLR should work better.

I'll conclude by saying that formatting numbers is something that should ideally be left to the presentation layer rather than SQL (such as Reporting Services or Excel), which is likely why Microsoft hasn't included the technology to achieve this. However, there are occasions when it is practical to provide huge number of possibilities with SQL Server.

Example 4: Postgresql uses the TO_CHAR() function to format integers, including formatting numbers with commas in the appropriate spot. It is compatible with the money data type, which outputs the value in the current locale. Additionally, proper use of commas is made based on the locale being used.

Let’s understand this with the help of an example to output a number with commas.

SELECT TO_CHAR(782933.63, 'fm999G999D99');

Let’s check the output of the above query.

• The format of the first argument is revealed by the second argument in the output, which is made up of a collection of numerical template patterns. In the query for a locale-aware group separator, commonly known as a "thousands separator," we have employed the G template pattern.
• The output uses a template pattern for a locale-aware decimal separator for D.
• In order to remove any potential automated trailing zeros and leading spaces from the result, we additionally employed fm, which stands for Fill Mode. For any digit, template pattern 9 is appropriate.