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

I am executing below SQL Script that throws an arithmetic overflow error converting an expression to a datetime data type

I searched for this error in SO and google, but I cannot find a solution.

Below is my 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 changed here.

Sorry for the duplicate!

+3
source share
1 answer

This is the beginning of your script where you assign lines DATETIMEs. This implies an implicit conversion, as if RDBMS were adding CONVERT()there for you. And in your case, this does not work.

Try a more universal date and time format ...

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

All Articles