SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 SQL Overflow error
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vmchase
Starting Member

5 Posts

Posted - 10/10/2012 :  10:45:30  Show Profile  Reply with Quote
I get this error message when trying to run this query.
Adding a value to a 'datetime' column caused an overflow.
I know that in the dateadd part, the oqm.evalexp has the max value of 99999. I just don't know how to fix this.

SELECT
company_users.EmpID, company_users.cu_fname, company_users.cu_lname, oqt.id_tasks, oqt.TaskCode, oqt.TaskName, oqm.id_eval, oqm.methodName, CompanyUserEvaluation.evaluationDate,
oqm.EvalExp AS evaluation_exp,
DATEADD(M, oqm.Evalexp, CompanyUserEvaluation.evaluationDate) AS evaluation_expdate,
oqat.Evalexp AS task_exp,
CompanyUserEvaluation.id_users,
CompanyUserEvaluation.id_eval AS companyusereval_ideval, CompanyUserEvaluation.id_version, CompanyUserEvaluation.Score,
CompanyUserEvaluation.PassFail, CompanyUserEvaluation.ID_Users_Evaluator
FROM OQMethods AS oqm RIGHT OUTER JOIN
CompanyUserEvaluation INNER JOIN
company_users ON CompanyUserEvaluation.id_users = company_users.id_users ON oqm.id_eval = CompanyUserEvaluation.id_eval LEFT OUTER JOIN
OperatorJobSiteTasks AS ojss INNER JOIN
CompanyJobsAssignment AS CJA1 ON CJA1.ID_CJ = ojss.id_cj AND (CJA1.CJA_EndDate >= GETDATE() OR
CJA1.CJA_EndDate IS NULL) INNER JOIN
CompanyJobs AS cj ON cj.ID_CJ = ojss.id_cj AND cj.CJ_Active = 1 RIGHT OUTER JOIN
CompanyGroupTasks RIGHT OUTER JOIN
OQTasks AS oqt ON CompanyGroupTasks.ID_Tasks = oqt.id_tasks LEFT OUTER JOIN
OQAllTasks AS oqat INNER JOIN
OQTypes AS oqty ON oqty.id_oqt = oqat.id_oqt AND oqty.Active = 1 ON oqt.id_tasks = oqat.id_tasks ON ojss.id_tasks = oqt.id_tasks ON
oqm.id_oqm = oqat.id_oqm AND oqm.Active = 1
WHERE (company_users.ID_CP = 995)
group by company_users.EmpID, company_users.cu_fname, company_users.cu_lname, oqt.id_tasks, oqt.TaskCode, oqt.TaskName, oqm.id_eval, oqm.methodName, CompanyUserEvaluation.evaluationDate,oqm.EvalExp,oqat.Evalexp,
CompanyUserEvaluation.id_users,
CompanyUserEvaluation.id_eval, CompanyUserEvaluation.id_version, CompanyUserEvaluation.Score,
CompanyUserEvaluation.PassFail, CompanyUserEvaluation.ID_Users_Evaluator

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2039 Posts

Posted - 10/10/2012 :  11:14:42  Show Profile  Visit jackv's Homepage  Reply with Quote
The date argument cannot be incremented to a value outside the range of its data type, Could you display the date and the date part that is causing the overlow

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/11/2012 :  00:30:10  Show Profile  Reply with Quote
if 99999 is a default value make sure you apply logic to change it to valid value using case when

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000