Updating DateTime columns failed when converting date

Question

Help. I have some records that have been inserted differently into a DATETIME column. When my sproc executes I receive this error: "Conversion failed when converting date and/or time from character string.". Is there any way to convert the bad datetime into the normal default DATETIME format?

Good: Bad:

;with t1 as
    (
        select
            s1.SheetID as SheetID,
            s1.RowNUmber as RowNumber, 
            (select max(RowNumber) from PRP_SmartSheetA where SheetID=s1.SheetID and RowNumber<s1.RowNumber) as LastRowNumber
        from
            PRP_SmartSheetA s1
    )
    update
        s1
    set
        s1.PRP_PreviousRowFinishDate= s2.Finish
    from
        t1
            inner join PRP_SmartSheetA s1
                on t1.SheetID=s1.SheetID
                and t1.RowNUmber=s1.RowNumber
            inner join PRP_SmartSheetA s2
                on t1.SheetID=s2.SheetID
                and t1.LastRowNumber=s2.RowNumber

A datatime column has no format. Datetime data are stored in a binary structure. Formatting of the value for display purposes is done by the presentation layer. Perhaps `PRP_PreviousRowFinishDate` or `Finish` is varchar instead of datetime. — Dan Guzman, Sep 12 '15 at 01:29
[read this.](http://stackoverflow.com/questions/30032915/how-to-cast-the-datetime-to-time/30033028#30033028) — Zohar Peled, Sep 13 '15 at 07:41
Hi, it would be nice to upvote and/or mark as accepted if my answer could help you, thx! — Shnugo, Sep 27 '15 at 15:00

score 1 · Answer 1 · answered Sep 11 '15 at 23:12

Your "bad" dates seem to be in a string format invalid for your systems culture.

You can convert them like this:

DECLARE @DateAsVarchar VARCHAR(20)='2015-09-16';
DECLARE @ConvertedDate AS DATETIME=CONVERT(DATETIME,REPLACE(@DateAsVarchar,'-',''),112);
SELECT @ConvertedDate;

Updating DateTime columns failed when converting date

1 Answers1