sql formatterFormatting in SQL occurs when data is converted to a version of the data that is displayed in a manner, which is more visually appealing and/or more efficiently used by a Database Management System. Text, numbers, and other characters can be formatted so that they are displayed in formats that are more uniform or more structured. Values can be formatted in SQL with the use of functions such as the Format function, the to_char (CStr in Access) function, and the to_date (CDate in Access) function. Common applications of the Format function involve values using currency and date formats. Formatting can be either explicit or implicit; if you want to explicitly apply a format, then you should directly apply a means of changing a format (e.g. via a function) along with the selected format type. While formatting of datatypes can involve processes such as those for parsing, translating, and converting; formatting can take place when one writes code while using an approach that makes code easier to read.

Datatypes

In Oracle, currency values may have a data type of Number and in Access, currency values can have a data type of Money or Currency. Data types that may be used for currency values in MySQL include the Decimal datatype. I may focus more on SQL for Oracle in this article; however, if you also want to gain practice with other database options, such as Microsoft SQL Server, then you may find an Udemy class to be of interest. In Oracle, the default format for a date, is the dd-mon-yy (day-month-year) date format. As a demonstration of applying a different format for a date value, a Select statement will be used with a formatting option for the following table:

EMPLOYEE_ID EMPLOYEE_FIRST EMPLOYEE_LAST HIRE_DATE
123 Brad Foster 01-JAN-2012
124 Kayla Wheeler 01-JAN-2012
125 Ruby Weaver 01-JAN-2014
126 Lee Wong 01-MAY-2012
127 Melanie Bryant 01-JAN-2012
128 Carol Erickson 01-JAN-2012
129 Francis Pearson 01-MAY-2012
130 Taylor Cohen 01-JAN-2012
131 Jay Weber 01-JAN-2012
132 Louis Ingram 01-JAN-2014
133 Sheldon Cross 01-JAN-2012
134 Sally Riley 01-JAN-2014
135 Maryanne Waters 01-JAN-2012
136 Blake Manning 01-MAY-2012

 

SELECT
Employee_Id,
Employee_First,
Employee_Last,
TO_CHAR(Hire_Date, ‘MM-DD-YYYY HH:MI AM’)
AS
Formatted_Date
FROM
Employee_Tbl;

 

EMPLOYEE_ID
EMPLOYEE_FIRST
EMPLOYEE_LAST
HIRE_DATE
FORMATTED_DATE
123
Brad
Foster
01-JAN-2012
01-01-2012  12:00AM
124
Kayla
Wheeler
01-JAN-2012
01-01-2012 12:00 AM
125
Ruby
Weaver
01-JAN-2014
01-01-2014 12:00 AM
126
Lee
Wong
01-MAY-2012
05-01-2012 12:00 AM
127
Melanie
Bryant
01-JAN-2012
01-01-2012 12:00 AM
128
Carol
Erickson
01-JAN-2012
01-01-2012 12:00 AM
129
Francis
Pearson
01-MAY-2012
05-01-2012 12:00 AM
130
Taylor
Cohen
01-JAN-2012
01-01-2012 12:00 AM
131
Jay
Weber
01-JAN-2012
01-01-2012 12:00 AM
132
Louis
Ingram
01-JAN-2014
01-01-2014 12:00 AM
133
Sheldon
Cross
01-JAN-2012
01-01-2012 12:00 AM
134
Sally
Riley
01-JAN-2014
01-01-2014 12:00 AM
135
Maryanne
Waters
01-JAN-2012
01-01-2012 12:00 AM
136
Blake
Manning
01-MAY-2012
05-01-2012 12:00 AM

Note that the time portion for the Formatted_Date column values is displayed as “12:00 AM”; In Oracle and Access, the default time is set as midnight. In the following example, you will see a demonstration of the to_date function being used within an Insert statement:

Initial Table (used for a library to record books that have been checked out) :

ACCOUNT_ID BOOK_TITLE CHECKOUT_DATE CHECKIN_DATE
87563 Biology 15-MAY-2014 30-MAY-2014
62841 Literature: A Pocket Anthology 01-MAY-2014 20-MAY-2014
56422 Pete the Cat 06-MAY-2014 12-MAY-2014
68442 Soccer on Sunday 05-MAY-2014 12-MAY-2014
63587 Earth Science 01-MAY-2014 30-MAY-2014
62012 Software Abstractions 12-MAY-2014 29-MAY-2014
22745 Design: The Key Concepts 15-MAY-2014 30-MAY-2014
43288 Programming Introduction 26-MAY-2014 30-MAY-2014

