CODEWITHSUNDEEP
sqlsql-serverdatetimesql-update
thatstevedude
thatstevedude

Reputation: 195

Updating DateTime columns failed when converting date

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:enter image description here Bad: enter image description here

;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

Upvotes: 0

Views: 85

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

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;

Upvotes: 1

Related Questions