Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot christmas Logo

Community Wiki

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Navigation

Google Ads

Trouble With ISDATE And Converting To SMALLDATETIME

From Wiki

Jump to: navigation, search

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

  1. 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

  1. SELECT  CONVERT(SMALLDATETIME,'2079-06-06 23:59:29')
  2.     SELECT  CONVERT(SMALLDATETIME,'2079-06-06 23:59:29.998')
  3.     SELECT  CONVERT(SMALLDATETIME,'2079-06-06 23:59:29.999')
  4.     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

  1. ALTER FUNCTION fnIsSmallDateTime(@d VARCHAR(50))
  2.     RETURNS BIT
  3.     AS
  4.     BEGIN
  5.     DECLARE @bitReturnValue bit
  6.  
  7.  
  8.     SELECT @bitReturnValue =CASE
  9.              WHEN ISDATE(@d) = 1 THEN CASE
  10.                                         WHEN convert(datetime,@d) > ='19000101'
  11.                          AND convert(datetime,@d) <= '20790606 23:59:29.998' THEN 1
  12.                                         ELSE 0
  13.                                       END
  14.              ELSE 0
  15.            END
  16.     RETURN @bitReturnValue
  17.     END
  18.     GO


Let's create a test table with values:

  1. CREATE TABLE TestSmallDate (SomeDate VARCHAR(40))
  2.     INSERT TestSmallDate VALUES ('19000101')
  3.     INSERT TestSmallDate VALUES ('18991231')
  4.     INSERT TestSmallDate VALUES ('19010101')
  5.     INSERT TestSmallDate VALUES ('20790607')
  6.     INSERT TestSmallDate VALUES ('2079-06-06 23:59:29.677')
  7.     INSERT TestSmallDate VALUES ('2079-06-06 23:59:29.998')
  8.     INSERT TestSmallDate VALUES ('2079-06-06 23:59:29.999')
  9.     INSERT TestSmallDate VALUES ('2079-06-06 23:59:59.000')
  10.     INSERT TestSmallDate VALUES ('2079-06-06 01:00:00')
  11.     INSERT TestSmallDate VALUES ('2079-06-06 00:00:00')
  12.     INSERT TestSmallDate VALUES ('2079-06-06 00:00:01')
  13.     INSERT TestSmallDate VALUES ('WhoIsYourDaddy')


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

  1. SELECT dbo.fnIsSmallDateTime(SomeDate),
  2.     CASE  dbo.fnIsSmallDateTime(SomeDate)
  3.     WHEN  1 THEN CONVERT(SMALLDATETIME,SomeDate) END AS ConvertedToSmallDate,
  4.         SomeDate
  5.     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:

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


Return only data that can be converted to smalldatetime:

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


Return only data that can not converted to smalldatetime:

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


Contributed By: --SQLDenis 17:08, 30 May 2008 (GMT)

Part of SQL Server Programming Hacks

Section Dates

84 Rating: 2.6/5 (45 votes cast)