Trouble With ISDATE And Converting To SMALLDATETIME

If you want to use the ISDATE function to convert a value to a SMALLDATETIME you also have to take into consideration that SMALLDATETIME stores date and time data from January 1, 1900, through June 6, 2079 but DATETIME stores date and time data from January 1, 1753 through December 31, 9999
So even though the ISDATE function returns 1 for the date 1890-01-01 this can not be converted to SMALLDATETIME and you will receive an error message after you run the following statement

SELECT CONVERT(SMALLDATETIME,'18900101')

Server: Msg 296, Level 16, State 3, Line 1
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.


Also be careful with rounding
Run these four statements
SELECT CONVERT(SMALLDATETIME,'2079-06-06 23:59:29')
SELECT CONVERT(SMALLDATETIME,'2079-06-06 23:59:29.998')
SELECT CONVERT(SMALLDATETIME,'2079-06-06 23:59:29.999')
SELECT CONVERT(SMALLDATETIME,'2079-06-06 23:59:30')


The first two are fine , the second two blow up because the value gets rounded up to the next day after it gets rounded up to the next minute (and hour)

I decided to roll out my own fnIsSmallDateTime() function because who wants to write the same CASE ISDATE when Value between this and that code all over the place?

Here is the code for the user defined function


CREATE FUNCTION fnIsSmallDateTime(@d VARCHAR(50))
RETURNS BIT
AS
BEGIN
DECLARE @bitReturnValue BIT


SELECT @bitReturnValue =CASE
WHEN ISDATE(@d) = 1 THEN CASE
WHEN CONVERT(DATETIME,@d) > ='19000101'
AND CONVERT(DATETIME,@d) <= '20790606 23:59:29.998' THEN 1
ELSE 0
END
ELSE 0
END
RETURN @bitReturnValue
END
GO


Let's create a test table with values
CREATE TABLE TestSmallDate (SomeDate VARCHAR(40))
INSERT TestSmallDate VALUES ('19000101')
INSERT TestSmallDate VALUES ('18991231')
INSERT TestSmallDate VALUES ('19010101')
INSERT TestSmallDate VALUES('20790607')
INSERT TestSmallDate VALUES ('2079-06-06 23:59:29.677')
INSERT TestSmallDate VALUES ('2079-06-06 23:59:29.998')
INSERT TestSmallDate VALUES ('2079-06-06 23:59:29.999')
INSERT TestSmallDate VALUES ('2079-06-06 23:59:59.000')
INSERT TestSmallDate VALUES('2079-06-06 01:00:00')
INSERT TestSmallDate VALUES ('2079-06-06 00:00:00')
INSERT TestSmallDate VALUES ('2079-06-06 00:00:01')
INSERT TestSmallDate VALUES('WhoIsYourDaddy')

If you want NULL for values that can not be converted to SMALLDATETIME use this code

SELECT dbo.fnIsSmallDateTime(SomeDate),
CASE dbo.fnIsSmallDateTime(SomeDate)
WHEN 1 THEN CONVERT(SMALLDATETIME,SomeDate) END AS ConvertedToSmallDate,
SomeDate
FROM TestSmallDate

if you want to convert the values that can not be converted to SMALLDATETIME to '1901-01-01 00:00:00' use the code below

SELECT dbo.fnIsSmallDateTime(SomeDate),
CASE dbo.fnIsSmallDateTime(SomeDate)
WHEN 1 THEN CONVERT(SMALLDATETIME,SomeDate)
ELSE CONVERT(SMALLDATETIME,'19000101') END AS ConvertedToSmallDate,
SomeDate
FROM TestSmallDate


Return only data that can be converted to SMALLDATETIME

SELECT * FROM TestSmallDate
WHERE dbo.fnIsSmallDateTime(SomeDate) =1



Return only data that can not converted to SMALLDATETIME

SELECT * FROM TestSmallDate
WHERE dbo.fnIsSmallDateTime(SomeDate) =0