Data truncated: Incorrect TimeStamp

ChatterBox
ChatterBox
in Archive

Hi,

 

I'm using Mysql to insert 12 new rows with an interval of 28 days I've tried date_add function. But, an error appears which reads as " Incorrect datetime value :Time stamp truncated" . insert ignore wouldn't iterate for 12 times. Kindly, help me solve this issue:  

 

create procedure MonthlyDeferredRevenue()
BEGIN
Set @v_max1 = 13;
Set @v_counter1 = 1;
Set @v_days =28;

start transaction;

while @v_counter1 < @v_max1 do

Set @v_days = @v_days * @v_counter1;

INSERT INTO cb_sf_masterdata_interim (OpportunityCloseDate)

SELECT  DATE_ADD(OpportunityCloseDate, Interval @v_days day)

FROM cb_sf_masterdata_temp where adjustedrevenue >= 2500 ;


SET @v_counter1 = @v_counter1 + 1;
end while;

commit;
end;

Comments

  • Tomo
    Tomo Contributor

    型違いでエラーになっているようです。
    OpportunityCloseDate は、TIMESTAMP で、入れようとしているのは DATETIME ではないでしょうか。
    適切な型に変換すると成功すると思います。

     

    Domoは型表示が曖昧なので、分かりづらいですね。

     

    It seems that there is an error due to type mismatch.

    OpportunityCloseDate is TIMESTAMP, and is not DATETIME trying to enter. I think that converting it to the proper type will succeed.

     

    Domo is ambiguous in type display, so it is hard to understand.

  • ChatterBox
    ChatterBox

    I changed the datatype of the OpportunityCloseDate column, removed nulls by assigning now() as default; also tried making both of them TimeStamp. This is the error: 

    Data truncated: Incorrect datetime value: '2068-03-12 00:00:00' for column 'OpportunityCloseDate'. 

    Could you think of any other possibility please ?

     

     

Categories