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

  - Microsoft SQL Server version 4.2 for OS/2
  - Microsoft SQL Server version 4.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: 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)

/* Creates a 4 byte integer under the datetype data type */

/* 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 */

  AS @day between 18800101 and 20991231

/* binds the rule to the datatype */

/* Below is the first version of the validation trigger */
/* This trigger checks to validate the date.       */

  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.   */

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

/* 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 */

  /* 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