convert char to datetime datatype -Err msg

[sowmyav]

Hi,
I want to convert the datatype from char to datetime in th sql server table.
But i am not able to convert .
I am getting the following error.


Msg 241, Level 16, State 1, Line 2
Conversion failed when converting datetime from character string.

But I need to convert the same....as i have done teh same long back.
It would be of very great help, if you suggest me the solution for the same.

Thanks in adv,
Sowmya

[sowmyav]

pls suggest me as it is very urgent

[SDas]

Without an example of the data I can't be sure if you need more or not but try:

CAST('2009-04-21' AS DATETIME)

[skhanal]

Sounds like the character string is not really date. You can check which is not date and try to fix it then convert.

select cdate, isdate(cdate)
from yourtable

anything with 0 in isdate column is invalid.

[mikky7]

Dear pls suggest me as it is very urgent............????





================================
Smile PlzZZzzz…(Usman Malik)…!!!
ion post

[van]

alter function anf
(
@mdvn int,
@fdate datetime,
@tdate datetime
)
returns table
as
begin
declare @vdate date
declare @fedddate date
declare @tedddate date
declare @tvdate date
set @vdate=dateadd(dd,-91,@fdate)
set @tvdate=dateadd(dd,36,@tdate)
set @fedddate=dateadd(dd,20,@fdate)
set @tedddate=dateadd(dd,310,@tdate)
declare @REGDT int
declare @ANC int
declare @TT1 int
declare @TT2 int
declare @IFA int
declare @BP int
declare @HB int
end
declare funcurz cursor static for
select dvn_cd,phc_cd,hsc_cd,sum(REGDT) as Regdt,SUM(ANC) as ANC,SUM(TT1) as TT1, SUM(TT2)as TT2,
SUM(IFA) as IFA, SUM(BP) as BP, SUM(HB) as HB from
(
select dvn_cd,phc_cd,hsc_cd,
case when ANEDD IS null then 0 else 1 end as REGDT,
case when visit_no=3 and ANEDD between @vdate and @tvdate then 1 else 0 end as ANC,
case when TTB=1 and ANEDD between @fdate and @tdate then 1 else 0 end as TT1,
case when TTB>2 and ANEDD between @fdate and @tdate then 1 else 0 end as TT2,
case when IFA=100 and ANEDD between @fdate and @tdate then 1 else 0 end as IFA,
case when BP>='140/90' and ANEDD between @fdate and @tdate then 1 else 0 end as BP,
case when HB<11 and ANEDD between @fdate and @tdate then 1 else 0 end as HB
from ANVisits3 a where DVN_CD=@mdvn and ANEDD between @fedddate and @tedddate
)a group by dvn_cd,phc_cd,hsc_cd
open funcurz
begin
fetch next from funcurz into @mdvn,@REGDT,@ANC,@TT1,@TT2,@IFA,@BP,@HB
while @@FETCH_STATUS=0
begin
print 'DVN : '+ convert(varchar(20),@mdvn)+',FDT :'+convert(varchar(20),@fdate)+
',TDT :'+convert(varchar(20),@tdate)+',RGDT:'+convert(va rchar(20),@REGDT)+
',ANC :'+convert(varchar(20),@ANC)+',TT1 :'+convert(varchar(20),@TT1)+
',TT2 :'+convert(varchar(20),@TT2)+',IFA :'+convert(varchar(20),@IFA)+
',BP :'+convert(varchar(20),@BP)+',HB:'+ convert(varchar(20),@HB)
fetch next from funcurz into @mdvn,@REGDT,@ANC,@TT1,@TT2,@IFA,@BP,@HB
END
end
return
close funcurz
deallocate funcurz

-----------------------
Pls anyone findout the solution for this pgm....
i got the error lik dis..
Incorrect syntax near 'BEGIN'.