[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