The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large.

I am getting the following error. I cannot change the sql its part of a prodcut is there an option at the sql server level that can avoid this error ?

08:22:05.451 Dbg 10739 ODBC: id='22.1' req='1' SQL: SELECT JOB_ID, JOB_NAME, JOB_VERSION, convert(char(19), DATEADD(MINUTE, -420, START_TIME), 120) START_TIME, convert(char(19), DATEADD(MINUTE, -420, END_TIME), 120) END_TIME, DURATION, STATUS FROM (SELECT * FROM ADMIN_ETL_JOB_HISTORY UNION SELECT * FROM ADMIN_ETL_JOB_STATUS WHERE STATUS = 'RUNNING') JOBS  ORDER BY START_TIME DESC
 +37318  odbcerr: [Microsoft][SQL Native Client][SQL Server]The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.
 +37318  MSG_ERROR status='serverError' msg='[Microsoft][SQL Native Client][SQL Server]The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart. '
pcombAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
No.

The error is suggesting you use minutes instead of seconds, or hours instead of minutes, or days instead of hours, etc.

But there is no reference to DATEDIFF in the shown SQL (formatted below)
SELECT
      JOB_ID
    , JOB_NAME
    , JOB_VERSION
    , CONVERT(char(19), DATEADD(MINUTE, -420, START_TIME), 120) START_TIME
    , CONVERT(char(19), DATEADD(MINUTE, -420, END_TIME), 120) END_TIME
    , DURATION
    , STATUS
FROM (
      SELECT
            *
      FROM ADMIN_ETL_JOB_HISTORY
      UNION
            SELECT
                  *
            FROM ADMIN_ETL_JOB_STATUS
            WHERE STATUS = 'RUNNING'
) JOBS
ORDER BY
      START_TIME DESC

Open in new window

Do you know if there are "computed columns" on any of the tables included in the query above?

{+edit 2}are either of these views? ADMIN_ETL_JOB_HISTORY or ADMIN_ETL_JOB_STATUS
if so, do those views contain datediff?
0
pcombAuthor Commented:
Paul both the above are views and it looks like they have some computed values
/*==============================================================*/
/* View: ADMIN_ETL_JOB_HISTORY                                  */
/*==============================================================*/
create view [dbo].[ADMIN_ETL_JOB_HISTORY] as
select
   JOB_ID,
   JOB_NAME,
   JOB_VERSION,
   MIN(GMT_START_TIME)   AS START_TIME,
   MAX(GMT_END_TIME)     AS END_TIME,
   DATEDIFF(SECOND,MIN(GMT_START_TIME),MAX(GMT_END_TIME)) AS DURATION,
   MAX(STATUS)           AS STATUS
from
   CTL_ETL_HISTORY
where
   JOB_ID NOT IN (SELECT JOB_ID FROM CTL_WORKFLOW_STATUS WHERE STATUS in ('RUNNING'))
group by
   JOB_ID,
   JOB_NAME,
   JOB_VERSION
GO

create view [dbo].[ADMIN_ETL_JOB_STATUS] as
select
   MAX(JOB_ID) as JOB_ID,
   JOB_NAME,
   MAX(JOB_VERSION) as JOB_VERSION,
   MIN(START_TIME)  as START_TIME,
  CASE
      WHEN MIN(START_TIME) < MAX(END_TIME)
      THEN MAX(END_TIME)
      ELSE NULL
  END as END_TIME,
  CASE
      WHEN MIN(START_TIME) < MAX(END_TIME)
      THEN DATEDIFF(SECOND,MIN(START_TIME),MAX(END_TIME))
      ELSE NULL
  END as DURATION,
   MAX(STATUS) as STATUS
from
   CTL_WORKFLOW_STATUS
where
  STATUS NOT IN ('NOT_CONFIGURED','INSTALLED')
  AND JOB_ID in (select MAX(JOB_ID) from CTL_WORKFLOW_STATUS group by JOB_NAME)
group by
   JOB_NAME
GO
0
PortletPaulfreelancerCommented:
Well the combination of some dsta and using seconds in the datediff function is exceeding a limit.

At least you know where it is now. How it is solved i don't know.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.