Microsoft KB Archive/71441

= INF: Creating a Four-Byte User-Defined DATE Data Type =

Article ID: 71441

Article Last Modified on 2/14/2005

-

APPLIES TO


 * Microsoft SQL Server 4.21a Standard Edition

-



This article was previously published under Q71441



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: Current versions of Microsoft SQL Server do offer 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 &quot;invalid day in date, rolling back transaction&quot; 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 &quot;invalid day in date, rolling back transaction&quot; 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 &quot;invalid day, violates leap year rules, rolling back transaction&quot; rollback transaction end end else begin print &quot;invalid day, violates leap year rules, rolling back transaction&quot; rollback transaction end end else begin print &quot;invalid day in date, rolling back transaction&quot; rollback transaction end end else begin print &quot;invalid day or month in date, rolling back transaction&quot; rollback transaction end

end else begin print &quot;invalid month in date, rolling back transaction&quot; rollback transaction end

end /* if not NULL */

Additional query words: 4.20 dblib

Keywords: kbprogramming KB71441

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.