overflow error

i m getting date like this 25-Dec-2006.....now i want to convert this date to 2006-12-25...

but i m getting erorr ?

Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.

select tbl_cams_uploaddetails.amount,tbl_cams_uploaddetails.folio_no,tbl_cams_uploaddetails.Scheme_Code,tbl_cams_uploaddetails.trade_date into #SuccessBJSIP from tbl_cams_uploaddetails WITH(NOLOCK)

join tbl_cms_uploaddetails on
tbl_cams_uploaddetails.Scheme_Code=parsename(replace(substring(tbl_cms_uploaddetails.AdditionalField1, patindex('%/%',tbl_cms_uploaddetails.AdditionalField1) + 1, len(tbl_cms_uploaddetails.AdditionalField1)), '/', '.'), 4) and
tbl_cams_uploaddetails.amount=tbl_cms_uploaddetails.amount and
tbl_cams_uploaddetails.folio_no=left(tbl_cms_uploaddetails.AdditionalField1, patindex('%/%', tbl_cms_uploaddetails.AdditionalField1) - 1) and
tbl_cams_uploaddetails.trade_date=convert(datetime,parsename(replace(substring(tbl_cms_uploaddetails.AdditionalField1, patindex('%/%', tbl_cms_uploaddetails.AdditionalField1) + 1, len(tbl_cms_uploaddetails.AdditionalField1)), '/', '.'), 3) +

'-' +

parsename(replace(substring(tbl_cms_uploaddetails.AdditionalField1, patindex('%/%', tbl_cms_uploaddetails.AdditionalField1) + 1, len(tbl_cms_uploaddetails.AdditionalField1)), '/', '.'), 2),126)
where tbl_cams_uploaddetails.compare_status='Pending' and Payment_Mechanism = 'EC'
and tbl_cms_uploaddetails.compare_status='Pending' and Format_ID in (88,89)
group by tbl_cams_uploaddetails.amount,tbl_cams_uploaddetails.folio_no,tbl_cams_uploaddetails.Scheme_Code,tbl_cams_uploaddetails.trade_date
having count(tbl_cams_uploaddetails.folio_no) = 1

instead of your manual logic try to use the Convert(datetime, tbl_cms_uploaddetails.AdditionalField1)|||hi mani thanx for reply.....acyually in additionalfoeld1 i m getting string like this

1930454/IFG/25/DEC-06/15878....so after doing parsing i m getting date 25-Dec-2006......

|||

check all your rows retunrs the same value as you expected (only date)..

You can use the following expression...

Case When IsDate(parsename(replace(substring(tbl_cms_uploaddetails.AdditionalField1, patindex('%/%', tbl_cms_uploaddetails.AdditionalField1) + 1, len(tbl_cms_uploaddetails.AdditionalField1)), '/', '.'), 3) +

'-' +

parsename(replace(substring(tbl_cms_uploaddetails.AdditionalField1, patindex('%/%', tbl_cms_uploaddetails.AdditionalField1) + 1, len(tbl_cms_uploaddetails.AdditionalField1)), '/', '.'), 2)) = 1 Then

Convert(datetime, parsename(replace(substring(tbl_cms_uploaddetails.AdditionalField1, patindex('%/%', tbl_cms_uploaddetails.AdditionalField1) + 1, len(tbl_cms_uploaddetails.AdditionalField1)), '/', '.'), 3) +

'-' +

parsename(replace(substring(tbl_cms_uploaddetails.AdditionalField1, patindex('%/%', tbl_cms_uploaddetails.AdditionalField1) + 1, len(tbl_cms_uploaddetails.AdditionalField1)), '/', '.'), 2))

Else NULL END

|||

try this, you can change you convert(datetime.. to convert(smalldatetime.. and you can also change the tbl_cams_uploaddetails.trade_date so that both have the same format

convert(varchar(10),tbl_cams_uploaddetails.trade_date,101)=convert(varchar(10),convert(smalldatetime,parsename(replace(substring(tbl_cms_uploaddetails.AdditionalField1, patindex('%/%', tbl_cms_uploaddetails.AdditionalField1) + 1, len(tbl_cms_uploaddetails.AdditionalField1)), '/', '.'), 3) +

'-' +

parsename(replace(substring(tbl_cms_uploaddetails.AdditionalField1, patindex('%/%', tbl_cms_uploaddetails.AdditionalField1) + 1, len(tbl_cms_uploaddetails.AdditionalField1)), '/', '.'), 2)),101)

|||

i added this in my existing query..now it is returning 12 records......and when i run my actual query than it returns only 3 records....

why?

|||i tride yours also...getting same error message.|||

Remove the Group BY clause & validate your records ..

|||same result..3 and 12 records.|||hmm.. did you check you excel if it has dirty date values?
|||

How about rewriting the query as follow as...

select

tbl_cams_uploaddetails.amount

,tbl_cams_uploaddetails.folio_no

,tbl_cams_uploaddetails.Scheme_Code

,tbl_cams_uploaddetails.trade_date into #SuccessBJSIP

From

tbl_cams_uploaddetails WITH(NOLOCK)

join tbl_cms_uploaddetails WITH(NOLOCK) on

tbl_cams_uploaddetails.amount=tbl_cms_uploaddetails.amount

and tbl_cms_uploaddetails.AdditionalField1Like cast(tbl_cams_uploaddetails.Scheme_Code as varchar) + '/' +

tbl_cams_uploaddetails.folio_no+ '/' + Cast(day(tbl_cams_uploaddetails.trade_date) as varchar) + '/' +

Upper(Substring(Datename(MM,tbl_cams_uploaddetails.trade_date),1,3)) + '-' +

Cast(Year(tbl_cams_uploaddetails.trade_date) as varchar) + '/%'

Where

tbl_cams_uploaddetails.compare_status='Pending'

andPayment_Mechanism = 'EC'

and tbl_cms_uploaddetails.compare_status='Pending'

and Format_ID in (88,89)

group by

tbl_cams_uploaddetails.amount

,tbl_cams_uploaddetails.folio_no

,tbl_cams_uploaddetails.Scheme_Code

,tbl_cams_uploaddetails.trade_date

|||now 5 minutes passed..still i m waiting for query result...still it is executing.|||

Its bcs of the expression may violate to use the index - the expression.

The operator LIKE also may cause this issue...

'1930454/IFG/25/DEC-06/15878' here what is 15878 stands for. Are you connecting this value also on join?

|||no i m not using this value 15878