Converting datetime and smalldatetime Data

When converting to datetime, Microsoft® SQL Server™ rejects all values it cannot recognize as dates (including dates earlier than January 1, 1753). You can convert datetime values to smalldatetime when the date is in the proper range (January 1, 1900 to June 6, 2079). The time value is rounded up to the nearest minute.

This example converts smalldatetime and datetime values to varchar and binary data types, respectively.

DECLARE @mydate_sm  SMALLDATETIME

SET  @mydate_sm  = '4/05/98'

SELECT  CAST(@mydate_sm AS VARCHAR) AS SM_DATE_VARCHAR

GO

DECLARE @mydate  DATETIME

SET @mydate     = '4/05/98'

SELECT  CAST(@mydate AS BINARY) AS DATE_BINARY

GO

Here is the result set:

(1 row(s) affected)

SM_DATE_VARCHAR               

------------------------------

Apr  5 1998 12:00AM           

(1 row(s) affected)

(1 row(s) affected)

DATE_BINARY                                                   

--------------------------------------------------------------

0x0000000000000000000000000000000000000000000000008c3000000000

(1 row(s) affected)

See Also