I think that one of the most interesting and confusing data types in SQL Server is datetime. I thought that I knew almost everything about the datetime data type, and I've even written some articles about usage of this data type, but lately I have had an interesting problem which showed me that there is more to datetime data type than I thought. In this article, I shed light on one very confusing issue.

One of my SQL Server jobs which runs multiple times per day started failing with this message:

The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.”

It was strange because function datediff compared the current datetime variable assigned with the getdate() function to the session start time and produced a result in seconds. According to Microsoft BOL, this function returns an integer, which in SQL Server is between (-2,147,483,648) and (2,147,483,647).

Here are the syntax and short explanation of this function.

DATEDIFF ( datepart , startdate , enddate )

datepart

Is the part of startdate and enddate that specifies the type of boundary crossed. The following table lists all valid datepart arguments. User-defined variable equivalents are not valid.

startdate

An expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value. date can be an expression, column expression, user-defined variable or string literal. startdate is subtracted from enddate.

To avoid ambiguity, use four-digit years. For information about two digits years, see . Configure the two digit year cutoff Server Configuration Option.

enddate

See startdate.

As you can calculate the date part in seconds with the function datediff, there is a maximum difference at about 68 years apart between dates. In my case, such a difference is not possible. By reading BOL further, I discovered the next sentence: “If only a time value is assigned to a variable of a date data type, the value of the missing date part is set to the default value: 1900-01-01”.

Here you go. You have more than 68 years between 1/1/1900 and now!!! The system does not throw an error when only the time part is assigned to the variable. I decided to do some experiments with this behavior. First, I tried to check if variable will get validated as datetime if it only has a time.

declare @last_request_start_time smalldatetime = '10:00:00';
select ISdate(@last_request_start_time);

Function IsDate validates this as a proper date. Then, I tried to insert time value into a table column with smalldatetime or datetime data type. SQL Server automatically converts time to a date as you can see below.

declare @tbldate table (tid int primary key, datetimevar smalldatetime);
insert into @tbldate  (tid,datetimevar) 
   values (1,'10:00'), (2,'1/1/2017');
select tid, datetimevar from @tbldate;

You can see the results in the image below.

Now, if you are going to select the value and use it for something in your application, you will gett the result, but this result is wrong based on the fact that 1/1/1900 is not the date you expect to see and use as the outcome. This is a little bit scary and creates potential data issues.

For example, if, later, I will be using this value to calculate the difference in months between now and stored date, I will have the result back.

  select  tid, datediff(mm,datetimevar, getdate()) diffInMonths from @tbldate;

But this result is wrong because the real date is unknown. Likely enough, we are using 1/1/1900 as default in many cases, which really represents a NULL (unknown) value. We also have cases across many company databases where 1/1/1900 is a legitimate date. For example, the year the company opened: there are some old companies that were opened on 1/1/1900. Or data about some historical events which happened at New Year of 1900.

In my specific situation, I was able to overcome such a scenario with a case statement because all I need to know is if the difference between requested start date and now is greater than X amount of seconds, so it was fine to treat the field value with time with greater difference than value X if date is really unknown.

  Declare @lastbatchsecondsback smallint;
  Select * from Table1 where 
  datediff(ss,
              case 
                when cast(last_request_start_time as date) = '1/1/1900' THEN '1/1/2017' 
                ELSE last_request_start_time 
              END, getdate()) > @lastbatchsecondsback;

Are there any way to verify what value was provided in the first place – datetime or only time? What if it is absolutely necessary to know the real date criteria based on business requirements? 

I can see only one way to do this: get a value as a string and check it before you store it to the datetime column/variable with something like this:

create function dbo.udfTimeOnly ( @datetimevar  varchar(25) )
  returns  bit
as
begin
  declare @bitvar bit;
  IF len  (ltrim(@datetimevar)) <= 8 and charindex(':',@datetimevar) > 0
      set @bitvar =  1;
  ELSE
      set @bitvar =  0;
                           
 return @bitvar;            
end;

Let’s see the result:

declare @var varchar(20) = '10:00:00';
declare @var1 varchar(20) = getdate();
declare @tbldate table (tid int primary key, datetimevar smalldatetime);
insert into @tbldate  (tid,datetimevar) 
  select 1, CASE 
              WHEN dbo.udfTimeOnly(@var) = 1 THEN NULL
              ELSE @var
            END;
  insert into @tbldate  (tid,datetimevar) 
  select 2, CASE 
              WHEN dbo.udfTimeOnly(@var1) = 1 THEN NULL
              ELSE @var1
            END;
  select tid, datetimevar from @tbldate;

See the image below.

As we can see, we do have the desired outcome. Needless to say that if number of records is huge, this additional check may present some performance issues.

Conclusion

The problem described above is very uncommon. But it could happen if you received data from a third party or your application has a bug. In both cases, it is better to fix the bug instead of creating a checking mechanism.

Unfortunately, in our situation, we were unable to force the third party company to fix the bug and we were stuck with the option to use the checking mechanism.