SQL: Updated IsSmallDate function
Due to a number of unexpected features of the ISNUMERIC function in SQL, I rewrote my IsSmallDate function to not use the function:
--Checks if a string is a valid smalldatetime ALTER FUNCTION dbo.IsSmallDate ( @SmallDateString varchar(20) --The input string to check ) RETURNS BIT AS BEGIN DECLARE @Result bit SET @SmallDateString = LTRIM(RTRIM(@SmallDateString)) --Check for numbers only, with a specific pattern IF @SmallDateString LIKE '[12][90][0-9][0-9]' 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
Labels: sql
2 Comments:
this script will not work correct! because, it execute correctly this datetime (exp. 01.01.1888). but, this exemple is not the smalldatetime !!
i want correct them, i modified this script on follow :
--Checks if a string is a valid smalldatetime
ALTER FUNCTION dbo.IsSmallDate
(
@SmallDateString varchar(20) --The input string to check
)
RETURNS BIT
AS
BEGIN
DECLARE @Result bit
SET @SmallDateString = LTRIM(RTRIM(@SmallDateString))
if (isdate(@SmallDateString)=1 and (year(convert(datetime, @SmallDateString)) between 1900 and 2079))
SET @Result = 1
ELSE
SET @Result = 0
END
RETURN @Result
END
By Anonymous, at Monday, April 03, 2006 4:05:00 AM
Mounir, thanks.
My original code was quite brain-dead - after my struggles with ISNUMERIC I had been fixating on dates that contained year-info only.
The corrected function can be found here and also on Code Snippets.
Thanks again for correcting my bug.
By Oskar Austegard, at Monday, April 03, 2006 10:15:00 AM
Post a Comment
<< Home