Validating if an expression is a date

1. Validating if an expression is a date

By now, you have learned quite some functions that help you manipulate dates and time. You noticed that the date functions don't necessarily need a date parameter in order to provide an accurate result. You can also provide a string or number and SQL Server will try to convert it to a date. But what if you want to verify if a given value is a date type? This is what this video is about. It will introduce you to the function that can be used to determine whether an expression is a valid date.

2. ISDATE(expression)

What SQL Server provides to validate whether an expression is a valid date or not is the ISDATE() function. It has a simple syntax, accepting one parameter, which is the expression to be evaluated. The output is 1 if the expression is evaluated as a valid date, time or datetime and 0 otherwise. This function has a technical limitation: if the expression is evaluated by SQL Server as a datetime2 type, the function will return 0, even if datetime2 means that the result is obviously a date.

3. ISDATE(expression)

Let's look at some examples of how to use this function. First, values are declared and assigned to four character strings: one is 2019-05-05, the second is 2019-01-XX, the third is a string that will be interpreted as a datetime2 value, because it has a very high precision, and the fourth is the same date, but with a precision of seconds, which will be interpreted as a datetime type. In the SELECT statement, ISDATE() is used to validate all variables. Can you predict what the results will be? The first value is a valid date. The second one is not, the third, the datetime2 is also not interpreted as a valid date. And the fourth variable is a datetime, so the result of ISDATE() is 1.

4. SET DATEFORMAT

There are functions that can impact the output of ISDATE(). One of the these functions is SET DATEFORMAT. You can use it to set the order of the date parts (year, month, day) when you want a character string to be interpreted as a valid date. There are several formats you can use, like month-day-year, day-month-year and so on.

5. SET DATEFORMAT

In this example, two strings are used that could be interpreted as the 31st of December, the only difference being that one starts with the month and the other with the day. If you set the DATEFORMAT to be day-month-year, the first date will not be interpreted as a correct date, but the second one will.

6. SET LANGUAGE

Another function that can change the ISDATE() results is SET LANGUAGE. You can use it to change how some messages are displayed and it also impacts how dates are interpreted. Different languages have different date formats so if you change the language, the date format is implicitly changed as well. Some valid languages in SQL Server are English, which is the default, Italian, Spanish, and so on. You can find in the MSDN documentation a list with all supported languages.

7. SET LANGUAGE

For example, if you set the language to English, which has the default date format month-day-year, and execute this query, the first date is valid, while the second one is not.

8. SET LANGUAGE

When changing the language to French, the default date format is day-month-year, so the first value is now invalid and the second one is a correct date.

9. Let's practice!

It's time to apply all of these by yourself and check whether some expressions are valid dates or not.

Create Your Free Account

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