In this post:

http://weblogs.sqlteam.com/jeffs/archive/2004/12/02/2954.aspx

I talked about separating dates from times for certain columns in your database.  One thing I have never really used before in SQL Server 2000 is user-defined datatypes and rules.  Rules are like CHECK constraints, but from what I understand they are very non-standard.  User Defined Datatypes are cool, but they require you to use rules if you'd like them to include validation, so in general they aren't very powerful.  I think all of this is addressed in SQL Server 2005.  (I'm sure someone will help me out with this...)

Note: As Adam points out in the comments section, Rules are being phased out in SQL 2005. I don't know if a mechanism will replace them, so this type of thing may not be possible in future versions of T-SQL.

Anyway, if you like, you can use User Defined Types and Rules together to create two new datatypes: udt_Date and udt_Time.  Both are based on the usual DateTime datatype, but both implement rules that ensure they hold the proper data, as discussed in the previous article.  Using this approach, you won't have to worry about implementing CHECK constraints in all of your tables since the UDT does that for you.

Here is a sample script for you to enjoy:

CREATE RULE DateOnly

AS

dateAdd(dd,datediff(dd,0,@DateTime),0) = @DateTime

go

CREATE RULE TimeOnly

AS

datediff(dd,0,@DateTime) = 0

GO

exec sp_addtype udt_Date, 'DateTime'

exec sp_addType udt_Time, 'DateTime'

go

exec sp_bindrule 'DateOnly', 'udt_Date'

exec sp_bindrule 'TimeOnly', 'udt_Time'

go

-- So, now we can just create a table and use our User Defined Type

-- and not worry about using CHECK constraints everywhere:

create table Test (ID int primary key,

      TransDate udt_Date,

      TransTime udt_Time)

go

-- this will succeed:

insert into Test (ID, TransDate, TransTime)

VALUES (1,'1/1/2004','10:23 AM')

-- this will fail -- there is a time attached to the date:

insert into Test (ID, TransDate, TransTime)

VALUES (2,'1/1/2004 4:34 AM','10:23 AM')

-- this will fail -- there is a non-zero date attached to the time:

insert into Test (ID, TransDate, TransTime)

VALUES (3,'1/2/2004','12/31/2003 10:23 AM')

go

select * from Test

go

-- clean it up:

drop table Test

exec sp_unbindrule 'udt_Date'

exec sp_unbindrule 'udt_Time'

drop rule DateOnly

drop rule TimeOnly

exec sp_droptype 'udt_Date'

exec sp_droptype 'udt_Time'

Update: If you are using SQL Server 2005, see this.

see also:

• Working with Time Spans and Durations in SQL Server
• Group by Month (and other time periods)
• Working with Date and/or Time values in SQL Server: Don't Format, Don't Convert -- just use DATETIME
• Data Types -- The Easiest Part of Database Design
• How to format a Date or DateTime in SQL Server
• Breaking apart the DateTime datatype -- Separating Dates from Times in your Tables
• Date Only and Time Only data types in SQL Server 2005 (without the CLR)
• Essential SQL Server Date, Time and DateTime Functions