Weird T-SQL DATEADD Overflow

Question

I'm trying to add a numeric(16,8) to a smalldatetime. I receive an overflow error which I cannot make sense of.

metric_value is the number, business_date is the smalldatetime.

SELECT
    rsda.name
    , business_date
    , metric_value
    , DATEADD(dd, metric_value, business_date) AS o_dt
    , metric.name
FROM [redacted] met
INNER JOIN [redacted] rsda ON met.bu_id = rsda.data_accessor_id
INNER JOIN Metric ON met.metric_id = metric.metric_id
WHERE CHARINDEX('Remodel', metric.name) > 0

Msg 517, Level 16, State 2, Line 1
Adding a value to a 'smalldatetime' column caused an overflow.

I realize that the obvious answer is "One of your dates adds beyond year 9999" but that isn't the case here. All of the dates are in 2017, and all of the numbers are whole numbers below 100, at least when the WHERE clause is true.

The met table has tons of other unrelated data to my 'Remodel' criteria and I'm wondering if that could cause the error. It is a strange table but I do not have control over its design. Is it possible that part of the DATEADD process occurs before my WHERE clause is applied? I cannot imagine what else is going on.

Edit. When i remove the DATEADD field:

Here's the first row of 'ORDER BY business_date ASC'

+----------+---------------------+--------------+-------------+
|   name   |    business_date    | metric_value |    name     |
+----------+---------------------+--------------+-------------+
| 466 - 94 | 2017-03-13 00:00:00 | 59.00000000  | FullRemodel |
+----------+---------------------+--------------+-------------+

ORDER BY business_date DESC

+----------+---------------------+-------------+-------------+
| 440 - 87 | 2017-07-31 00:00:00 | 38.00000000 | FullRemodel |
+----------+---------------------+-------------+-------------+

ORDER BY metric_value ASC

+----------+---------------------+------------+----------------+
| 471 - 05 | 2017-05-01 00:00:00 | 0.00000000 | PartialRemodel |
+----------+---------------------+------------+----------------+

ORDER BY metric_value DESC

+----------+---------------------+-------------+-------------+
| 466 - 86 | 2017-03-13 00:00:00 | 59.00000000 | FullRemodel |
+----------+---------------------+-------------+-------------+

Ok, good to know. But none of the dates will exceed 2017 in this case. — Satchel, Jun 22 '17 at 18:08
I'd remove the DATEADD() field and look at the returned data to see if there is something unexpected in the metric_value or business_date fields. — Jay Buckman, Jun 22 '17 at 18:23
I already did that. But I'll edit my original question with "proof" — Satchel, Jun 22 '17 at 18:55
"Is it possible that part of the DATEADD process occurs before my WHERE clause is applied? ". Yes. Same as https://stackoverflow.com/q/7192524/73226 — Martin Smith, Jun 22 '17 at 19:12
@MartinSmith Interesting. What do you suggest in my case? Subquery? Temp table? Something more elegant than those two options? — Satchel, Jun 22 '17 at 19:45
My guess is that you have an unexpected date in your data or one of your metric_values isn't correct. SELECT out your data to see what you're working with. — Shawn, Jun 22 '17 at 19:47
A subquery isn't safe it can be pushed into there too. Use `CASE WHEN metric_value BETWEEN datediff(dd, business_date, '1900-01-01') AND datediff(dd, business_date, '2079-06-06') THEN DATEADD(dd, metric_value, business_date) END` — Martin Smith, Jun 22 '17 at 19:57
Do any of your metric_value values have anything in the decimal positions, or are they all .00000000? — Shawn, Jun 22 '17 at 20:09
The error does seem to indicate that the smalldatetime is being overflowed. I'd check your metric_value fields. — Shawn, Jun 22 '17 at 20:18
Also, in your SELECT, you are pulling both rsda.name and metric.name without aliasing either one. This ends with two "name" columns. You won't be able to reference it outside of your results. — Shawn, Jun 22 '17 at 20:46
RE: My comment about decimals in metric_value. Really it doesn't matter if they have decimals. When it goes into the dateadd() function, it's truncated (not rounded) to an int anyway. So 1.99999999 will be 1, not 2. — Shawn, Jun 22 '17 at 20:56

score 1 · Accepted Answer · answered Jun 22 '17 at 20:04

1

Is it possible that part of the DATEADD process occurs before my WHERE clause is applied?

Yes. The compute scalar calculating the expression can run on rows before they get filtered out. Some links on this topic are in my answer here.

You can use a CASE expression to only do the dateadd if the two inputs would result in a valid date for the smalldatetime range.

CASE 
 WHEN metric_value BETWEEN datediff(day, business_date, '1900-01-01') 
                       AND datediff(day, business_date, '2079-06-06')
 THEN 
  DATEADD(day, metric_value, business_date) 
END

answered Jun 22 '17 at 20:04

Martin Smith

438,706
87
741
845

Thanks, and for the links in your other comment. – Satchel Jun 22 '17 at 20:24
This is a good method to prevent the error, but, if the user has control over the data, I still think this logic should be put in the WHERE clause to find the bad row and then fix the data. – Shawn Jun 22 '17 at 20:24

Shawn · Answer 2 · 2017-06-23T13:30:54.687

0

Or you could also use (in the SELECT)

CASE 
  WHEN metric_value <= datediff(day, GETDATE(), '20790606') 
  THEN DATEADD(day, metric_value, business_date) 
  ELSE NULL 
END AS o_dt

Using BETWEEN on datetimes can get hinky, so I try to avoid it.

EDIT: Per Martin's comment below, I no longer consider this a valid solution.

In combination with @Martin Smith's answer, you could also change your query to a subquery, and it should filter out the rows that are causing the error.

SELECT t1.rsda_name
    , t1.business_date
    , t1.metric_value
    , DATEADD(day, t1.metric_value, t1.business_date) AS o_dt
    , t1.metric_name
FROM (
    SELECT
        rsda.name AS rsda_name
        , business_date
        , metric_value
        , metric.name AS metric_name
    FROM [redacted] met
    INNER JOIN [redacted] rsda ON met.bu_id = rsda.data_accessor_id
    INNER JOIN Metric ON met.metric_id = metric.metric_id
    WHERE CHARINDEX('Remodel', metric.name) > 0
) t1

edited Jun 23 '17 at 13:30

answered Jun 22 '17 at 21:14

Shawn

4,758
1
20
29

And it might not be a bad idea to leave a comment as to why you are using 06/06/2079 as your date. – Shawn Jun 22 '17 at 21:16
This is not a safe solution. The compute scalar can be pushed into the sub query. See https://connect.microsoft.com/SQLServer/feedback/details/537419/sql-server-should-not-raise-illogical-errors – Martin Smith Jun 23 '17 at 07:29
@MartinSmith Interesting. Didn't know that. I'll edit my answer for future users. :-) – Shawn Jun 23 '17 at 13:26
@MartinSmith I imagine that wasn't a fun bug to encounter. :-/ – Shawn Jun 23 '17 at 13:39

score -1 · Answer 3 · answered Jun 22 '17 at 19:35

-1

You need to replace 'dd' with DAY whithin the DATEADD() function.

answered Jun 22 '17 at 19:35

Mustafa Erdogan

103
7

1

While I agree that putting DAY, YEAR, MONTH, etc in your DATEADD function, "dd" is still a perfectly valid abbreviation for "day". – Shawn Jun 22 '17 at 19:38
Yes, the query still fails. – Satchel Jun 22 '17 at 19:44

Weird T-SQL DATEADD Overflow

3 Answers3