June 3, 2013 at 6:12 am
ok a few years ago, I was poking around with calculating Lunar Phases,which I adapted off of a math web site: and with a bit of help from the community in this thread, we came up with a better ITVF version for it in this thread:
mis-appying a cross apply table value function?
so today I decided to apply that ITVF apply against a larger tally table than before; basically 1101 years, from 1900-01-01 to 3000-0101.
that ITVF returns an error when it starts calculating against any date greater than
2808-07-13 00:00:00.000 and i sure as heck can't see the forest thru the trees, and see where the calculation is hitting a data type limit;
Msg 8115, Level 16, State 8, Line 4
Arithmetic overflow error converting numeric to data type numeric.
here's a test harness that allows you to recreate the issue; can you help me figure out where i'm overflowing?
--this TallyCalendar table goes from year 0 (1900-01-01 to year 3000-01-01
--calculation crashes on dates greater than '2808-07-11 00:00:00.000'
--initialize our vars
With TallyC AS (SELECT convert(datetime,RW) AS TheDate
FROM (
SELECT TOP ( datediff(dd,0, dateadd(year,1101,0)) )
ROW_NUMBER() OVER (ORDER BY sc1.id) -1 AS RW
FROM Master.dbo.SysColumns sc1
CROSS JOIN Master.dbo.SysColumns sc2
) X),
cteDtSplit AS (
SELECT TheDate,
YEAR(TheDate) AS TheYear,
MONTH(TheDate) AS TheMonth,
DAY(TheDate) AS TheDay
FROM TallyC
),
cteDates AS (
SELECT TheDate,
TheYear - FLOOR( ( 12 - TheMonth ) / 10 ) AS yy,
CASE
WHEN (TheMonth + 9) >= 12
THEN (TheMonth + 9) - 12
ELSE TheMonth + 9
END AS mm,
TheDay AS dd
FROM cteDtSplit
),
ctePre AS (
SELECT TheDate,
dd,
FLOOR( 365.25 * ( yy + 4712 ) ) AS k1,
FLOOR( 30.6 * mm + 0.5 ) AS k2,
FLOOR( FLOOR( ( yy / 100 ) + 49 ) * 0.75 ) - 38 AS k3
FROM cteDates
),
cteAdj AS (
SELECT TheDate,
CASE
WHEN (k1 + k2 + dd + 59) > 2299160
THEN (k1 + k2 + dd + 59) - k3
ELSE k1 + k2 + dd + 59
END AS jd -- % for dates in Julian calendar
FROM ctePre
),
cteFin AS (
SELECT TheDate,
((( jd - 2451550.1 ) / 29.530588853) - CAST((FLOOR( ( jd - 2451550.1 ) / 29.530588853 )) AS DECIMAL(20,16))) * 29.53 AS AG
FROM cteAdj
)
SELECT TheDate,CASE
WHEN ag < 1.84566 THEN 'New Moon'
WHEN ag < 5.53699 THEN 'Waxing crescent'
WHEN ag < 9.22831 THEN 'First quarter'
WHEN ag < 12.91963 THEN 'Waxing near full moon' -- the web calls this "Gibbous ", WTH is that?
WHEN ag < 16.61096 THEN 'Full Moon '
WHEN ag < 20.30228 THEN 'Waning near full moon' -- the web calls this "Gibbous ", WTH is that?
WHEN ag < 23.99361 THEN 'Last quarter'
WHEN ag < 27.68493 THEN 'Waning crescent'
ELSE 'New Moon'
END AS Phase
FROM cteFin
Lowell
June 3, 2013 at 6:28 am
WOW! That's an intense nested CTE.
It ran fine until the year 2808, so I figured you must be bumping up against a converted numeric somewhere that was "really up there" for lack of a better term. In cteFin, I changed your CAST to DECIMAL(20, 16) to DECIMAL(38, 16) and it ran successfully.
June 3, 2013 at 6:43 am
Ed Wagner (6/3/2013)
WOW! That's an intense nested CTE.It ran fine until the year 2808, so I figured you must be bumping up against a converted numeric somewhere that was "really up there" for lack of a better term. In cteFin, I changed your CAST to DECIMAL(20, 16) to DECIMAL(38, 16) and it ran successfully.
Ed thank you; i didn't even THINK it might be something that was explicitly cast;
i was digging into the details of some of the other calculated values, and figured there was an implicit conversion to into or something that i was fighting with in the FLOOR functions;
Lowell
June 3, 2013 at 6:46 am
No problem. Glad I could help you for a change.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply