Member Avatar for antman89

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~

  • 1 Contributor
  • 1 Reply
  • 404 Views
  • 25 Minutes Discussion Span
  • Latest Post Latest Post by antman89
Member Avatar for antman89

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)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.