SQL Between day and month (No Year) related with Reaccurrance

Question

Excuse me, This is my app:

The db design:

So, the rule is when reaccurrance, so I am not see the year just the month and day. So far this is my query, but when I try, it still failed when start date is at december and end date is at january next year...

ALTER PROC [dbo].[uspPFS_HolidayListWithDateOrWithUnionReaccurrance] (
    @p_sKeyword VARCHAR(255) = NULL
    ,@p_dtStartHolidayDateFrom DATETIME = NULL
    ,@p_dtEndHolidayDateTo DATETIME = NULL
    )
AS
BEGIN
    SET NOCOUNT ON;

    SELECT [COM_HOLIDAY_ID]
        ,[RECURRANCE]
        ,[START_HOLIDAY_DATE]
        ,[END_HOLIDAY_DATE]
        ,[HOLIDAY_NAME]
        ,[HOLIDAY_DESC]
        ,[CREATE_BY_USER_ID]
        ,[UPDATE_BY_USER_ID]
        ,[CREATE_DATE]
        ,[UPDATE_DATE]
    FROM [PFS_HOLIDAY] WITH (NOLOCK)
    WHERE (
            @p_sKeyword IS NULL
            OR [HOLIDAY_NAME] LIKE '%' + @p_sKeyword + '%'
            OR [HOLIDAY_DESC] LIKE '%' + @p_sKeyword + '%'
            )
        AND (
            @p_dtStartHolidayDateFrom IS NULL
            OR @p_dtStartHolidayDateFrom BETWEEN [START_HOLIDAY_DATE]
                AND [END_HOLIDAY_DATE]
            )

    UNION

    SELECT [COM_HOLIDAY_ID]
        ,[RECURRANCE]
        ,[START_HOLIDAY_DATE]
        ,[END_HOLIDAY_DATE]
        ,[HOLIDAY_NAME]
        ,[HOLIDAY_DESC]
        ,[CREATE_BY_USER_ID]
        ,[UPDATE_BY_USER_ID]
        ,[CREATE_DATE]
        ,[UPDATE_DATE]
    FROM [PFS_HOLIDAY] WITH (NOLOCK)
    WHERE (
            @p_sKeyword IS NULL
            OR [HOLIDAY_NAME] LIKE '%' + @p_sKeyword + '%'
            OR [HOLIDAY_DESC] LIKE '%' + @p_sKeyword + '%'
            )
        AND (
            @p_dtEndHolidayDateTo IS NULL
            OR @p_dtEndHolidayDateTo BETWEEN [START_HOLIDAY_DATE]
                AND [END_HOLIDAY_DATE]
            )

    UNION

    SELECT [COM_HOLIDAY_ID]
        ,[RECURRANCE]
        ,[START_HOLIDAY_DATE]
        ,[END_HOLIDAY_DATE]
        ,[HOLIDAY_NAME]
        ,[HOLIDAY_DESC]
        ,[CREATE_BY_USER_ID]
        ,[UPDATE_BY_USER_ID]
        ,[CREATE_DATE]
        ,[UPDATE_DATE]
    FROM [PFS_HOLIDAY] WITH (NOLOCK)
    WHERE (
            @p_sKeyword IS NULL
            OR [HOLIDAY_NAME] LIKE '%' + @p_sKeyword + '%'
            OR [HOLIDAY_DESC] LIKE '%' + @p_sKeyword + '%'
            )
        AND (
            @p_dtStartHolidayDateFrom IS NULL
            OR [START_HOLIDAY_DATE] >= @p_dtStartHolidayDateFrom
            )
        AND (
            @p_dtEndHolidayDateTo IS NULL
            OR [END_HOLIDAY_DATE] < DATEADD(DAY, 1, @p_dtEndHolidayDateTo)
            )

    UNION

    SELECT [COM_HOLIDAY_ID]
        ,[RECURRANCE]
        ,[START_HOLIDAY_DATE]
        ,[END_HOLIDAY_DATE]
        ,[HOLIDAY_NAME]
        ,[HOLIDAY_DESC]
        ,[CREATE_BY_USER_ID]
        ,[UPDATE_BY_USER_ID]
        ,[CREATE_DATE]
        ,[UPDATE_DATE]
    FROM [PFS_HOLIDAY] WITH (NOLOCK)
    WHERE RECURRANCE = 1
        AND (
            @p_dtStartHolidayDateFrom IS NULL
            OR @p_dtStartHolidayDateFrom BETWEEN CAST(CONVERT(VARCHAR(10), DATEPART(yyyy, @p_dtStartHolidayDateFrom)) + '-' + CONVERT(VARCHAR(10), DATEPART(mm, START_HOLIDAY_DATE)) + '-' + CONVERT(VARCHAR(10), DATEPART(dd, START_HOLIDAY_DATE)) AS DATETIME)
                AND DATEADD(DAY, 1, CAST(CONVERT(VARCHAR(10), DATEPART(yyyy, @p_dtStartHolidayDateFrom)) + '-' + CONVERT(VARCHAR(10), DATEPART(mm, END_HOLIDAY_DATE)) + '-' + CONVERT(VARCHAR(10), DATEPART(dd, END_HOLIDAY_DATE)) AS DATETIME))
            )
        AND (
            @p_sKeyword IS NULL
            OR [HOLIDAY_NAME] LIKE '%' + @p_sKeyword + '%'
            OR [HOLIDAY_DESC] LIKE '%' + @p_sKeyword + '%'
            )

    UNION

    SELECT [COM_HOLIDAY_ID]
        ,[RECURRANCE]
        ,[START_HOLIDAY_DATE]
        ,[END_HOLIDAY_DATE]
        ,[HOLIDAY_NAME]
        ,[HOLIDAY_DESC]
        ,[CREATE_BY_USER_ID]
        ,[UPDATE_BY_USER_ID]
        ,[CREATE_DATE]
        ,[UPDATE_DATE]
    FROM [PFS_HOLIDAY] WITH (NOLOCK)
    WHERE RECURRANCE = 1
        AND (
            @p_dtEndHolidayDateTo IS NULL
            OR @p_dtEndHolidayDateTo BETWEEN CAST(CONVERT(VARCHAR(10), DATEPART(yyyy, @p_dtEndHolidayDateTo)) + '-' + CONVERT(VARCHAR(10), DATEPART(mm, START_HOLIDAY_DATE)) + '-' + CONVERT(VARCHAR(10), DATEPART(dd, START_HOLIDAY_DATE)) AS DATETIME)
                AND DATEADD(DAY, 1, CAST(CONVERT(VARCHAR(10), DATEPART(yyyy, @p_dtEndHolidayDateTo)) + '-' + CONVERT(VARCHAR(10), DATEPART(mm, END_HOLIDAY_DATE)) + '-' + CONVERT(VARCHAR(10), DATEPART(dd, END_HOLIDAY_DATE)) AS DATETIME))
            )
        AND (
            @p_sKeyword IS NULL
            OR [HOLIDAY_NAME] LIKE '%' + @p_sKeyword + '%'
            OR [HOLIDAY_DESC] LIKE '%' + @p_sKeyword + '%'
            )

    UNION

    SELECT [COM_HOLIDAY_ID]
        ,[RECURRANCE]
        ,[START_HOLIDAY_DATE]
        ,[END_HOLIDAY_DATE]
        ,[HOLIDAY_NAME]
        ,[HOLIDAY_DESC]
        ,[CREATE_BY_USER_ID]
        ,[UPDATE_BY_USER_ID]
        ,[CREATE_DATE]
        ,[UPDATE_DATE]
    FROM [PFS_HOLIDAY] WITH (NOLOCK)
    WHERE RECURRANCE = 1
        AND (
            @p_sKeyword IS NULL
            OR [HOLIDAY_NAME] LIKE '%' + @p_sKeyword + '%'
            OR [HOLIDAY_DESC] LIKE '%' + @p_sKeyword + '%'
            )
        AND (
            @p_dtStartHolidayDateFrom IS NULL
            OR CAST(CONVERT(VARCHAR(10), DATEPART(yyyy, @p_dtStartHolidayDateFrom)) + '-' + CONVERT(VARCHAR(10), DATEPART(mm, START_HOLIDAY_DATE)) + '-' + CONVERT(VARCHAR(10), DATEPART(dd, START_HOLIDAY_DATE)) AS DATETIME) >= @p_dtStartHolidayDateFrom
            )
        AND (
            @p_dtEndHolidayDateTo IS NULL
            OR CAST(CONVERT(VARCHAR(10), DATEPART(yyyy, @p_dtEndHolidayDateTo)) + '-' + CONVERT(VARCHAR(10), DATEPART(mm, END_HOLIDAY_DATE)) + '-' + CONVERT(VARCHAR(10), DATEPART(dd, END_HOLIDAY_DATE)) AS DATETIME) < DATEADD(DAY, 1, @p_dtEndHolidayDateTo)
            )

