en-UShe-IL
You are here:   Blog
Register   |  Login

Blog Archive:

Maximize
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

Maximize
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

Maximize
* Can be used in order to search for blogs by keywords

TNWikiSummit


Awared MVP

 


Microsoft® Community Contributor 


Microsoft® Community Contributor


 Read first, before you you use the blog! Maximize

Recent Entries

Minimize
פבר29

Written by: ronen ariely
29/02/2016 21:57 RssIcon

Background - February 29

Most people heard about the 2000 year bug, which occur once in 1000 years, but less people familiar with much more common bugs related to February 29, which occur approximately every 4 years. Let's named these as the "February 29 bug" or the "Leap year bug".

February 29, also known as leap day in the Gregorian calendar, is a date that occurs in most years that are divisible by 4, such as 2008, 2012, 2016, 2020, and 2024. Years that are divisible by 100, but not by 400, do not contain a leap day. Thus, 1700, 1800, and 1900 did not contain a leap day, 2100, 2200, and 2300 will not contain a leap day, while 1600 and 2000 did, and 2400 will. Years containing a leap day are called leap years. February 29 is the 60th day of the Gregorian calendar in such a year, with 306 days remaining until the end of the year. (From the Wikipedia)

The Issue

This blog based on a message posted at MSDN forum, by Yaniv Cohen, who notice that the use of the function ISNULL returns unexpected result, regarding February 29. Is this really unexpected result and did we found a BUG?!? Starting with the original issue, as a starting point, I explain how the relevant functions behaves behind the scenes, and the difference between them. I reproduces the "unexpected behavior" using simpler case, and point to the source of the "unexpected behavior". I will present demos that you can execute in order to figure out how the relevant functions works, and in particular gives you the tools to understand the extent of the problem.

* This blog published on February 29 2016, which is best time to start discuss some of the weird behaviors (or BUGs ?!?) people experience regarding February 29 in SQL Server, from Transect-SQL to the SQL Engine behaviors and result.

Our Case Study

We create a table and insert two rows, which one of them includes date

/********************************************************************** */
/*************************************************** Preparations */
/********************************************************************** */
SET LANGUAGE us_english
GO
  
/********************************************************************** */
/*************************************************** CASE Study (from the forum) */
/********************************************************************** */
DROP TABLE IF EXISTS dbo.zz
CREATE TABLE dbo.zz (
    stam VARCHAR(5) null,
    stam_date DATETIME null
)
GO
  
INSERT INTO dbo.zz VALUES('aa', '20160210')
GO
  
DECLARE @stam_date AS VARCHAR(8) = NULL
SELECT *
FROM  dbo.zz
WHERE stam_date = ISNULL(@stam_date, stam_date)
GO -- OK
  
--> Insert a row with the date February 29
INSERT INTO dbo.zz VALUES('aa', '20160229')
GO
  
--> Unexpected result! SQL Server raises an error!
DECLARE @stam_date AS VARCHAR(8) = NULL
SELECT      *
FROM  dbo.zz
WHERE stam_date = ISNULL(@stam_date, stam_date)
GO -- ERROR!
-- The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
  
--> Using COALESCE function instead of ISNULL works as expected
DECLARE @stam_date AS VARCHAR(8) = NULL
SELECT      *
FROM  dbo.zz
WHERE stam_date = COALESCE(@stam_date, stam_date)
GO


>> As we can see in the error message, we have an issue that related to converting from type VARCHAR to type DATETIME. The first question is which part of the query leads to the need to convert data type? Let's confirm that the issue is not with using the ISNULL function.

-- using ISNULL(@stam_date, stam_date) in the filter create issue
-- butusing it in the select part work OK
DECLARE @stam_date AS VArchar(8) = NULL
SELECT *,ISNULL(@stam_date, stam_date)
FROM dbo.zz
GO -- OK

The above test confirm that we can use February 29 as input to ISNULL function without any issue. But yet in our case ISNULL raises error while COALESCE works OK, What is the different behaviors?

