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

ID: Q71441

The information in this article applies to:

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 "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 query words: 4.20 dblib 


Keywords          : kbprg SSrvDB_Lib SSrvGen SSrvTran_SQL 
Version           : 4.2
Platform          : OS/2 WINDOWS 
Issue type        :

Last Reviewed: March 11, 1999