1.11M Members

Arithmetic overflow error converting expression to data type datetime

 
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~

 
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 six months: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article