SQL Tutorial: Formatting dates for reporting

Published: 14 March 2020
on channel: DataCamp

499

Want to learn more? Take the full course at https://learn.datacamp.com/courses/ti... at your own pace. More than a video, you'll learn hands-on coding & quickly apply skills to your daily work.

---
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 us: 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 SomeDate and turn it 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, 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 1, 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 ISO8601 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 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.

#DataCamp #SQLTutorial #SQL #Building #dates #TimeSeries #Analysis #SQLServer

On this page of the site you can watch the video online SQL Tutorial: Formatting dates for reporting with a duration of hours minute second in good quality, which was uploaded by the user DataCamp 14 March 2020, share the link with friends and acquaintances, this video has already been watched 499 times on youtube and it was liked by 9 viewers. Enjoy your viewing!