In short: The type returned by ISNULL is different from the type that returned from COALESCE.

By default, ISNULL returns the same type as check_expression. If a literal NULL is provided as check_expression, it returns the datatype of the replacement_value. If a literal NULL is provided as check_expression and no replacement_value is provided, returns an int. Back to our case the check_expression is @stam_date, which is type VARCHAR and it's value is NULL. The replacement_value is stam_date, which is  type DATETIME and it's value is February 29 2016. But we do not use literal NULL. Instead we are taking the input from the table and from a variable. Therefore the ISNULL returns VARCHAR.

COALESCE behaves differently regarding the type that it returns! It returns the data type of expression with the highest data type precedence. If all expressions are nonnullable, the result is typed as nonnullable. Therefore in our case the COALESCE returnes type DATTIME

Let's confirm the data type returned:


declare @t sql_variant
DECLARE @S AS VArchar(8) = NULL
DECLARE @D AS datetime = '20160229'
  
SET @t = ISNULL(@S, @D) -- This returns varchar
SELECT SQL_VARIANT_PROPERTY(@t,'BaseType'AS 'ISNULL-returned-Type'
  
SET @t = COALESCE(@S, @D) -- This returns DATETIME
SELECT SQL_VARIANT_PROPERTY(@t,'BaseType'AS 'COALESCE-returned-Type'
GO


>> OK, the issue is not related to the use of ISNULL with the input of February 29 directly, but we found the different behavior between ISNULL and COALESCE in our case. The returned type is different. Now we ask which part of the query leads to the need to convert the type?

In short: Whenever we use a "WHERE" filter, SQL Server needs to compare both sides of the equation, and we can only compare elements from the same type. SQL Server uses a set of rules regarding these cases, and implicitly converts one of the types according to the rules.

If this is the case then we can reproduce the unexpected behavior using a simple query without the need of a table. This will give us the option to focus on the unexpected behavior itself. We understand that the issue is when we force SQL Server to use implicit convert the result of ISNULL function from VARCHAR to DATETIME (same is other date types like SMALLDATETIME).

-- Back to our case: Here is a much simpler case to show the issue:
DECLARE @stam_date VArchar(8) = NULL
DECLARE @D datetime = '20160229'
DECLARE @R datetime = ISNULL(@stam_date, @D) -- ERROR!!! This is the issue!
SELECT @R
-- Using any value for @D other then leap day works great!
-- Soon I will confirm this...

I explained the basic behavior of ISNULL and the different from COALESCE. Now you can look into the issue more deeply. I just want to be sure and to confirm that this is only something that related to "leap years", or in other words to February 29 BUG.


1.    Create a simple table with 100,000 dates from 1900-01-01, including the February 29 dates.
1.1  Execute a query that reproduce the unexpected behavior, which should raise error.
2.    Delete all February 29 dates from the table
2.1  Execute same query that reproduce the unexpected behavior, which should not raise error, if the issue is really a BUG related to February 29.

DROP TABLE IF EXISTS Dates
CREATE TABLE Dates (D datetime)
insert Dates
select TOP 100000 DATEADD(day, ROW_NUMBER() over (order by (select null)), '19000101')
FROM sys.all_objects AS s1
CROSS JOIN sys.all_objects AS s2
GO
  
DECLARE @stam_date AS VArchar(8) = NULL
SELECT *
FROM  Dates
WHERE D = ISNULL(@stam_date, D)
GO -- ERROR
-- The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
  
-- Delete all leap days
delete Dates
WHERE DATEPART(month,D) = 2 and DATEPART(DAY,D) > 28
GO-- (67 row(s) affected)
  
DECLARE @stam_date AS VArchar(8) = NULL
SELECT *
FROM  Dates
WHERE D = ISNULL(@stam_date, D)
GO -- OK :-)
-- The issue related only to leap day !


As someone who work with multiple languages and have lot of experience with issues that related to non-deterministic elements, whenever I see a convert issue from VARCHAR to DATETIME, my first guess is that this might be related to language and to the VARCHAR format. Moreover, the error message fit to these cases: "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value". 

* I HIGHLY recommend to read this blog regarding issues related to implicit convert to DATETIME, and the error "DateTime data type resulted in an out-of-range value".

I want to confirm that this unexpected behavior does not occur on all languages.

DECLARE @stam_date VARCHAR(8)
DECLARE @D         DATETIME 
DECLARE @R         DATETIME 
DECLARE @langname NVARCHAR(100)
DECLARE ITEM_CURSOR CURSOR
FOR select [alias] from sys.syslanguages
OPEN ITEM_CURSOR
FETCH NEXT FROM ITEM_CURSOR INTO @langname
WHILE @@FETCH_STATUS = 0 BEGIN
    SET  LANGUAGE @langname;
    BEGIN TRY
        SET @stam_date = NULL
        SET @D         = '20160229' -- using 20160228 will work OK
        SET @R         = ISNULL(@stam_date, @D) -- ERROR!!! This is the issue!
        SELECT @R, @langname, @@LANGUAGE, @@LANGID,'YES'
    END TRY
    BEGIN CATCH
         SELECT @langname, @@LANGUAGE, @@LANGID, 'NO'
    END CATCH
    FETCH NEXT FROM ITEM_CURSOR INTO @langname
END
CLOSE ITEM_CURSOR 
DEALLOCATE ITEM_CURSOR
GO

Well, Very strange :-)
A "BUG" that is not related to all languages. This actually make sense and suggest that this is not a bug, since each language displays DATE and TIME differently. For example we can see in the above result that using Japanese we do not get any error. Now that I found a specific language that do not raise the error I can demonstrate using that language 

