INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Date and time Tips and tricks

Trouble With ISDATE And Converting To SMALLDATETIME by SQLDenis
Posted: 29 Sep 06 (Edited 29 Sep 06)

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 Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close