| Author |
 Topic  |
|
|
ntn104
Posting Yak Master
162 Posts |
 Posted - 07/25/2008 : 13:45:42
|
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
47069 Posts |
Posted - 07/25/2008 : 14:25:29
|
| whats the datatype of revname? |
 |
|
|
ntn104
Posting Yak Master
162 Posts |
Posted - 07/25/2008 : 14:39:00
|
quote:
Originally posted by visakh16
whats the datatype of revname?
revname = varchar(50)(data example: Callback: 7/3/2008)
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 07/25/2008 : 14:47:42
|
| are you sure dates will always come in mm/dd/yyyy format after callback in revname? |
|
|
|
ntn104
Posting Yak Master
162 Posts |
Posted - 07/25/2008 : 14:55:50
|
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 |
|
|
| |
Topic |
|
cast function
