Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
2179 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
52326 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  
 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.09 seconds. Powered By: Snitz Forums 2000