Data Warehousing Snippets – a slightly different ISDATE() function

The T-SQL function ISDATE() is great for determining if a string is a valid date or not. I imagine you’ve used it many times if you’ve been writing T-SQL for a long time. However, there is something that can make it act quite unexpected… the language settings!

Eh? The language settings? What has that to do with dates???

Well, it has to do with the various formats that constitute a valid date in different parts of the world. For example, in the U.S.A. they often refer to dates by month first followed by day like April 1st. In the U.K. we’re the other way around, so you’d likely see 1st April. Taking this back to T-SQL we have the following date ‘2019-25-01’, this is a perfectly valid date. However, we also could have ‘2019-01-25’ which is also a perfectly valid date. The problem is when using the ISDATE() function it takes into account the language. Try this T-SQL below and you’ll see which dates are valid or not. Note that we’re using the SET LANGUAGE to explicitly set the language (duh!) for the session:-

DECLARE @invalidDateInUK VARCHAR(10) = '2019-25-01';
DECLARE @validDateInUK VARCHAR(10) = '2019-12-01';  
  
SET LANGUAGE italian;  
SELECT 
ISDATE(@invalidDateInUK)
,ISDATE(@validDateInUK)

SET LANGUAGE english;  
SELECT 
ISDATE(@invalidDateInUK)
,ISDATE(@validDateInUK)

Hopefully now you can see what might happen if you don’t know (or don’t set) the language? Say for example you ran this code below, you will see different results for the ISDATE() function depending on your language setting.

DECLARE @invalidDateInUK VARCHAR(10) = '2019-25-12'; 

SELECT 
ISDATE(@invalidDateInUK)

This of course makes the use of ISDATE() potentially risky if you aren’t explicit in the language settings. Is there any way we can improve or fix this? Well yes, there’s several options. The simplest being just set the language explicitly I hear you cry! Yes, in most cases this should suffice and you should only write the least amount of code you can to solve the problem. Unfortunately this isn’t possible in some scenarios, for example you cannot set the language in a view. We can get around this by using a function. So how can we improve the function itself so language doesn’t matter? Let’s try this:-

CREATE FUNCTION [Chronological].[IsValidDate] (
	@year INT
	,@month INT
	,@day INT
)
RETURNS BIT AS
BEGIN
	-- this function checks for a specific year, month and day to see
	-- if its a valid date

	-- first get the year as a varchar
	DECLARE @yearStr VARCHAR(4) = CAST(@year AS VARCHAR(4))

	-- generate a 2 character leading zero month
	DECLARE @monthStr VARCHAR(2) = CASE
		WHEN @month < 10 THEN '0' + CAST(@month AS VARCHAR(1))
		ELSE CAST(@month AS VARCHAR(2))
	END

	-- generate a 2 character leading zero day
	DECLARE @dayStr VARCHAR(2) = CASE
		WHEN @day < 10 THEN '0' + CAST(@day AS VARCHAR(1))
		ELSE CAST(@day AS VARCHAR(2))
	END

	-- now we can use the standard ISDATE() function
	-- note we are using the YYYYMMDD format of the ISO 8601 
	-- standard here as this should prevent problems with 
	-- dates being interpreted differently depending on the
	-- culture or language settings
	RETURN ISDATE(@yearStr + @monthStr + @dayStr)
END

Pretty simple and straightforward, the important part really is the last bit. We can use one of the ISO 8601 formats with the original ISDATE() function to achieve our goal. The preceding code is really only there just to generate the string in that specific format. If we explicitly ask for the year, month and day as simple integer parameters then we’re sorted 😉

Hope you like this one, it’s not something that you should use if there’s a simpler solution to the problem. However, if you find yourself stuck needing ISDATE() in a view then this, or something like it may be the solution.