INFO: Use User-Defined Data Type to Extract Current Time (67410)


The information in this article applies to:

  • Microsoft SQL Server 4.2x
  • Microsoft SQL Server 6.0
  • Microsoft SQL Server 6.5
  • Microsoft SQL Server 7.0
  • Microsoft SQL Server 2000 (all editions)
This article was previously published under Q67410

SUMMARY

You can create a user-defined data type that extracts the current time. The advantage of using user-defined data types is that you can bind rules and defaults to them for use in several different tables.

MORE INFORMATION

The following is an example of creating a data type with the functionality of extracting the current time by first creating a default, and then binding it to the user data type: 
/* First, create the user type "time". Please note that it is defined
   as not null. */ 

sp_addtype time, "char(8)", "not null"
go

/* Next, create the default "timedft". This default uses the string
   function "right" to convert it to type char. */ 

create default timedft
as right (getdate(),8)
go

/* Now, bind the result to the user data type. */ 

sp_bindefault timedft, time

/* Create the table with this user data type. */ 

create table test
(curtime time,
 test int)

/* Since the data type was created as not null, any time a value is not
   supplied for the curtime column, the current time will be input into
   the column. */ 

insert test (test)
values (1)

go

select * from test
go
				

Curtime  test
-------  --------------
4:24 PM    1
Modification Type:MinorLast Reviewed:2/14/2005
Keywords:kbinfo kbusage KB67410
©2004 Microsoft Corporation. All rights reserved.