1,105,263 Community Members

Arithmetic overflow error converting expression to data type datetime

Member Avatar
antman89
Newbie Poster
14 posts since May 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

select a.EID, b.ShopName, a.TxnMonth,a.TxnYear,a.TxnDate, a.TxnAmount,a.TxnDay,a.DateCreated, a.SubmissionType
from [UA-DCM]..DCM_FTPDailyData as a Inner Join [UA-DCM]..MMS_ECR as b on a.EID = b.EID
where a.TxnDay between DateAdd(day,DateDiff(day,0,GETDATE() - 14),0) AND DateAdd(day,DateDiff(day,0,GETDATE()),0) and
a.SubmissionType = 'ECR' or a.SubmissionType = 'FTP' and
b.AssetID = '71'
and a.TxnAmount not like '"%'
and a.EID = '7100101001'
Order by a.EID, a.TxnMonth,a.TxnDay, a.SubmissionType;

Hi all,
From the code above, I am trying to retrive data from previous 14 days of the month I've tried many ways and always get this error
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.

May I know where does the error come from and how to fix it, thanks~

Member Avatar
antman89
Newbie Poster
14 posts since May 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

solved: by using a.TxnDay between convert(varchar(8),GETDATE() - 14,112) AND convert(varchar(8),GETDATE(),112) instead of a.TxnDay between DateAdd(day,DateDiff(day,0,GETDATE() - 14),0) AND DateAdd(day,DateDiff(day,0,GETDATE()),0)

You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article