1. Formatting dates for reporting
The last video was a walk-through of generating and working with dates and their component parts. We'll now take those dates and look at what formatting options are available for them.
2. Formatting functions
SQL Server provides three formatting options for us: CAST(), CONVERT(), and FORMAT().
3. The CAST() function
The CAST() function has been in SQL Server for over two decades.
It helps convert one data type to another data type, such as an integer to a decimal.
When using CAST(), we have no control over the way the function turns our dates into strings.
On the positive side, though, CAST() is part of the ANSI SQL standard, which means almost every database which implements SQL will have it.
4. Using the CAST() function
Let's look at an example of casting.
For each of the three variables shown, we will perform a CAST() operation. We will turn SomeDate into a string, SomeString into a DATETIME2, and OldDateTime into a string.
As you can see, the CAST() function successfully converts each of these, returning a string, a date, and a "well-formatted" string, respectively. How well it's formatted is up to personal preference!
5. The CONVERT() function
The CONVERT() function also goes back to at least SQL Server 2000.
It is useful for converting between data types, just like CAST().
Unlike CAST(), however, we do get some input into how our dates will look when we convert them to strings.
The CONVERT() function is specific to T-SQL, meaning we should not expect to find it outside of SQL Server.
6. Using the CONVERT() function
Let's have a look at how the CONVERT() function works using the declared date shown.
The CONVERT() function takes three input parameters: a data type, an input, and an optional style. Let's look at how some of these styles change our strings.
Style 0 prints out text the same way that CAST() does for a DATETIME type. Using styles 1 or 101, we can print dates in United States common format. The difference is that with 1, we get a two-digit year and 101 prints a four-digit year. Another example is to use style 120, which prints in a standard DATETIME2 format to the second, called the ODBC canonical standard, ignoring milliseconds and microseconds.
7. Sample CONVERT() styles
SQL Server supports a few dozen distinct conversion styles. We already saw the United States format, but that's not the only national format.
We can use 3 or 103 to print in the British/French national format. Other formats shown include German, Japanese, and Italian.
For interoperability, the ISO or ODBC standards are recommended. We'd recommend the ISO8601 standard for the most interoperability across systems and platforms.
8. The FORMAT() function
The FORMAT() function arrived in SQL Server 2012.
This adds much more flexibility in reporting than CAST() or CONVERT(), but is also specific to SQL Server.
It uses the dot-NET framework for conversion and is single-threaded, so it can be slower than CAST() or CONVERT().
9. Using the FORMAT() function
FORMAT() takes three parameters: an input, a format code, and an optional culture.
Using different cultures, we can print dates for different locales. We can also specify a custom format!
10. How slow is FORMAT()?
The FORMAT() function can be slower than CAST() or CONVERT(). In the example shown, we can see that starting at around 100,000 rows, FORMAT() becomes slower than either function. In other examples people have run, FORMAT() becomes practically unusable in the 50,000 or 100,000 record range.
For datasets of less than a few thousand rows, a delay using FORMAT() is probably not noticeable, but performance is something to keep in mind when thinking about scalability.
11. Let's practice!
Let's do some formatting!