Translating date strings

1. Translating date strings

In the last lesson, we learned about the FROMPARTS() series of functions, which build dates based off of integers. More typically, however, we will want to translate strings to dates like when reading data from CSVs or external sources. SQL Server has a number of techniques at our disposal to translate strings to date types.

2. Casting strings

The first function we will look at is CAST(). We saw CAST() already in Chapter 1 when formatting dates for reporting. Here, we take a string and cast it as a date type. In this case, because my server uses US English as its locale, it can innately understand that '09/14/99' is actually September 14th of 1999. On a SQL Server instance with a French locale, this would be an error as it would look for the 9th day of the 14th month of 1999. CAST() is fast and is the ANSI standard, so it makes sense to use this as a default.

3. Converting strings

We can, of course, also use the CONVERT() function here. Just like CAST(), CONVERT() serves multiple purposes and allows us to take a string and turn it into a date type. In this case, we convert a long string date to a DATETIME2 with a precision of 1 millisecond. Because CONVERT() is not an ANSI standard, it is probably better to use CAST() instead of CONVERT() for this type of conversion. CONVERT() doesn't gain us anything like it does when formatting date values for reporting, so I'd recommend just using CAST() instead.

4. Parsing strings

The PARSE() function lets us translate locale-specific dates. It uses the dot-NET framework to perform string translation, which makes it a powerful function. For example, December 25th is Christmas, or Weihnachten in Germany. Germans write out long-form dates like this, with a two-digit day followed by the month name followed by the year. We can use the PARSE() function to translate this row. The output is exactly what we would expect: a DATE type. As a quick note, this is useful not only for translating non-default locales but we can handle even strings in our default locale.

5. The cost of parsing

Next, let's talk about the PARSE() function's performance for a moment so you can see just how slow it is. First, the CONVERT() function was able to make just a little over 250 thousand translations per second. Next up, CAST() clocked in at about 240 thousand translations per second. Although this does look like a big difference, I should note that CAST() and CONVERT() actually run the same underlying function in SQL Server's code, so this difference is noise. The performance difference for PARSE(), however, is anything but noise. It is an order of magnitude worse than the other functions. If you have a large number of rows and can use CAST() or CONVERT(), I recommend using one of those and saving PARSE() for small data sets or cases when you are willing to trade the performance hit for the flexibility.

6. Setting languages

One last thing to hit before the exercises is the `SET LANGUAGE` syntax. You can use this command to change the language in your current session. This command changes the way SQL Server parses strings for dates and displays error and warning messages. In this case, I switch the language to French and try to parse a couple of dates. These casts would fail in English. But they succeed here, showing the correct date.

7. Let's practice!

Now that we have some clarity on the three leading functions for translating strings into date types, let's solidify that knowledge with a few exercises.

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.