SQL Tutorial: Formatting dates for reporting

The last lesson took you through generating and working with dates and their component parts. In this lesson, we will take those dates and see what formatting options are available. SQL Server has three formatting options available to cast, convert, and Format. The CAST function has been in SQL Server for over two decades. It lets us 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. Let's look at an example of casting. We will start with three variables of different types. For each of these we will perform a CAST operation. We will take some date and turn it into a string, some string into a datetime two and old datetime 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 I'll leave up to you. The convert function also goes back at least to SQL Server 2000. It is useful for converting between data types just like cast. Unlike cast, we get some input in how our dates should look as we convert them to strings. The convert function is specific to T SQL, meaning you should not expect to find it outside of SQL Server. We can see how the convert function works using this date. The convert function takes three parameters, a data type, an input, and an optional style. We can see what some of these styles do to 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 one we get a two digit year and 101 prints a four digit year. Another example is to use style 120, which prints in ODBC standard format to the second, ignoring milliseconds and microseconds. Microsoft supports a few dozen distinct conversion styles. We already saw United States format, but that's not the only national format. We can use 3 or 103 to print in the British or French national format. There are others as well, such as German, Japanese and Italian. We can format in the ISO or ODBC standards for interoperability. There is even the ISO 8601 standard, which is my preference. The format function arrived in SQL Server 2012. It adds much more flexibility in reporting than either CAST or Convert, but is also specific to T SQL because it uses the. NET framework for conversion and is single threaded, it can be slower than cast or convert. Format takes three parameters, an input value, a format code, and an optional culture. Using different cultures, I can print out dates for locales. I can also specify my own custom format. The format function can be slower than cast or convert. On my machine we can see that Starting at around 100,000 rows, format becomes slower than either function. In other examples people have run, format becomes unbearable in the 50,000 or 100,000 record range for data sets of a few hundred or a few thousand rows. You will probably not notice any delay when using format, but keep this in mind as you scale up your data. With these three functions in hand, let's dig into exercises.