SQLTeam.com | Weblogs | Forums

Comparing to Datetime Gives Arithmetic Overflow Error

Hi exerts.

I just want to select all jobs that have failed in the past day. Got a little problem with my TSQL code:

select @@SERVERNAME as 'Server', a.name as 'Job Name',
b.run_date as 'Run Date', b.run_status as 'Job Status'
from sysjobs a INNER JOIN sysjobhistory b ON a.job_id = b.job_id
where a.enabled = 1 and b.step_id = 0 and b.run_status = 0 AND DATEDIFF(day, getDate(), b.run_date) <= 1
order by b.run_date;

"Arithmetic overflow error converting expression to data type datetime"

Thanks for any suggestions.

What data type is b.run_date.

Try the following to find some items that might be null or an invalid date

TRY_CONVERT

1 Like

Thanks. b.run_date is integer

So I tried this:
Use msdb;

declare @curdate int;
set @curdate = (select convert(int, getdate()))

select @@SERVERNAME as 'Server', a.name as 'Job Name',
b.run_date as 'Run Date', b.run_status as 'Job Status'
from sysjobs a INNER JOIN sysjobhistory b ON a.job_id = b.job_id
where a.enabled = 1 and b.step_id = 0 and b.run_status = 0 AND DATEDIFF(day, @curdate, b.run_date) <= 1
order by b.run_date

Error: Arithmetic overflow error converting expression to data type datetime.

Now you are just hacking with tje same end result

What are some sample values in that column

use the built in function

select @@SERVERNAME as 'Server', 
      a.name as 'Job Name',
	  dbo.agent_datetime(b.run_date, run_time),
       b.run_date as 'Run Date', b.run_status as 'Job Status'
from sysjobs a INNER JOIN sysjobhistory b ON a.job_id = b.job_id
where 1 = 1 
  and a.enabled = 1 
  and b.step_id = 0 
  and b.run_status = 0 
  and DATEDIFF(day, getDate(), dbo.agent_datetime(b.run_date, run_time)) <= 1
order by b.run_date;

might not the best for performance

1 Like

or

select @@SERVERNAME as 'Server', 
      a.name as 'Job Name',
	  filterdate,
       x.run_date as 'Run Date', 
	   x.run_status as 'Job Status'
 from sysjobs a 
INNER join (select job_id, run_date, 
run_status, 
step_id, 
dbo.agent_datetime(run_date, run_time) as filterdate 
             from sysjobhistory) x
 on a.job_id = x.job_id
where a.enabled = 1 
  and x.step_id = 0 
  and x.run_status = 0 
  and DATEDIFF(day, getDate(), filterdate) <= 1
order by x.run_date;
1 Like

You should never use functions against a table column in a WHERE (or JOIN) clause. Instead, convert the other value to match the format of the column:

SELECT 
    @@SERVERNAME as 'Server', j.name as 'Job Name',
    jh.run_date as 'Run Date', jh.run_status as 'Job Status'
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory jh ON j.job_id = jh.job_id
WHERE j.enabled = 1 AND jh.step_id = 0 AND jh.run_status = 0 AND 
    jh.run_date >= CONVERT(varchar(8), DATEADD(DAY, -1, GETDATE()), 112) AND
    jh.run_date <= CONVERT(varchar(8), GETDATE(), 112)
ORDER BY jh.run_date;
2 Likes

That looks like a bunch of functions in a WHERE clause to me :grinning_face_with_smiling_eyes:

But NOT "against a table column". Functions against literal / constant values don't matter, since they won't prevent index seeks and are only performed once. Functions against a table column are applied for every row that is read, and, as noted, prevent index seeks on indexes on that column.

2 Likes

Nice!!

Here's a comparison:

IF OBJECT_ID('tempdb.dbo.#test_table_column_convert') IS NOT NULL
    DROP TABLE #test_table_column_convert;
CREATE TABLE #test_table_column_convert (
    run_date int NOT NULL PRIMARY KEY --YYYYMMDD
    );

;WITH
cte_tally10 AS (
    SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally100 AS (
    SELECT 0 AS number FROM cte_tally10 c1 CROSS JOIN cte_tally10 c2
),
cte_tally10K AS (
    SELECT 0 AS number UNION ALL
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number FROM cte_tally100 c1 CROSS JOIN cte_tally100 c2
)
INSERT INTO #test_table_column_convert ( run_date )
SELECT CONVERT(varchar(8), DATEADD(DAY, t.number, '20000101'), 112)
FROM cte_tally10K t
--SELECT TOP (100) * FROM #test_table_column_convert

SET STATISTICS IO ON;

SELECT TOP (1) *
FROM #test_table_column_convert
WHERE CONVERT(varchar(8), run_date) = '20210505' --must scan whole table
SELECT TOP (1) *
FROM #test_table_column_convert
WHERE run_date = CONVERT(varchar(8), GETDATE(), 112) --can do index seek (lookup)

SET STATISTICS IO OFF;
2 Likes

Thanks, these are both good solutions. But yosiasz, yours returns rows for jobs that failed days ago as well as failed today.

ScottPletcher, yours works well, returning only those jobs which failed in the past day.
But as I think more about it, I need to only return jobs which failed on the most recent run which was in the past day. I know \that's a change from my original request.

This will test only the last run of each job within the last week.

SELECT 
    @@SERVERNAME as 'Server', j.name as 'Job Name',
    jh.run_date as 'Run Date', jh.run_status as 'Job Status'
FROM msdb.dbo.sysjobs j
CROSS APPLY (
    SELECT TOP (1) jh.*, ROW_NUMBER() OVER(PARTITION BY jh.job_id ORDER BY jh.run_date DESC) AS row_num
    FROM msdb.dbo.sysjobhistory jh
    WHERE 
        jh.step_id = 0 AND 
        jh.run_date >= CONVERT(varchar(8), DATEADD(DAY, -7, GETDATE()), 112) AND
        jh.run_date <= CONVERT(varchar(8), GETDATE(), 112)
    ORDER BY jh.run_date DESC
) AS jh
WHERE j.enabled = 1 AND jh.run_status = 0
ORDER BY j.name, jh.run_date;
1 Like

Thanks, ScottPletcher. That looks promising, but it doesn't return any rows. I forced a job to fail just now and it didn't return any rows. I changed the -7 to various values but no luck. Thanks

I was trying different things, that query is a bad mix of options. Try this instead, and adjust the dates as needed:

SELECT 
    @@SERVERNAME as 'Server', j.name as 'Job Name',
    jh.run_date as 'Run Date', jh.run_status as 'Job Status'
FROM msdb.dbo.sysjobs j
LEFT OUTER JOIN (
    SELECT ROW_NUMBER() OVER(PARTITION BY jh.job_id ORDER BY jh.run_date DESC) AS row_num, jh.* 
    FROM msdb.dbo.sysjobhistory jh
    WHERE 
        jh.step_id = 0 AND 
        jh.run_date >= CONVERT(varchar(8), DATEADD(DAY, -7, GETDATE()), 112) AND
        jh.run_date <= CONVERT(varchar(8), GETDATE() + 1, 112)
    --ORDER BY jh.run_date DESC
) AS jh ON jh.job_id = j.job_id
WHERE j.enabled = 1 AND jh.run_status = 0
ORDER BY j.name, jh.run_date;