thanks you so much EUGENE for your precious help its done now for all the table who had similar message error

but still have another error with a dimension_time generate with this script 🙁 if i revolve it i can move to the next step )

CREATE PROCEDURE [dbo].[PS_DIM_TEMPS] /*(@Date_debut varchar(10), @Date_fin varchar(10))*/

AS

truncate table DWProd.dbo.DIM_TEMPS

declare @D_DEBUT datetime

declare @D_FIN datetime

declare @Date_Complete datetime

declare @Annee VARCHAR(4)

declare @Annee2 int

declare @campagne VARCHAR(5)

declare @trimestre VARCHAR(6)

declare @quinzaine varchar(1)

declare @mois VARCHAR(6)

declare @ordre_mois VARCHAR(6)

declare @mois_complet VARCHAR(12)

declare @semaine_annee VARCHAR(7)

declare @jour VARCHAR(2)

declare @jour_semaine VARCHAR(9)

declare@anneeS VARCHAR(4)

BEGIN

SET @D_DEBUT = cast('01-10-2009' as datetime)

SET @D_FIN = cast('30-09-2013' as datetime)

/*set @D_DEBUT = @Date_debut

set @D_FIN = @Date_fin*/

WHILE @D_DEBUT<@D_FIN BEGIN

set @Date_Complete= @D_DEBUT

set @Annee =datename(YY,@D_DEBUT)

set @trimestre =datepart(Q,@D_DEBUT)

set @mois = datepart(MM,@D_DEBUT)

set @ordre_mois = case @mois

when '10' then 1

when '11' then 2

when '12' then 3

when '1' then 4

when '2' then 5

when '3' then 6

when '4' then 7

when '5' then 8

when '6' then 9

when '7' then 10

when '8' then 11

when '9' then 12

end

set @mois_complet =datename(MONTH,@D_DEBUT)

set @semaine_annee = datepart(WW,@D_DEBUT)

set @jour = datepart(DD,@D_DEBUT)

set @jour_semaine = datepart(DW,@D_DEBUT)

if (@jour<16)

set @quinzaine =1

else

set @quinzaine =2

set @anneeS=@annee+1

if (@jour>0 and @mois>9) set @campagne=substring(@annee,3,2)+'/'+substring(@anneeS,3,2)

if (@jour>0 and @mois>9) set @annee2=@anneeS

INSERT INTO DIM_TEMPS(DATEID,ANNEE,TRIMESTRE,

MOIS,LIB_MOIS,SEMAINE,

JOUR,JOUR_SEMAINE,

QUINZAINE,CAMPAGNE,ANNEE_CAMP,ORDRE_MOIS)

VALUES(@Date_Complete,@Annee,CONVERT(int,@trimestre),

CONVERT(int,@mois),@mois_complet,CONVERT(int,@semaine_annee),

CONVERT(int,@jour),CONVERT(int,@jour_semaine),

CONVERT(int,@quinzaine),@campagne,@annee2,CONVERT(int,@ordre_mois))

set @D_DEBUT =dateadd(d,1,@D_DEBUT)

END

Update DIM_TEMPS set DECADE=case When day(DATEID) between 1 and 10 then convert(varchar(2),month(DATEID))+'-D1'

When day(DATEID) between 11 and 20 then convert(varchar(2),month(DATEID))+'-D2'

When day(DATEID) between 21 and 31 then convert(varchar(2),month(DATEID))+'-D3' END

END

GO

and this is the message error :

Msg 242, Level 16, State 3, Procedure PS_DIM_TEMPS, Line 29

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

(0 row(s) affected)

(1 row(s) affected)