Post a Comment On: mo.notono.us

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