Did the way SQL Server 2008 evaluates a CASE WHEN

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Did the way SQL Server 2008 evaluates a CASE WHEN

Author

Topic

Kanwulf
Starting Member

11 Posts

Posted - 2010-04-21 : 03:54:04

I am looking at a weird issue since morning. Below query works fine in SQL Server 2005 but throws a datetime overflow exception in 2008:

select 1 from sys.tables where
((
CASE WHEN
(CASE WHEN CAST(CAST('9999-12-31 00:00:00' AS DATETIME) as NUMERIC(38,10)) + ISNULL(CAST(1 AS NUMERIC(38, 28)),0) >= 2958464
THEN '9999/12/31'
WHEN CAST(CAST('9999-12-31 00:00:00' AS DATETIME) AS NUMERIC(38, 10)) + ISNULL(CAST(1 AS NUMERIC(38,28)),0) <= 0
THEN '1900/01/01'
ELSE DATEADD(dd,ISNULL(CAST(1 AS NUMERIC(38, 10)), 0),CAST('9999-12-31 00:00:00' AS DATETIME))
END) IS NULL
THEN 1
ELSE 0
END =1 ))

The weirdness comes from the fact that if I extract the case when statement and execute it, it works just fine:

SELECT
CASE WHEN
(CASE WHEN CAST(CAST('9999-12-31 00:00:00' AS DATETIME) as NUMERIC(38,10)) + ISNULL(CAST(1 AS NUMERIC(38, 28)),0) >= 2958464
THEN '9999/12/31'
WHEN CAST(CAST('9999-12-31 00:00:00' AS DATETIME) AS NUMERIC(38, 10)) + ISNULL(CAST(1 AS NUMERIC(38,28)),0) <= 0
THEN '1900/01/01'
ELSE DATEADD(dd,ISNULL(CAST(1 AS NUMERIC(38, 10)), 0),CAST('9999-12-31 00:00:00' AS DATETIME))
END) IS NULL
THEN 1
ELSE 0
END

My version of the SQL Server 2008 is:
SQL Server 2008 R2 (CTP) - 10.50.1352.12 (X64)

I am really stuck with this, so any help would be appreciated.

Ionut Hrubaru

Kanwulf
Starting Member

11 Posts

Posted - 2010-04-21 : 08:01:42

Since the issue occurs only in case on constants we can trick the engine not to throw the error, like this:

select 1 from sys.tables where
((
CASE WHEN
(CASE WHEN CAST(CAST((select '9999-12-31 00:00:00' AS MY_COL) AS DATETIME) as NUMERIC(38,10)) + ISNULL(CAST(1 AS NUMERIC(38, 28)),0) >= 2958464
THEN '9999/12/31'
WHEN CAST(CAST((select '9999-12-31 00:00:00' AS MY_COL) AS DATETIME) AS NUMERIC(38, 10)) + ISNULL(CAST(1 AS NUMERIC(38,28)),0) <= 0
THEN '1900/01/01'
ELSE DATEADD(dd,ISNULL(CAST(1 AS NUMERIC(38, 10)), 0),CAST((select '9999-12-31 00:00:00' AS MY_COL) AS DATETIME))
END) IS NULL
THEN 1
ELSE 0
END =1 ))

Issue closed I guess :)

Ionut Hrubaru

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources