In this SQL Server tutorial, you will understand how to use the ISDATE function in SQL Server.
First, I will explain the ISDATE function in SQL Server, its syntax, and how to use it. With an example, you will understand how it works or how it evaluates the given value.
Then, you will use the ISDATE function in SQL Server with the datetime value on the table column. Additionally, you will understand how the ISDATE() function responds when the date and time value format is passed to it and based on the different languages.
ISDATE Function in SQL Server
ISDATE function in SQL Server verifies whether the given expression has a date and time value or not; if the given expression has a date and time value, it returns 1; it returns 0; in case the given expression does not have a date and time value.
The syntax is given below.
ISDATE(expression_value)Where,
- ISDATE(): It is the expression value to evaluate for date and time.
- expression_value: The value that needs to be identified as the date and time value. Specify the expression of character or string of type date and time data type, but don’t specify the expression of type datetime and smalldatetime.
- The expression length should be less than 4000 thousand characters.
Let’s see with an example; suppose you have a data value ‘2024-01-25’, and you need to validate whether this value is a date value, so use query blow.
SELECT ISDATE('2024-01-25') AS DateValue;
From the output, the expression ‘2024-01-25’ is a valid date because the ISDATE() function returns the value as 1, indicating the valid date and time value.
In the same way, you can also validate if the given expression has a time value; suppose you have value ’14:29:30′; to validate it using the ISDATE() function, use the query below.
SELECT ISDATE('14:29:30') AS TimeValue;
In the output, the ISDATE() function returns 1 for the expression ’14:29:30′, again indicating this value is a valid time value.
Using the ISDATE function in SQL Server, you can validate the given expression separately for date and time value.
Next, let’s see how to validate the expression containing both date and time values.
Checking Datetime Expression using ISDATE Function in SQL Server
If you have a string containing both date and time values, then you can verify whether that string is valid date and time value or not.
For example, if you have an expression or string value ‘2024-01-22 10:53:20’, to know whether this value is a valid datetime value or not, run the query below.
SELECT ISDATE('2024-01-22 10:53:20') AS CheckDateTimeValue;
As you can see in the above output, the ISDATE() function returns 1 for the expression ‘2024-01-22 10:53:20’, which indicates the valid datetime value containing both date and time values.
This is how to use the ISDATE function in SQL Server to check the given expression for datetime value.
Effects of Changing Dateformat and Language on ISDATE Function in SQL Server
Users may use different date formats based on their geographical location and language. So, SQL Server provides a way to format the data and language.
The commands to change the date format and language are given below.
SET LANGUAGE language_name;The above command sets the language of the current session to a different language, such as us_english, etc.
The next command allows you to change the format of the date.
SET DATEFORMAT specify_dateformat;where specify_dateformat can be dmy (day/month/year), mdy (month/day/year); explaining the command details is not part of this tutorial; with examples, you will understand how to use these functions.
Let’s take an example. First, change the language to ‘us_english’ using the query below.
SET LANGUAGE 'us_english';Change the date format to ‘myd’ using the query below.
SELECT ISDATE('11-23-2024') AS MonDayYear;
Using the above query, the first language and date format are changed to ‘us_english’ and ‘mdy’.
The expression ’11-23-2024′ based on the date format ‘mdy’ (month/date/year) passed to ISDATE() function, and the function returns the output as 1, which means the expression is a valid date value.
Now pass the date with a format other than ‘mdy’, for example, in the format ‘dmy’, as shown in the query below.
SELECT ISDATE('23-11-2024') AS DayMonYear;
So when you pass the date with a different format than the date format (mdy) you have set it to, the ISDATE() function returns the output as 0, which means the given expression is not a valid date.
Although the expression (’23-11-2024′) of format ‘dmy’ is valid, it is not due to the date format (mdy) we set it to.
But if you set the date format to ‘dmy’ using the query below, a not-valid expression becomes valid.
SET DATEFORMAT dmy;Now again, validate the expression ’23-11-2024′ using the query below.
SELECT ISDATE('23-11-2024') AS DayMonYear;
As you can see in the output, this time, it is the valid expression according to the ISDATE() function, and this is because of the date format.
Remember, different date formats affect the ISDATE function in SQL Server, so the ISDATE() function validates the given expression for date and time value based on the current date format.
Now, here is one exercise for you: change the language of the current session using the SET LANGUAGE and pass the different format of date to the ISDATE() function. See which date format returns the value as 0 or 1.
Keep in mind while specifying the language and check the date format for that language.
This is how the date format can affect the ISDATE function in SQL Server while validating the date and time value.
Using ISDATE Function in SQL Server on Table
From the above sections, you have learned how to use the ISDATE function in SQL Server on the expression value, but in the real world, you will often interact with a database where you will work on the tables.
So here, you will understand how to use the ISDATE function in SQL Serve on the table. Suppose you have a User_Info table, as shown below.
Consider the above table, which is from the website and contains the user’s basic details. Before processing, you must ensure that the date of birth values are valid in the DOB column.
Here, you can use the ISDATE() function, so to check the validity of the DOB column in the User_Info table, use the query below.
SELECT UserID, Name, DOB, ISDATE(DOB) AS ValidDob
FROM User_Info;
After executing the above query, the result set contains the ValidDob column, which contains the value 1 and 0, which the ISDATE() assigns function to each user based on their DOB.
So, the ValidDob column indicates whether or not a valid DOB exists. For example, James has a DOB of 1985-04-12, which is a valid DOB because look at the ValidDob column for this user, which contains the value 1.
Again, let me show you how to divide the user based on their valid or invalid DOB; execute the query below.
SELECT Name,DOB,ISDATE(DOB) AS ValidDob
FROM User_Info
GROUP BY Name,DOB,ISDATE(DOB)
ORDER BY ISDATE(DOB);
From the output, you can see in the User_Info table that half of the users have an invalid DOB, and half have a valid DOB.
Here, in the query part, the GROUP BY Name, DOB, ISDATE(DOB), ISDATE() function is used with the GROUP BY clause, so the user is separated based on the valid and invalid DOB.
So, you can also use the ISDATE() function with the GROUP BY clause to create a group.
This is how to use the ISDATE function in SQL Server on the table column to check the validity of the date and time value.
Conclusion
In this SQL Server tutorial, you covered how to validate the given expression, whether the date and time value or not, using the ISDATE() function in SQL Server.
After that, you have changed the current session language and the date format to see how the ISDATE() function responds or returns the result.
Finally, you have used the ISDATE() function on the table column to verify the user’s DOB.
You may like to read:
- SYSDATETIMEOFFSET Function in SQL Server
- How to use SYSDATETIME Function in SQL Server
- DATEDIFF Function in SQL Server
After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.