Results 1 to 6 of 6

Thread: convert char to datetime datatype -Err msg

  1. #1
    Join Date
    Feb 2009
    Posts
    9

    convert char to datetime datatype -Err msg

    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

  2. #2
    Join Date
    Feb 2009
    Posts
    9
    pls suggest me as it is very urgent

  3. #3
    Join Date
    Apr 2009
    Posts
    86
    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)

  4. #4
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    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.

  5. #5
    Join Date
    Dec 2013
    Posts
    1
    Dear pls suggest me as it is very urgent............????





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

  6. #6
    Join Date
    Dec 2013
    Posts
    1
    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'.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •