April 8, 2005 at 12:04 pm
I have two servers, one production and one development. I can run the following script on the production box without issues but it errors out of the dev side with "Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime."
Both servers run SQL 2K sp3, Windows 2k sp4. The databases on dev are restores of production. The only configuration option that is different is lightweight pooling is off on prod but is set to on on the dev box. Any thoughts?
SELECT PD1.EMPLID
, 'B'
, GETDATE()
, PD1.NAME
, PD1.FIRST_NAME
, PD1.LAST_NAME
, JB.SUPERVISOR_ID
, JB.DEPTID
, DEP.DESCR
, NULL
, ''
, NULL
, ''
, 0
, (CONVERT(CHAR(10),PD1.BIRTHDATE,121)) AS BIRTHDATE
, DATENAME (MONTH, PD1.BIRTHDATE) + ' ' + DATENAME (DAY, PD1.BIRTHDATE) AS BIRTHDAY , DATENAME (weekday, CASE WHEN DATEPART (MONTH, PD1.BIRTHDATE) < DATEPART (MONTH, GETDATE() ) THEN DATEADD (YEAR, 1, SUBSTRING (CONVERT(VARCHAR(15),PD1.BIRTHDATE,10), 1, 5) + '-' + DATENAME (YEAR, GETDATE()))
ELSE SUBSTRING (CONVERT(VARCHAR(15),PD1.BIRTHDATE,10), 1, 5) + '-' + DATENAME (YEAR, GETDATE()) END) AS BIRTHDAY_DAY
, EM.EMAIL_ADDR
, PD2.NAME
, PD2.FIRST_NAME
, 'N'
FROM PS_PERSONAL_DATA PD1
, PS_JOB JB
, PS_DEPT_TBL DEP
, PS_EMPLOYMENT EMP
, PS_EMAIL_ADDRESSES EM
, PS_PERSONAL_DATA PD2
WHERE PD1.EMPLID = JB.EMPLID
AND PD2.EMPLID = JB.SUPERVISOR_ID
AND EM.EMPLID = JB.SUPERVISOR_ID
AND EM.E_ADDR_TYPE = 'BUSN'
AND JB.EFFDT = (
SELECT MAX(JB2.EFFDT)
FROM PS_JOB JB2
WHERE JB.EMPLID = JB2.EMPLID
AND JB.EMPL_RCD = JB2.EMPL_RCD
AND JB2.EFFDT <= GETDATE())
AND JB.EFFSEQ = (
SELECT MAX(B_ES.EFFSEQ)
FROM PS_JOB B_ES
WHERE B_ES.EMPLID = JB.EMPLID
AND B_ES.EMPL_RCD = JB.EMPL_RCD
AND B_ES.EFFDT = JB.EFFDT)
AND JB.COMPANY = 'CCI'
AND JB.EMPL_STATUS IN ('A','L','P')
AND JB.EMPLID = EMP.EMPLID
AND JB.EMPL_RCD = EMP.EMPL_RCD
AND DEP.SETID = JB.SETID_DEPT
AND DEP.DEPTID = JB.DEPTID
AND DEP.EFFDT = (
SELECT MAX(DEP2.EFFDT)
FROM PS_DEPT_TBL DEP2
WHERE DEP2.SETID = DEP.SETID
AND DEP2.DEPTID = DEP.DEPTID
AND DEP2.EFFDT <= JB.EFFDT)
AND CASE WHEN DATEPART (MONTH, PD1.BIRTHDATE) < DATEPART (MONTH, GETDATE() )
THEN DATEADD (YEAR, 1, SUBSTRING (CONVERT(VARCHAR(15), PD1.BIRTHDATE,10), 1, 5) + '-' + DATENAME (YEAR, GETDATE()))
ELSE SUBSTRING (CONVERT(VARCHAR(15), PD1.BIRTHDATE,10), 1, 5) + '-' + DATENAME (YEAR, GETDATE()) END BETWEEN DATEADD (DAY, 6, GETDATE()) AND DATEADD (DAY, 13, GETDATE())
Terry
April 8, 2005 at 12:10 pm
Couple of thoughts...
Can you comment out individual lines in the select to identify exactly which line is causing the error? In dev of course, not production.
I would suspect an issue with a null value in one environment but not in the other environment. Perhaps ANSI_NULLS is set differently for this procedure in production than it is in dev?
hth
Wayne
April 8, 2005 at 12:22 pm
Read this very recent thread:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=172976
Notice that you are doing the same thing, creating a potentially ambiguous date with hyphens between date parts.
Either explicitly SET DATEFORMAT. Or use dates in the form YYYYMMDD.
April 8, 2005 at 12:23 pm
I thought about that and checked all my configurations options. Outside of the one I mentioned, they are identical.
Terry
April 8, 2005 at 12:32 pm
Did you checked REGIONAL SETTINGS on the OS and @@language on SQL Server
I don't really understand the purpose of that many conversions but you may want to try this:
DATENAME (weekday, DATEADD (YEAR,
( CASE WHEN DATEPART (MONTH, PD1.BIRTHDATE) < DATEPART (MONTH, GETDATE() )
THEN 1 ELSE 0 END), PD1.BIRTHDATE ) AS BIRTHDAY_DAY
which seems equivalent to
DATENAME (weekday, CASE WHEN DATEPART (MONTH, PD1.BIRTHDATE) < DATEPART (MONTH, GETDATE() ) THEN DATEADD (YEAR, 1, SUBSTRING (CONVERT(VARCHAR(15),PD1.BIRTHDATE,10), 1, 5) + '-' + DATENAME (YEAR, GETDATE()))
ELSE SUBSTRING (CONVERT(VARCHAR(15),PD1.BIRTHDATE,10), 1, 5) + '-' + DATENAME (YEAR, GETDATE()) END) AS BIRTHDAY_DAY
hth
note: is the data equal on both ?
* Noel
April 8, 2005 at 1:23 pm
Actually, that piece of code is returning the day of the week for their birthday this year. It works but if you have another method, I'm open to suggestions. Your code returns the day of the week of the actual birth of the individual.
Terry
April 8, 2005 at 1:44 pm
Here's the lon version to calculate the next birthday from the birthdate (I know I could've made it much shorter but I went for clarity):
Declare @BirthDate as datetime
Declare @ThisYear as datetime
Declare @NextBirthDate as datetime
set @BirthDate = '1981/03/23'
select @BirthDate
set @ThisYear = dateadd(yy, datediff(yy, @BirthDate, getdate()), @BirthDate)
select @ThisYear
if datepart(y, @BirthDate) < datepart(y, GetDate())
begin
set @NextBirthDate = dateadd(yy, 1, @ThisYear)
end
else
begin
set @NextBirthDate = @ThisYear
end
Select @NextBirthDate
Select datepart(dw, @NextBirthDate)
This is the short version to get this year's birthday' day of week regardless of if the day has passed or not.
select datepart(dw, dateadd(yy, datediff(yy, @BirthDate, getdate()), @BirthDate))
April 14, 2005 at 2:13 pm
With my code, leap year birthdates were the culprits leading to the errors. Thanks for the suggestions.
Terry
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply