Copyright

SQL: FORMAT Statement

Instructor: Martin Gibbs

Martin has 16 years experience in Human Resources Information Systems and has a PhD in Information Technology Management. He is an adjunct professor of computer science and computer programming.

Sometimes data in databases needs to be presented differently than how it is stored in the database. This lesson will cover the FORMAT statement, covering methods for making output more meaningful.

FORMAT

The SQL FORMAT statement is very powerful for displaying data in a meaningful way to end users (or even database administrators!). Take a very complex number for example: 234.25334331. Users don't want to see all those numbers, but may want to only see 234.35. Using FORMAT and its methods allow these types of operations.

Remember that the actual data in the table is not being altered: FORMAT is only creating a type of a view or presentation of the data; you don't have to worry that the underlying data is altered in any way. It is simply a great tool for presenting data in a readable/understandable format.

Basic syntax

The basic syntax for using FORMAT is below. The keyword FORMAT is placed before the column/field name that is being formatted, followed by the specific formatting command. The example below shows the basic syntax.


SELECT FORMAT(column, format_string)
  FROM table_name;


Examples

Each database management system (MySQL, SQL Server, Oracle, etc.) has its own variations on the formatting of text, numbers, and dates. However, once you understand the basic concept, it becomes easier to learn the nuances and apply the formatting.

Format Date

Dates are often stored differently from how the users want to see them. In many databases, the dates are stored year, month, day (YYYY/MM/DD), but users would rather see a different format. The following example changes the format of dateAdded for the table tblArtist to MM-DD-YYYY:


SELECT FORMAT(dateEntered, 'MM-DD-YYYY')
  FROM tblArtist;


The result would look something like 05-15-2017.

Format Date: SQL Server

As we mentioned, there are nuances between database systems: SQL Server 2012 provides a FORMAT function that formats the date. The basic syntax is similar, except that the recommended procedure is to declare a variable based on the column/field. In this case the variable is named with the @ symbol,. In the following example, DateEntered is set to equal the dateEntered field in the tblAlbum table.


DECLARE @DateEntered DATETIME = tblAlbum.dateEntered
  SELECT FORMAT ( @DateEntered, 'd', 'en-US')
  AS [Using 'en-US' Culture]


Format Number

FORMAT can be used to format a number to strip out any extra decimal places. When used by default, it formats the number to the following: ###,###,###.##. This means that a long pay rate, e.g., 38.3438843 would be trimmed to 38.34. If we use the following syntax, we will get that number.


SELECT FORMAT(38.3438843, 2);


Example: Format an SSN in SQL Server

Many common elements, such as telephone numbers, zip codes, social security numbers, are stored in varying ways in databases. Some include the dashes, or parenthesis, while others are just plain text data. As a database administrator, the SQL FORMAT function can be useful to display a social security number with the dashes, even if the value isn't stored that way. In SQL Server, the code is written as follows:


To unlock this lesson you must be a Study.com Member.
Create your account

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Try it risk-free for 30 days

Earning College Credit

Did you know… We have over 200 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it risk-free for 30 days!
Create an account
Support