[SQL Server] Trouver la date comparable

-- =============================================

-- Author:        KHARROUBI Zouhaier

-- Create date: 10/12/2011

-- Description:   Trouver la date comparable

-- EXemple : SELECT dbo.usp_DateComparable('20131106')

-- =============================================

CREATE FUNCTION [dbo].[usp_DateComparable]

(

      @Date_A_Comparer AS DATETIME

)

RETURNS DATETIME

AS

BEGIN

      DECLARE @Date_A_Traiter  AS DATETIME

      DECLARE @Numero_Jour AS INT

 

      SET @Date_A_Traiter  = CAST(FLOOR(CAST( @Date_A_Comparer AS FLOAT)) AS DATETIME)

      SET @Date_A_Traiter  = DateAdd( day, -365, @Date_A_Traiter )

      SET @Numero_Jour = DatePart( weekday, @Date_A_Traiter )

      IF ( @Numero_Jour % 7 ) = 0

            SET @Numero_Jour = DatePart( weekday, @Date_A_Comparer )

      ELSE

      SET @Numero_Jour = DatePart( weekday, @Date_A_Comparer ) - @Numero_Jour

      SET @Date_A_Traiter =DateAdd(day, @Numero_Jour, @Date_A_Traiter )  

      RETURN @Date_A_Traiter

END