Adding a value to a 'datetime' column caused overflow.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dt - 26 Aug 2008 21:23 GMT

This is a simple select statement where I am trying to convert a Julian date.
Where one of the fields is zero I get this error.

My Select statement is:

select
DATEPART(yy, DATEADD(dd, date_last_inv - 722815, '1/1/80'))as date_last_inv,
DATEPART(yy, DATEADD(dd, date_last_cm - 722815, '1/1/80')) as date_last_cm,
DATEPART(yy, DATEADD(dd, date_last_pyt - 722815, '1/1/80'))as date_last_pyt
from aractcus

the date_last_inv, cm, pyt is a int column. How can I get around the zero's.

The full error is:

Server: Msg 517, Level 16, State 1, Line 1
Adding a value to a 'datetime' column caused overflow.

Reply to this Message

Tariq - 27 Aug 2008 15:26 GMT

Try it,

SELECT
CASE WHEN DATE_LAST_INV>640000 THEN DATEPART(YY, DATEADD(DD, DATE_LAST_INV -
722815, '1/1/80')) ELSE 0 END AS DATE_LAST_INV,
CASE WHEN DATE_LAST_INV>640000 THEN DATEPART(YY, DATEADD(DD, DATE_LAST_CM -
722815, '1/1/80')) ELSE 0 END AS DATE_LAST_CM,
CASE WHEN DATE_LAST_INV>640000 THEN DATEPART(YY, DATEADD(DD, DATE_LAST_PYT -
722815, '1/1/80')) ELSE 0 ENDAS DATE_LAST_PYT
FROM ARACTCUS

Reply to this Message

dt - 27 Aug 2008 17:06 GMT

Thank you.

> Try it,
>
[quoted text clipped - 6 lines]
> 722815, '1/1/80')) ELSE 0 ENDAS DATE_LAST_PYT
> FROM ARACTCUS

Reply to this Message