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
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
posted by Oskar Austegard at 2:16 PM on Sep 21, 2005
"SQL: Updated IsSmallDate function"
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
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.
Monday, April 03, 2006 10:15:00 AM