mo.notono.us

Wednesday, September 21, 2005

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:

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 Mounir BEN HAMED, 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 Blogger Oskar, at Monday, April 03, 2006 10:15:00 AM  

Post a Comment

Links to this post:

Create a Link

<< Home