ID Number: Q71441
1.10 1.11 4.20
OS/2
Summary:
Using the SQL Server DATETIME data type is one way to represent a date
in SQL Server. There are times, however, when it is necessary to store
the date without the time. In this case, it may be more efficient to
use an integer data type instead of the DATETIME data type, because
four of the eight bytes of storage used by DATETIME would be unused.
(Note: Version 4.2 does offer a 4 byte SMALLDATETIME; however, the
range of dates that can be represented is smaller and this option may
still be desired.)
The following example shows how to create a user-defined DATE datatype
as a 4 byte integer. The example consists of three steps: creating the
user-defined data type, creating a rule to validate the year, and
creating a trigger to validate the date. Note that there are two
different versions of the trigger.
More Information:
The date is stored in a YYYYMMDD format directly into an integer. To
insert the date in different formats, create either a stored procedure
to convert the format to the storage format or convert the format to
the storage format within the C application. Note that the rule
validates the year and the integer is long enough to represent the
needed YYYYMMDD format.
The first trigger checks to see if the date inserted/updated is valid
by trying to convert the integer representation of the date to a
datetime datatype. If the conversion fails, everything is rolled
back, and the user is prompted with the following message:
The conversion from CHAR to DATETIME resulted in a DATETIME value
out of range
(MSG 242, LEVEL 16, STATE 0)
The second trigger validates the month and date, and gives more
control over the error messages by using RAISEERROR or PRINT to send
messages to the error and message handlers, respectively. Note
that for further optimization, you can place much of this trigger
in a stored procedure, allowing table(s) with multiple instances of
DATE data types to make use of one block of code.
======================================================================
/* Creates a 4 byte integer under the datetype data type */
SP_ADDTYPE datetype, int, null
go
/* Rule checks to see if the date being inserted is between */
/* the dates Jan 1, 1880 and Dec 31, 2099. These dates can */
/* be tailored to your needs */
CREATE RULE datetype_rule
AS @day between 18800101 and 20991231
go
/* binds the rule to the datatype */
SP_BINDRULE datetype_rule, datetype
go
/* Below is the first version of the validation trigger */
/* This trigger checks to validate the date. */
CREATE TRIGGER date_trigger
ON date_table
FOR insert, update
AS
SET arithignor on
declare @date datetime
/* Retrieve date from inserted table trying to convert it to */
/* a datetime data type. If the conversion fails, the insert */
/* or update will be rolled back. */
select @date=convert (datetime, convert (char(12), date_field_name))
from inserted
SET arithignor off
======================================================================
/* Below is the second version of the validation trigger. */
/* This trigger checks to validate both months and days, */
/* including the simple rule to validate for leap years. */
CREATE TRIGGER date_trigger
ON date_table
FOR insert, update
AS
declare @day_part tinyint /* holds DD part of YYYYMMDD */
declare @mon_part tinyint /* holds MM part of YYYYMMDD */
declare @yr_part smallint /* holds YYYY part of YYYYMMDD */
declare @date int /* gets date field from inserted table */
/* Retrieve date from inserted table
select @date=date_field_name from inserted
/* As stated earlier, you can place this portion in a stored procedure */
/* so that table(s) can make use of multiple DATE fields */
/* Check allows for NULL dates */
if @date != NULL
begin
/* retrieves month part out of YYYYMMDD format */
select @mon_part = ((@date%10000)/100)
/* validates month */
if @mon_part between 1 and 12
begin
/* retrieves day part out of YYYYMMDD format */
select @day_part = (@date % 100)
/* Validates days of 1..31 for months of 31 days */
if @mon_part in (1,3,5,7,8,10,12)
and @day_part not between 1 and 31
begin
print "invalid day in date, rolling back transaction"
rollback transaction
end
/* Validates days of 1..30 for months of 30 days */
else if @mon_part in (4,6,9,11) and @day_part not between 1 and 30
begin
print "invalid day in date, rolling back transaction"
rollback transaction
end
/* This portion checks to see if the Feb. date satisfies */
/* the Leap Year Rule. A year is a leap year if the year is */
/* divisible by four. However, if the year ends in 00, then it */
/* it is only a leap year if the year ending in 00 is divisible */
/* by 400. */
else if @mon_part = 2 and @day_part not between 1 and 28
begin
if @day_part = 29
begin
select @yr_part = @date/10000
if @yr_part % 4 = 0 begin
if @yr_part % 100 = 0 and @yr_part % 400 != 0
begin
print "invalid day, violates leap year rules, rolling back
transaction"
rollback transaction
end
end
else begin
print "invalid day, violates leap year rules, rolling back
transaction"
rollback transaction
end
end
else begin
print "invalid day in date, rolling back transaction"
rollback transaction
end
end
else begin
print "invalid day or month in date, rolling back transaction"
rollback transaction
end
end
else
begin
print "invalid month in date, rolling back transaction"
rollback transaction
end
end /* if not NULL */
Additional reference words: Transact-SQL triggers