CREATE TABLE #T(
id INT IDENTITY,
dt DATETIME NOT NULL DEFAULT GETDATE(),
sdt SMALLDATETIME NOT NULL DEFAULT GETDATE())
GO
INSERT #T VALUES(DEFAULT,DEFAULT)
INSERT #T VALUES('6/24/2001 7:05:01.544', '6/24/2001 7:05:01.544')
INSERT #T VALUES('6/24/2001', '6/24/2001')
INSERT #T VALUES('6/24/01', '6/24/01')
INSERT #T VALUES('6-24-2001','6-24-2001')
INSERT #T VALUES('20010624', '20010624') -- this is ISO/international format
INSERT #T VALUES('June 24, 2001', 'June 24, 2001')
INSERT #T VALUES('7:05:01.544', '7:05:01.544')
INSERT #T VALUES('7:05:01.544 PM', '7:05:01.544 PM')
GO
SELECT * FROM #T
id dt sdt
--- --------------------------- ---------------------------
1 2001-06-24 07:36:56.910 2001-06-24 07:37:00
2 2001-06-24 07:05:01.543 2001-06-24 07:05:00
3 2001-06-24 00:00:00.000 2001-06-24 00:00:00
4 2001-06-24 00:00:00.000 2001-06-24 00:00:00
5 2001-06-24 00:00:00.000 2001-06-24 00:00:00
6 2001-06-24 00:00:00.000 2001-06-24 00:00:00
7 2001-06-24 00:00:00.000 2001-06-24 00:00:00
8 1900-01-01 07:05:01.543 1900-01-01 07:05:00
9 1900-01-01 19:05:01.543 1900-01-01 19:05:00
