Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Trouble With ISDATE And Converting To SMALLDATETIME

Date and time Tips and tricks

Trouble With ISDATE And Converting To SMALLDATETIME

by  SQLDenis  Posted    (Edited  )
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

Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top