SET  LANGUAGE 'Japanese'
DECLARE @stam_date VArchar(8) = NULL
DECLARE @D datetime = '20160229'
DECLARE @R datetime = ISNULL(@stam_date, @D)
SELECT @R
GO -- OK :-)
 
SET LANGUAGE 'English'
DECLARE @stam_date VArchar(8) = NULL
DECLARE @D datetime = '20160229'
DECLARE @R datetime = ISNULL(@stam_date, @D)
SELECT @R
GO -- ERROR!!

Maybe this behavior have nothing to do with ISNULL function?!? Maybe it is simply related to any implicit convert from VARCHAR to DATETIME, if the VARCHAR use format like some language do. We saw that the error raised when we used English. Let's examine how the English display the VARCHAR that returns from ISNULL


SET LANGUAGE 'English'
DECLARE @stam_date VArchar(8) = NULL
DECLARE @D datetime = '20160229'
PRINT ISNULL(@stam_date, @D)

The result show: "Feb 29 2", which is all the issue :-)

THERE IS NO BUG in this case!
We just needed to understand how ISNULL works and what it returns, and then remember that we used VARCHAR(8) for the variable @stam_date. This causes the string that returns from the ISNULL to cut the first 8 characters, and instead of getting "Feb 29 2016 12:00AM" we got "Feb 29 2" which cannot be converted to DATETIME. 

* The reason that any date which is not Feb 29 works OK, is that in the year 2 all other dates are valid but Feb 29 is not valid in the year 2 (we cut the string and got a date which is in the year 2 instead of 2016).

You can now go back to the start -> replace the VARCHAR(8) with VARCHAR(80) and the issue will resolved :-)

 

Conclusions

Long Story short.. The issue is with the code and there is no BUG :-)

But the question in the forum gave me a great opportunity to teach a bit about: How ISNULL and COALESCE functions works. Moreover, it gave me the opportunity to talk about the February 29 day :-)

 

Good night and see you in my next blog,
Ronen

 

Important!!!

If you develop applications or write code in general, then you must remember the date February 29 and the name "Leap year"! History full with BUGs related to the February 29 BUG.

Familiar events from history