In this case, an Insert statement (in Oracle SQL) is used when a book is checked in; sysdate is used for the new value for the Checkin_Date column as it will retrieve the current date and time.

INSERT INTO Library_Tbl
VALUES (000068527, ‘The Power of Now’,
TO_DATE
(’05-30-2014 09:30 AM’, ‘MM-DD-YYYY HH:MM AM’),
sysdate);

 

68527 The Power of Now 30-MAY-2014 03-JUN-2014

Note that, although the time portion of the to_date function had been added, the time had not been displayed along with the date in the output. When using the to_date function, the time is still stored in tables; however, you will have to explicitly retrieve the data for the time. You can find helpful tutorials that cover concepts, such as implicit and explicit conversions, on Udemy.

Formatting and The Where Clause

A factor to be mindful of is that when a Select statement is used along with a Where clause, even if the value of a column displays a dollar sign, you should not use the dollar sign in the Select statement. An example of a Select statement being used to select a specific value in a column containing currency values is as follows:

Table:

MENU_ITEM DESCRIPTION PRICE
1 Tropical Burst $3.00
2 Protein Peanut $4.00
3 Java Almond $4.00
4 Very Berry $3.00
5 Apple Orchard $3.00

 

SELECT * FROM Smoothie_Tbl
WHERE PRICE < 4.00;

 

MENU_ITEM DESCRIPTION PRICE
1 Tropical Burst $3.00
4 Very Berry $3.00
5 Apple Orchard $3.00

Note that the dollar sign had been omitted when the Where clause had been used to search for all columns from the Smoothie_Tbl that contained values of less than 4.00 for the Price column.

Readability

One can apply personal standards or groups can adapt a uniform standard in order to use formatting that increases the level of readability for code. When you refer back to code that you have previously written, it may be helpful with having notations that help you recall why each line of code had been added. Additionally, if someone who had not been the author of code reads prewritten code, it may helpful for them to be able to understand the objective of the code as well as to be able to easily read the code. If code is written using a format that is not easy to read, it may still produce the intended results, but it can require more time spent deciphering the code in cases such as when a program is being updated. Some formatting techniques to use when writing code are as follows:

Reporting

Formatting options can also be used for reporting. There are cases in which reports contain accurate data, but the formatting of the reports is not ideal. For instance; suppose that the Library_Tbl table used earlier had contained multiple instances of books being checked out for the same account number. As opposed to all of the instances being displayed along with the duplicate account number, a formatting option such as the SQL *PLUS Break command may be used as follows:

BREAK ON ACCOUNT_ID;

By using the Break command for the Library_Tbl, the output would still include all of the table data for books checked out under the same account numbers, but there would be empty spaces in the Account_Id column (after initial instances of the account numbers) where duplicate account numbers would have been displayed.

Summary

There is an abundant amount of formatting options available for each SQL version. This article has included some of the formatting options; if you are interested in discovering more formatting options, then I would like to suggest tutorials on Udemy. Formatting options can improve layouts for tables, general readability of code, as well as formats for reports. It is also important to acknowledge formats used in tables while performing operations, such as queries and updates, for databases.

Top courses in SQL

SQL and Data Visualization - The Complete Bootcamp
Raffi Sarkissian | SQL | PostgreSQL | Metabase
4.6 (20)
SQL - Beyond The Basics
Infinite Skills
4.4 (460)
The Complete SQL Bootcamp 2020: Go from Zero to Hero
Jose Portilla
4.7 (74,716)
Bestseller
The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert
Colt Steele, Ian Schoonover
4.6 (43,388)
Bestseller
SQL for Data Analysis: Weekender Crash Course for Beginners
A Course You'll Actually Finish, David Kim, Peter Sefton
4.3 (11,317)
Bestseller
Master SQL For Data Science
Imtiaz Ahmad
4.6 (5,873)
SQL & Database Design A-Z™: Learn MS SQL Server + PostgreSQL
Kirill Eremenko, Ilya Eremenko, SuperDataScience Team
4.4 (3,486)
Bestseller
Advanced SQL : SQL Expert Certification Preparation Course
Oracle Master Training • 60,000+ Students Worldwide, Code Star Academy
4.3 (1,672)
SQL for Beginners: Learn SQL using MySQL and Database Design
Tim Buchalka's Learn Programming Academy, Jon Avis - SQL Instructor
4.4 (5,291)

More SQL Courses

SQL students also learn

Empower your team. Lead the industry.

Get a subscription to a library of online courses and digital learning tools for your organization with Udemy for Business.

Request a demo