on this part, should filter reaccurrance regardless the year, just between day and month. So, if I input 01 Jan 2017, com_holiday_id = 2, "Natal dan tahun baru" should appears, but it was not since my date converter still bugging, when try to filter end of years until some days next year, when I try to get the result of sql it showing : 2017-12-25 until 2017-01-02. Maybe there is way to solve this maybe sql server built in function?

  SELECT [COM_HOLIDAY_ID]
        ,[RECURRANCE]
        ,[START_HOLIDAY_DATE]
        ,[END_HOLIDAY_DATE]
        ,[HOLIDAY_NAME]
        ,[HOLIDAY_DESC]
        ,[CREATE_BY_USER_ID]
        ,[UPDATE_BY_USER_ID]
        ,[CREATE_DATE]
        ,[UPDATE_DATE]
    FROM [PFS_HOLIDAY] WITH (NOLOCK)
    WHERE RECURRANCE = 1
        AND (
            @p_sKeyword IS NULL
            OR [HOLIDAY_NAME] LIKE '%' + @p_sKeyword + '%'
            OR [HOLIDAY_DESC] LIKE '%' + @p_sKeyword + '%'
            )
        AND (
            @p_dtStartHolidayDateFrom IS NULL
            OR CAST(CONVERT(VARCHAR(10), DATEPART(yyyy, @p_dtStartHolidayDateFrom)) + '-' + CONVERT(VARCHAR(10), DATEPART(mm, START_HOLIDAY_DATE)) + '-' + CONVERT(VARCHAR(10), DATEPART(dd, START_HOLIDAY_DATE)) AS DATETIME) >= @p_dtStartHolidayDateFrom
            )
        AND (
            @p_dtEndHolidayDateTo IS NULL
            OR CAST(CONVERT(VARCHAR(10), DATEPART(yyyy, @p_dtEndHolidayDateTo)) + '-' + CONVERT(VARCHAR(10), DATEPART(mm, END_HOLIDAY_DATE)) + '-' + CONVERT(VARCHAR(10), DATEPART(dd, END_HOLIDAY_DATE)) AS DATETIME) < DATEADD(DAY, 1, @p_dtEndHolidayDateTo)
            )

Is there another way to solve this case? Something like between just day and month?

Answer 1

Try this:

select *
from [PFS_HOLIDAY] with (NOLOCK)
where
    ([HOLIDAY_NAME] like '%' + isnull(@p_sKeyword,'') + '%' OR [HOLIDAY_DESC] LIKE '%' + isnull(@p_sKeyword,'') + '%') and
    (
        (
            -- compare start date
            case
                when RECURRANCE = 1 then
                    cast(year(@p_dtStartHolidayDateFrom) as nvarchar(4)) + cast(month(START_HOLIDAY_DATE) as nvarchar(2)) + cast(day(START_HOLIDAY_DATE) as nvarchar(2))
                else
                    START_HOLIDAY_DATE                  
            end >= @p_dtStartHolidayDateFrom
        )
        and
        (
            -- compare end date
            case
                when RECURRANCE = 1 then
                    cast(year(@p_dtEndHolidayDateTo) as nvarchar(4)) + cast(month(END_HOLIDAY_DATE) as nvarchar(2)) + cast(day(END_HOLIDAY_DATE) as nvarchar(2))
                else
                    END_HOLIDAY_DATE                    
            end <= @p_dtEndHolidayDateTo
        )
    )