May 16, 2016 at 5:13 am
I've one table with one specific integer column containing date as YYYYMMDD format and apart from date it also may contain value 99999999. I'm using this table in a view with saving that column in a datetime column with the condition if column value = 99999999 then consider it as 31 December 9999. I've written this query as follows:
SELECT COL1, COL2, COL3,....
BusinessDate = CASE WHEN tbl.BusinessDate = '99999999' THEN CAST('99991231' AS DATETIME)
ELSE CAST(CAST(tbl.BusinessDate AS VARCHAR(8)) AS DATETIME) END,
COL12, COL13, COL14
FROM dbo.TableNameT1tbl
WHERE col1 = 'xyz'
-- Only derivation happening is for BusinessDate
It gives the error:
Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
However if i run the query like this, it works fine:
SELECT BusinessDate = CASE WHEN tbl.BusinessDate = '99999999' THEN CAST('99991231' AS DATETIME)
ELSE CAST(CAST(tbl.BusinessDate AS VARCHAR(8)) AS DATETIME) END
FROM dbo.TableNameT1tbl
WHERE col1 = 'xyz'
Let me know if I'm doing anything wrong or sql performing some tricks
May 16, 2016 at 5:51 am
Are you saying that when you work with BusinessDate on its own, you don't get the error, but you do when you include other columns in your query? That doesn't make sense.
Of course, the proper way to solve this is to store all your dates as one of the proper date/time data types. If you don't have that option, you could do something like this:
WITH ProperDates AS (
SELECT
COL1
,COL2
,COL3
...
,COL12
,COL13
,COL14
,CASE
WHEN BusinessDate = '99999999' THEN '99991231'
ELSE BusinessDate
END AS BusinessDate
FROM dbo.TableNameT1
WHERE col1 = 'xyz'
)
SELECT
COL1
,COL2
,COL3
...
,CAST (BusinessDate AS datetime)
,COL12
,COL13
,COL14
FROM ProperDates
or:
SELECT
COL1
,COL2
,COL3
...
,CAST (CASE
WHEN BusinessDate = '99999999' THEN '99991231'
ELSE BusinessDate
END AS datetime)
,COL12
,COL13
,COL14
FROM dbo.TableNameT1
John
May 16, 2016 at 5:52 am
As a test, have you tried to create a view consisting only of the BusinessDate column calculation and none of the other columns? That would be my first "Sherlock Holmes" step in troubleshooting this.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 16, 2016 at 6:23 am
I would also try the following sql to see what you get.
select top 10 tbl.BusinessDate
from dbo.TableNameT1 tbl
where tbl.BusinessDate < '17530101'
May 16, 2016 at 6:54 am
I tried the following but still the same issue:
CAST (CASE
WHEN BusinessDate = '99999999' THEN '99991231'
ELSE cast(BusinessDate as varchar(8))
END AS datetime)
And yes, when I'm trying it with other columns, it's giving error. When simply selecting only this column, it works fine. Very very strange, I must say.
May 16, 2016 at 7:06 am
Please will you check - is TableNameT1 actually a table, or is it a view?
SELECT type_desc FROM sys.objects
WHERE SCHEMA_NAME(schema_id) = 'dbo'
AND OBJECT_NAME(object_id) = 'TableNameT1'
John
May 16, 2016 at 7:22 am
It's a table. Used inside the view. I've given here just a select from this table. Actually there are many joins with other tables. But the select statement is same way as mentioned.
May 16, 2016 at 7:23 am
One way this could happen is if the inclusion of other columns changed the query plan in just the right way.
Imagine that all the rows in the table that match the WHERE clause have values that can indeed be converted to datetime as you are doing, but that some rows that don't satisfy the WHERE clause have values that are out of range.
Now, it may well be that the query that succeeds is filtering out rows using the WHERE clause, and then doing a compute scalar to do the conversion. The other (failing) query has to get a different plan because of the additional columns, and that plan involves doing the compute scalar for the conversion, and then filtering out rows.
You can check the estimated execution plans to see if that could be the case.
Either way, the main way forward is to track down the out-of-range values.
That could be a value beyond December 31, 9999, 23:59:59.997, a value before January 1, 1753 00:00:00.000, or a value that has an incorrect value in the months or days position (most commonly this is from swapping the months/days value, where a date is entered as YYYYDDMM when the database expects YYYYMMDD).
Try running this to find the highest and lowest values for each segment to see which could be out of range:
SELECT MAX(tbl.BusinessDate/10000) AS highest_year,
MIN(tbl.BusinessDate/10000) AS lowest_year,
MAX((tbl.BusinessDate%10000)/100) AS highest_month,
MIN((tbl.BusinessDate%10000)/100) AS lowest_month,
MAX(tbl.BusinessDate%100) AS highest_day,
MIN(tbl.BusinessDate%100) AS lowest_day
WHERE tbl.BusinessDate!=99999999
Once you find it, you can track down and hopefully fix the offending value.
If you can't fix the data, there are other ways, like using a CASE expression to restrict the conversion to only those values that map to valid dates. Of course, you then still have to figure out how to handle the invalid data.
Cheers!
May 16, 2016 at 7:37 am
highest_yearlowest_yearhighest_monthlowest_monthhighest_daylowest_day
2099 2005 12 1 31 1
No issues here. Also as I mentioned, if mentioning this only derivation in select clause, it works fine.
May 16, 2016 at 7:51 am
Also as I mentioned, if mentioning this only derivation in select clause, it works fine.
And I had explained how the inclusion of other columns could lead to such a result if you have out-of-range values in the data 🙂
From those results it looks like you don't have any blatantly out-of-range values, but you could still have some.
For example, specifying 31 as the day for a month with only 30 days, or 29 for February on a non-leap year would also throw this error.
Just to check that possibility, try this:
SELECT (tbl.BusinessDate%10000)/100) AS [month],
MAX(tbl.BusinessDate%100) AS highest_day
FROM dbo.TableNameT1tbl
WHERE tbl.BusinessDate!=99999999
GROUP BY (tbl.BusinessDate%10000)/100)
Cheers!
May 16, 2016 at 8:22 am
I checked all the distinct dates and everything looks good. From your query it was also more clear. All the date parts were good and under the definition of that month.
I tried running the same in new session as well but same issue.
May 16, 2016 at 8:32 am
Ah, if you can check all the distinct dates, then it must be a small(ish) list.
Any chance of posting sample DDL and data that recreates the issue, or even just that list of distinct integer values?
Cheers!
May 16, 2016 at 9:16 am
sqlnaive (5/16/2016)
I've one table with one specific integer column containing date as YYYYMMDD format and apart from date it also may contain value 99999999. I'm using this table in a view with saving that column in a datetime column with the condition if column value = 99999999 then consider it as 31 December 9999. I've written this query as follows:
SELECT COL1, COL2, COL3,....
BusinessDate = CASE WHEN tbl.BusinessDate = '99999999' THEN CAST('99991231' AS DATETIME)
ELSE CAST(CAST(tbl.BusinessDate AS VARCHAR(8)) AS DATETIME) END,
COL12, COL13, COL14
FROM dbo.TableNameT1tbl
WHERE col1 = 'xyz'
-- Only derivation happening is for BusinessDate
It gives the error:
Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
However if i run the query like this, it works fine:
SELECT BusinessDate = CASE WHEN tbl.BusinessDate = '99999999' THEN CAST('99991231' AS DATETIME)
ELSE CAST(CAST(tbl.BusinessDate AS VARCHAR(8)) AS DATETIME) END
FROM dbo.TableNameT1tbl
WHERE col1 = 'xyz'
Let me know if I'm doing anything wrong or sql performing some tricks
Slightly off topic, if your BusinessDate is stored as an integer, why are you comparing it to a string? For a constant, there won't be a huge performance penalty, but if you join an integer column to a string column, there will be an implicit conversion which will cause a performance penalty.
Why are you using 99999999 instead of 99991231?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 16, 2016 at 9:45 am
drew.allen (5/16/2016)
sqlnaive (5/16/2016)
I've one table with one specific integer column containing date as YYYYMMDD format and apart from date it also may contain value 99999999. I'm using this table in a view with saving that column in a datetime column with the condition if column value = 99999999 then consider it as 31 December 9999. I've written this query as follows:
SELECT COL1, COL2, COL3,....
BusinessDate = CASE WHEN tbl.BusinessDate = '99999999' THEN CAST('99991231' AS DATETIME)
ELSE CAST(CAST(tbl.BusinessDate AS VARCHAR(8)) AS DATETIME) END,
COL12, COL13, COL14
FROM dbo.TableNameT1tbl
WHERE col1 = 'xyz'
-- Only derivation happening is for BusinessDate
It gives the error:
Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
However if i run the query like this, it works fine:
SELECT BusinessDate = CASE WHEN tbl.BusinessDate = '99999999' THEN CAST('99991231' AS DATETIME)
ELSE CAST(CAST(tbl.BusinessDate AS VARCHAR(8)) AS DATETIME) END
FROM dbo.TableNameT1tbl
WHERE col1 = 'xyz'
Let me know if I'm doing anything wrong or sql performing some tricks
Slightly off topic, if your BusinessDate is stored as an integer, why are you comparing it to a string? For a constant, there won't be a huge performance penalty, but if you join an integer column to a string column, there will be an implicit conversion which will cause a performance penalty.
Why are you using 99999999 instead of 99991231?
Drew
+1 for your point on using string. I've made that change already though. Regarding second point, I'll be using 99991231 instead of 99999999.
May 16, 2016 at 9:50 am
sqlnaive (5/16/2016)
drew.allen (5/16/2016)
sqlnaive (5/16/2016)
I've one table with one specific integer column containing date as YYYYMMDD format and apart from date it also may contain value 99999999. I'm using this table in a view with saving that column in a datetime column with the condition if column value = 99999999 then consider it as 31 December 9999. I've written this query as follows:
SELECT COL1, COL2, COL3,....
BusinessDate = CASE WHEN tbl.BusinessDate = '99999999' THEN CAST('99991231' AS DATETIME)
ELSE CAST(CAST(tbl.BusinessDate AS VARCHAR(8)) AS DATETIME) END,
COL12, COL13, COL14
FROM dbo.TableNameT1tbl
WHERE col1 = 'xyz'
-- Only derivation happening is for BusinessDate
It gives the error:
Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
However if i run the query like this, it works fine:
SELECT BusinessDate = CASE WHEN tbl.BusinessDate = '99999999' THEN CAST('99991231' AS DATETIME)
ELSE CAST(CAST(tbl.BusinessDate AS VARCHAR(8)) AS DATETIME) END
FROM dbo.TableNameT1tbl
WHERE col1 = 'xyz'
Let me know if I'm doing anything wrong or sql performing some tricks
Slightly off topic, if your BusinessDate is stored as an integer, why are you comparing it to a string? For a constant, there won't be a huge performance penalty, but if you join an integer column to a string column, there will be an implicit conversion which will cause a performance penalty.
Why are you using 99999999 instead of 99991231?
Drew
+1 for your point on using string. I've made that change already though. Regarding second point, I'll be using 99991231 instead of 99999999.
My recommendation for that would be to use 99990101 instead. It can be represented by just '9999' (implicit conversion to 99990101 for date/time datatypes) and leaves some headroom for calculations in WHERE clauses like AND SomeDateColumn <= @EndDate+1
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply