SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 cast function
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ntn104
Posting Yak Master

171 Posts

Posted - 07/25/2008 :  13:45:42  Show Profile  Reply with Quote
Hello. i tried to use Cast function for my query. but I got error message saying Data Exception -data type conversion not possible. If you could help to solve this error.

select distinct a.ID_internal, a.ID_entity, a.segment,
Count(a.RevName) as Number_Callback ,
sum(case when a.letterdate > cast(substring(a.revname,11,10) as smalldatetime)
then 1 else 0 end) as Number_ResentLetter,
a.returnamt
from dba.tletter a
inner join dba.tletterNotes b on b.ID_internal=a.ID_internal
where a.revname like 'Callback%'
and a.letterdate>cast(substring(a.revname,11,10) as smalldatetime)
and a.segment in ('I')
group by a.ID_internal,a.ID_entity, a.segment, a.returnamt, a.revName, a.letterdate
having sum(returnamt) >0


Edited by - ntn104 on 07/25/2008 13:56:01

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/25/2008 :  14:25:29  Show Profile  Reply with Quote
whats the datatype of revname?
Go to Top of Page

ntn104
Posting Yak Master

171 Posts

Posted - 07/25/2008 :  14:39:00  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

whats the datatype of revname?



revname = varchar(50)(data example: Callback: 7/3/2008)

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/25/2008 :  14:47:42  Show Profile  Reply with Quote
are you sure dates will always come in mm/dd/yyyy format after callback in revname?
Go to Top of Page

ntn104
Posting Yak Master

171 Posts

Posted - 07/25/2008 :  14:55:50  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

are you sure dates will always come in mm/dd/yyyy format after callback in revname?



I am sure...since I ran table for revname column only...I can see it comes like mm/dd/yyyy

and letterdate come with the format (2008-07-14-12.00.00.000000
)

Edited by - ntn104 on 07/25/2008 14:57:16
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 1.75 seconds. Powered By: Snitz Forums 2000