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 do this
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

CODE

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 because 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

CODE

ALTER 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

CODE

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

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

CODE

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

CODE

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

return only data that can not converted to smalldatetime

CODE

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

Revision history
Initial version created on 2006-09-29 10:55:50.620

Back to Microsoft SQL Server: Programming FAQ Index
Back to Microsoft SQL Server: Programming Forum
My FAQ Archive

Email This FAQ To A Friend

My Archive

My FAQ Archive