Click here to monitor SSC

SQL Server Development and Data Security

Moving Dates

Published 24 April 2011 12:32 am

At the loading dock, hard working individuals load a semi trailer with your new television, stereo system and personal computer. There is a dependency in this process: it is that your favorite electronics store has a loading dock that will be able to accommodate the size of the semi. If the semi were to arrive to the store and the location of receiving deliveries is only the size of a one-car garage it may either result in a refused delivery or a lot of manual work to perform the task of unloading the semi.

This experience is a shared one between semi drivers and database administrators. We pack up data from one database and deliver it to another. Not always does the proverbial loading dock meet the needs of the data that is being delivered. Consider the datetime data type for an example.

The datetime data type is one in which we are all familiar. It has been part of the data type options for quite sometime. The range of dates that can be stored in this data type is from January 1, 1753 through December 31, 9999. The release of SQL Server 2008 a collection of date data types were introduced: date, datetime2,  and datetimeoffset; all of which provide an accepted date range from January 1, 0001 through December 31, 9999. The expansion of date range for these new date data types provides compliance with ANSI 92 SQL Standard for the Gregorian calendar. This also presents a challenge when shipping data from a SQL Server 2008 database, which is using the date data type, to a SQL Server 2005 database, which is using the datetime data type.

This situation can be illustrated through the execution of the following code:

DECLARE @Date DATE;          
DECLARE @Datetime DATETIME;

SET @Date = ’10/24/1492′;
SET @Datetime = @Date

The resulting error that occurs is:

The conversion of date data type to a datetime type resulted in an out-of-range value.

The quick and easy reply might be to upgrade the SQL Server 2005 database to SQL Server 2008 and change the data type of the column in question. While this is certainly a valid option, not always is making such changes to the receiving database within our realm of control or the desire of the business.

Data type modifications, such as varchar or numeric could provide a means of resolving this issue, but this conversion does loose some date characteristics that are valuable, such as ordering of dates and the use of date methods like DATEDIFF.

In essence, the resolution of this issue resides in an application of some minor business logic and compromise with the data that is being sent. It could be determined that since the receiving database does not allow October 24, 1492 that it has no functional use for dates preceding January 1, 1753. Therefore, these rows could be filtered out of the data that is being delivered. Another option could be to return NULL or another date in the date column to substitute out of range dates; thus, delivering the row but with a compatible, although less than accurate, data value. An illustration of such a statement would be:

DECLARE @Date DATE;          
DECLARE @Datetime DATETIME;

SET @Date = ’10/24/1492′;
SET @Datetime = (CASE WHEN @Date < ’1753-01-01′ THEN ’1753-01-01′ ELSE @Date END);

SELECT @Datetime

The latter solution noted above certainly does not settle well for those of us who are concerned with data integrity. It certainly should not be the option executed as your default response, nor should it be implemented without the exclusion of the options noted prior. However, it does offer a solution that could be mutually acceptable depending on the data’s use. The key factor here is to work closely with those who are requesting the delivery of data. Before packing up your semi gain an understanding of the dock to which you are delivering. The time taken here could save your driver a load of sweat.

2 Responses to “Moving Dates”

  1. Jack the DBA says:

    John,

    smalldatetime is not from January 1, 0001, but from January 1, 1900. I don’t know if that was a typo or what.

  2. Johnm says:

    @Jack the DBA: Thanks for the eagle-eye catch on that one. I could say it was a test to see if anyone really reads my blog, but the truth is that I mistyped it. I have corrected it in the posting.

Leave a Reply