I am executing below SQL Script which is throwing error Arithmetic overflow error converting expression to data type datetime
I have searched for this error in SO and google but I'm not able to figure out the solution.
Below is mine Longggggggggg SQL Query
DECLARE @TimeByDay DATETIME
DECLARE @TimeByDay2 DATETIME
SET @TimeByDay = '1/1/2012'
SET @TimeByDay2 = '12/31/2014'
SELECT p1.ProjectName ,
p1.EnterpriseProjectTypeName ,
p1.[Legacy System ID] ,
p1.ProjectDescription ,
p1.ProjectOwnerName ,
p1.[Gate Approvers] ,
p1.[Team Members] ,
p1.[Project Sponsor] ,
p1.[Idea Project Type] ,
p1.[Compliance Project] ,
p1.[Product Category] ,
p1.[Relevant Functional Area] ,
p1.Initiative ,
p1.[OPEX Transformation] ,
p1.[Commodity Type] ,
p1.[LSS Training Project] ,
p1.[Project Status Comments] ,
p1.[Project Status] ,
p1.Phase ,
p1.[Innovation Matrix Class] ,
p1.[Legacy Project Manager] ,
p1.[AOP Line Number] ,
p1.[AOP Project Name] ,
p1.[Approval Level] ,
p1.Assumptions ,
p1.[Belt Name] ,
p1.[Benefits Impact] ,
p1.[Bus Impact or Bus Y] ,
p1.[Business Sponsor] ,
p1.[Code Name] ,
p1.[Complexity Risk Level] ,
p1.[Complexity Risk Score] ,
p1.[Critical to Quality] ,
p1.Deliverables ,
p1.[Development Risk] ,
p1.[Estimated Duration] ,
p1.[Financial Uncertainty] ,
p1.[Financial Rep] ,
p1.[External Dependencies] ,
p1.[Financial Risk Level] ,
p1.[Financial Risk Score] ,
p1.[Idea Functional Area] ,
p1.[Idea Beneftting Location] ,
p1.[Idea Reviewer Comments] ,
p1.[Idea Comments] ,
p1.[Idea Sector] ,
p1.[Idea Submitters Email] ,
p1.[Idea Submission Date] ,
p1.[Idea Submitter Phone] ,
p1.[Idea Reviewer Name] ,
p1.[Idea Description_] ,
p1.[Idea Est Revenue Benefit] ,
p1.[Idea Name] ,
p1.[Idea Product Category] ,
p1.[Idea Est Savings Benefit] ,
p1.[Idea Number] ,
p1.[Idea Review Date] ,
p1.[Idea Est Balance Sheet Benefit] ,
p1.[Idea Est Investment] ,
p1.[Idea Submitter Email] ,
p1.[IR Project Type] ,
p1.[Idea Sources] ,
p1.[Idea Submitter] ,
p1.[Implementation Risk] ,
p1.[Technology Capabilities Risk] ,
p1.[Market Uncertainty] ,
p1.[Legal Regulatory Risk] ,
p1.[Investment Risk] ,
p1.Mentor ,
p1.[Out of Scope] ,
p1.[Project Scope] ,
p1.[Project Start Date] ,
p1.[Project Finish Date] ,
p1.[Mandated by whom?] ,
p1.[Reason for Action] ,
p1.[Project Champion] ,
p1.[Process Owner] ,
p1.[Target State (Box 3 of A3)] ,
p1.[Problem Statement] ,
p1.[Project Interaction] ,
p1.[Strategic Goal/Business Case] ,
p1.[Sending Leader(s)] ,
p1.[Receiving Leader(s)] ,
p1.[Process Definition] ,
p1.[Things That Can Be Improved] ,
p1.[Idea Estimated Duration] ,
p1.[ST IT Cost Center] ,
p1.[Top Project] ,
p1.[Reason For Action (Box 1 of A3)] ,
p1.[Initial State] ,
p1.[Impact of not meeting mandate] ,
p1.[Primary Proponent Department] ,
p1.[Project Financial Benefit Status] ,
p1.[Project Resource Status] ,
p1.[Project Schedule Status] ,
p1.[Project Investment Status] ,
p1.[Project Success Factors] ,
p1.[Support Required] ,
p1.[Project Milestones] ,
p1.[Project Constraints] ,
p1.[Other Funding Source] ,
p1.[Project Cost Center] ,
p1.[Initial State (Box 2 of A3)] ,
p1.CostCenterName ,
p1.CostCategoryName ,
YEAR(p1.TimeByDay) AS Year ,
MONTH(p1.TimeByDay) AS Month ,
p1.Actuals ,
p1.Budget ,
p1.Forecast ,
p1.PhaseName ,
p1.ProjectUID ,
CONVERT(VARCHAR(20), p1.ProjectCreatedDate, 101) AS ProjectCreatedDate ,
CONVERT(VARCHAR(20), p1.ProjectModifiedDate, 101) AS ProjectModifiedDate
FROM [IR.ST.EPM Custom Flds and Financial Data] p1
--WHERE p1.TimeByDay >= CAST(@TimeByDay AS DATETIME)
-- AND p1.TimeByDay <= CAST(@TimeByDay2 AS DATETIME)
WHERE p1.timebyday >= @TimeByDay
AND p1.timebyday <=@TimeByDay2
AND p1.StageEntryDate IS NOT NULL
AND p1.StageCompletionDate IS NOT NULL
I'm not sure what needs to be change here.
Sorry for duplicate!
datetimedatatype. otherwise, there must be some data which is out of date range.[IR.ST.EPM Custom Flds and Financial Data]a view or a table? If it is a view does this run asSELECT *(to elimiate the error not originating in the code you have posted. Your next check should be does theSELECT *run with theWHEREclause, if it does then you know your error is in theWHEREclause. If this works, then start adding the select columns in 1 at a time until you get the error. This will enable you to isolate the column that is causing the error. This will allow you to post more information that could help to solve the problem.