Converting datetime and smalldatetime Data
When converting to datetime, Microsoft® SQL Server™ 2000 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 (from January 1, 1900 through June 6, 2079). The time value is rounded 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)
DATE_BINARY
--------------------------------------------------------------
0x0000000000000000000000000000000000000000000000008c3000000000
(1 row(s) affected)