DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Snippets has posted 5883 posts at DZone. View Full User Profile

IsSmallDate Function - Corrected

01.18.2006
| 4066 views |
  • submit to reddit
        Courtesy of Mounir BEN HAMED, the following is <a href="http://austegard.blogspot.com/2006/04/sql-corrected-issmalldate-function.html">the corrected function</a>.
Background Info: See <a href="http://austegard.blogspot.com/2005/04/sql-isdatefoo-and-castfoo-as.html"> SQL: ISDATE(@foo) and CAST(@foo AS smalldatetime)</a> and the incorrect <a href="http://austegard.blogspot.com/2005/09/sql-updated-issmalldate-function.html">Updated IsSmallDate function</a>

Oskar Austegard
<a href="http://mo.notono.us">http://mo.notono.us</a>

--Checks if a string is a valid smalldatetime
--Updated 04/03/06 by Oskar Austegard after bug find by Mounir Ben Hamed
CREATE 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 CONVERT(datetime, @SmallDateString) BETWEEN '1900-01-01' AND '2079-06-06'
    SET @Result = 1
  ELSE
    SET @Result = 0

  RETURN @Result
END