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: