We're a community of 1.1M IT Pros here for help, advice, solutions, professional growth and fun. Join us!
1,080,620 Members — Technology Publication meets Social Media
Username:
Password:
Lost login information?
Start New Discussion Reply to this Discussion

Arithmetic overflow error converting expression to data type datetime

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
25 Minutes
Discussion Span
5 Months Ago
Last Updated
2
Views
antman89
Newbie Poster
14 posts since May 2010
Reputation Points: 10
Solved Threads: 0
Skill Endorsements: 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)

antman89
Newbie Poster
14 posts since May 2010
Reputation Points: 10
Solved Threads: 0
Skill Endorsements: 0

This article has been dead for over three months: Start a new discussion instead

Post: Markdown Syntax: Formatting Help
 
You
 
© 2013 DaniWeb® LLC
Page generated in 0.2950 seconds using 2.66MB