CODEWITHSUNDEEP
sqlsql-serverdatestored-procedures
Mike
Mike

Reputation: 853

SQL Server: compare dates by only matching month and day

I have a stored procedure that fetches records based on dates matching a date input which works fine so far. Both the dates in the table and my input date are formatted as datetime.

Instead of comparing the full dates I would like to change this so that it only compares month and day so that it works with any year for the input.

Example: A date in the table is saved as 2013-04-30 and my input date is 2014-04-30. What I want is that the stored procedure still returns that record independent of the year as long as month and day match.

My stored procedure: 

ALTER PROCEDURE [dbo].[FetchDays]
    @inputDate datetime
AS
BEGIN
    SET NOCOUNT ON;
    SELECT      dateID,
                dayDT,
                countries,
                regions
    FROM        DaysDT
    WHERE       dayDT = @inputDate
    FOR XML PATH('daysFixed'), ELEMENTS, TYPE, ROOT('root')

END

Many thanks for any help with this, Mike.

Upvotes: 6

Views: 24715

Answers (3)

Bhargav Variya
Bhargav Variya

Reputation: 775

I have been needed this type of requirement in past. I have used this solution and it worked for me as I wanted.

SELECT * FROM yourTableName
WHERE DATE_FORMAT(yourColumnName, '%m-%d') = DATE_FORMAT('yourValue', '%m-%d') and yourOtherCondition;

Upvotes: 2

Ryx5
Ryx5

Reputation: 1366

You can do something like this ;)

ALTER PROCEDURE [dbo].[FetchDays]
    @inputDate datetime
AS
BEGIN
    SET NOCOUNT ON;
    SELECT      dateID,
                dayDT,
                countries,
                regions
    FROM        DaysDT
    WHERE       
        DAY(dayDT) = DAY(@inputDate) --Extract and compare day
        AND MONTH(dayDT) = MONTH(@inputDate) --Extract and compare month
    FOR XML PATH('daysFixed'), ELEMENTS, TYPE, ROOT('root')

END

Upvotes: 11

shree.pat18
shree.pat18

Reputation: 21757

Try this:

WHERE datepart(day, dayDT) = datepart(day,@inputDate)
AND datepart(month, dayDT) = datepart(month,@inputDate)

This will compare the date and month parts of your overall date, without checking the year.

Upvotes: 3

Related Questions