Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 157, comments - 2686, trackbacks - 64

My Links

Advertisement

News

Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.


Subscribe





Archives

Post Categories

Programming

Sports

SQL

Date Only and Time Only User Defined Dataypes in SQL Server 2000

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:

Print | posted on Thursday, December 02, 2004 2:41 PM | Filed Under [ T-SQL Code Library - SQL DateTime Data ]

Feedback

Gravatar

# re: Date Only and Time Only User Defined Dataypes in SQL Server 2000

Jeff,

I would recommend that you not use these at all. They're deprecated in 2005.

Write to sqlwish@microsoft.com and ask for support for DOMAINS! That's the ANSI Standard (and much, much more powerful) way to implement this functionality.
12/3/2004 10:58 AM | Adam Machanic
Gravatar

# re: Date Only and Time Only User Defined Dataypes in SQL Server 2000

maybe I will re-word the intro ... i kind of hinted that RULES are non-standard and probably going away in SQL 2005, but didn't really state it.
12/3/2004 11:59 AM | Jeff S
Gravatar

# re: Towards a Point Array constraint

2/7/2005 6:05 PM | DavidM
Gravatar

# re: Date Only and Time Only User Defined Dataypes in SQL Server 2000

Now now children
8/23/2007 10:43 AM | the Boss
Gravatar

# re: Date Only and Time Only User Defined Dataypes in SQL Server 2000

very bad
10/31/2007 8:47 AM | beso
Gravatar

# re: Date Only and Time Only User Defined Dataypes in SQL Server 2000

very very bad
10/14/2008 10:33 AM | raj
Gravatar

# Display Time Only

How do I write a query which display ONLY the time in this format (14:28:10) if it
is stored as 12/5/2009 14:29:10 in the database? (SQL Server 2000)
5/12/2009 4:19 AM | ZeroGhost
Gravatar

# re: Date Only and Time Only User Defined Dataypes in SQL Server 2000

hello,

its very useful to me to create user define datatype. after using your code the format of udt_Date is mm/dd/yyyy. i wantto convert it in dd/mm/yyyy. how can ido?????
please help.

thanks.
10/12/2009 3:45 AM | bhavna
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET