Procedure to check annual date

I'm trying to create a procedure to verify annual dates. The date is stored one time in the BD, but in the calendar on my winform it shows annually(I use annually bolded dates in C#). So what I want to do with the query, is to check is the month and the day are like the date stored in the table, but does not work. This is my query:

SELECT IdCalendar,
        Description,
        DateCalendar,
        Annualy
FROM Calendar
WHERE
(DATEPART(MONTH,DateCalendar) like DATEPART(MONTH,@DateCalendar)) AND
(DATEPART(DAY,DateCalendar) like DATEPART(DAY,@DateCalendar))

And for example, my stored DateCalendar is '2015-12-04', and I my paramenter @DateCalendar is '2016-12-04'. Any idea about how to do a better query?

EDIT

The query does not have any error or warning. Just returns 0 rows. And my DateCalendar is stored as DateTime.

The SP:

CREATE PROC [dbo].[usp_app_Calendar_Search]
@DateCalendar DATETIME,
@Result     SMALLINT OUTPUT,
@Message      VARCHAR(1000) OUTPUT

AS
BEGIN
    DECLARE @vResult SMALLINT, @vMessage VARCHAR(1000)
 SELECT  @vResult = 0, @vMessage = ''

 BEGIN TRY
 IF EXISTS (SELECT * FROM Calendar WHERE DateCalendar = @DateCalendar)
 BEGIN
        IF(@DateCalendar = 0) SET @DateCalendar = NULL
SELECT IdCalendar,
        Description,
        DateCalendar,
        Annualy
FROM Calendar
WHERE
(DATEPART(MONTH,DateCalendar) like DATEPART(MONTH,@DateCalendar)) AND
(DATEPART(DAY,DateCalendar) like DATEPART(DAY,@DateCalendar))
SET @vResult = 1
        SET @vMessage = 'Done'
    END
    ELSE BEGIN

        SET @vResult = 0
        SET @vMessage  = 'Error.'

    END
END TRY
BEGIN CATCH

    SET @vResult = -1
    SET @vMessage = 'Error: ' + ERROR_MESSAGE() + ' Line: ' + CAST(ERROR_LINE() AS VARCHAR)

END CATCH

SELECT @Result = @vResult, @Message = @vMessage
END

Thanks in advance.

Answers


The problem is with IF EXISTS (SELECT * FROM Calendar WHERE DateCalendar = @DateCalendar). I rewrite your SP:

CREATE PROC [dbo].[usp_app_Calendar_Search]
   @DateCalendar DATETIME,
   @Result       SMALLINT OUTPUT,
   @Message      VARCHAR(1000) OUTPUT
AS
BEGIN
DECLARE @vResult SMALLINT = 0
        ,@vMessage VARCHAR(1000) = '';

IF(@DateCalendar = 0) SET @DateCalendar = NULL;

BEGIN TRY   
  SELECT IdCalendar,
        Description,
        DateCalendar,
        Annualy
  FROM Calendar
  WHERE DATEPART(MONTH,DateCalendar) = DATEPART(MONTH,@DateCalendar)
    AND DATEPART(DAY,DateCalendar) = DATEPART(DAY,@DateCalendar);

  IF @@ROWCOUNT > 0
    SELECT @vResult = 1, @vMessage = 'Done'
  ELSE 
    SELECT @vResult = 0, @vMessage  = 'Error.';
END TRY
BEGIN CATCH
    SET @vResult = -1
    SET @vMessage = 'Error: ' + ERROR_MESSAGE() + ' Line: ' 
                    + CAST(ERROR_LINE() AS VARCHAR)
END CATCH

SELECT @Result = @vResult, @Message = @vMessage;

END

EDIT:

Now the WHERE condition is not-SARGable so it means that query optimizer will skip index on DateCalendar column (if exists any).

You can use computed columns like @Tom Page suggested in comment:

ALTER TABLE Calendar ADD MonthCalendar AS DATEPART(MONTH,DateCalendar);
ALTER TABLE Calendar ADD DayCalendar AS DATEPART(day,DateCalendar);

/*Create Index on Calculated Columns for Month and day*/
 CREATE INDEX IX_Calendar_Month_Day ON Calendar(MonthCalendar , DayCalendar); 

/*Use Computed Column Index in W*/ 
DECLARE @DateCalendar datetime = '2015-12-25';

SELECT IdCalendar, Description, DateCalendar, Annualy 
FROM Calendar 
WHERE MonthCalendar = DATEPART(MONTH,@DateCalendar) 
  AND DayCalendar   = DATEPART(DAY,@DateCalenda); 

Need Your Help

Progressive loop through pairs of increasing integers

arrays

Suppose one wanted to search for pairs of integers x and y a that satisfy some equation, such as (off the top of my head) 7 x^2 + x y - 3 y^2 = 5

How to save WPF UI state?

c# .net wpf settings tabcontrol

I have a TabControl and under it I have several elements like TreeView and DataGrid. When I expand the tree and resize data grid columns, if I then tab to another tab and come back, the entire UI s...