Arithmetic overflow error converting expression to data type datetime in SQL Server 2008

Question

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!

Answer 1

It's the start of your script where you assign strings to DATETIMEs. This involves an implicit conversion, as if the RDBMS adds the CONVERT() in there for you. And in your case it's failing.

Try a more universal date-time format instead...

SET @TimeByDay  = '20120101'
SET @TimeByDay2 = '20141231'