Listing 1: Columns that Demonstrate Three Datetime Data Types

Alter Procedure insert_six_datetime_values
As

--Drop table_of_dates if it already exists
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('table_of_dates')
	AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE table_of_dates

--Create table_of_dates with datetime and smalldatetime values
CREATE TABLE table_of_dates
(
	myidentity int IDENTITY,
	mydatetime datetime DEFAULT getdate(),
	mysmalldatetime smalldatetime DEFAULT getdate()
)

--Insert 6 sets of default values into the table with
--a gradually increasing delay between inserts
INSERT table_of_dates DEFAULT VALUES
WAITFOR DELAY '00:00:00.050'
INSERT table_of_dates DEFAULT VALUES
WAITFOR DELAY '00:00:00.50'
INSERT table_of_dates DEFAULT VALUES
WAITFOR DELAY '00:00:01.000'
INSERT table_of_dates DEFAULT VALUES
WAITFOR DELAY '00:00:01.500'
INSERT table_of_dates DEFAULT VALUES
WAITFOR DELAY '00:00:02.000'
INSERT table_of_dates DEFAULT VALUES