SQL: ISDATE(@foo) and CAST(@foo AS smalldatetime)

SQL is a squirrely language when it comes to string manipulation, but it has some nice functions. ISNUMERIC and ISDATE are two of them.

ISDATE does not work exactly as advertised in SQL Books Online, however. One feature not mentioned there is that any 4 digit string from '1753' to '9999' will be evaluated as a date (ISDATE interprets the number as a year, and valid years for datetime are 1753-9999). Unfortunately, CAST('1753' AS smalldatetime) will NOT work, as smalldatetime allows years only from 1900 through 2079.

In order to verify that a string input is indeed a 'small' date, I'd recommend the following function:

--Checks if a string is a valid smalldatetime
CREATE FUNCTION dbo.IsSmallDate
(
 @SmallDateString varchar(20) --The input string to check
)
RETURNS BIT
AS
BEGIN
 DECLARE @Result bit
 IF ISNUMERIC(@SmallDateString) = 1
 BEGIN
  IF CAST(@SmallDateString AS int) BETWEEN 1900 AND 2079 
   SET @Result = 1
  ELSE
   SET @Result = 0
 END
 ELSE 
  SET @Result = ISDATE(@SmallDateString)

 RETURN @Result
END