New to SQL, having an issue with a calculated date column I want to insert in my query. I want to add 45 days to each date in the OOEXD column. The closest I can get to something working is below. Should I solve for the error message or is there a better syntax ? Thanks
select OOEXD AS [TERM DATE], DATEADD(day, 45, ooexd) AS [45 DAY]
from [Table]
Error: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
what is the datatype for OOEXD column?
Decimal (8)
Hi,
select DATEDIFF(D,'1900-Jan-01','9999-Dec-31')
output:2958463
declare
@var decimal(8) = 2958418.00
select DATEADD(DAY,45,@var)
Output: 9999-12-31 00:00:00.000
declare
@var decimal(8) = 2958419.00
select DATEADD(DAY,45,@var)
Msg 517, Level 16, State 1, Line 3
Adding a value to a 'datetime' column caused an overflow.
From above results my understanding is the decimal is implicitly converting to datetime date type and then applying dateadd function. from above results the maximum decimal value allowed in your case is "2958418" for decimal (8). May be for one of your table row the column "OOEXD" value exceeds this boundary value.
The error message is different ... so my money would be on the value for ooexd being far too large to convert to a parameter suitable for DATEADD
My WAG is that the value of [ooexd] is not a number of days, but a date in the style "20170307"
declare @var decimal(8) = 20170307.00
select DATEADD(DAY,45,@var)
gives me the "Arithmetic overflow error converting expression to data type datetime." error message
I wouldn't do the conversion this way, but this works without error:
declare @var decimal(8) = 20170307.00
select DATEADD(DAY,45,CONVERT(varchar(20), @var))
2017-04-21
Thanks MM/Kristen. Your WAG is correct on the style :). I tried using the syntax below from one of your earlier posts and get the following:
SELECT convert(datetime, convert(DECIMAL(8),ooexd)) as [TERM DATE], DATEADD(day, 45, ooexd) AS [45 DAY]
FROM psa.[dbo].[SMFD35_XPSFILE_Owner_Operator]
Error: Msg 8115, Level 16, State 2, Line 10
Arithmetic overflow error converting expression to data type datetime.