CODEWITHSUNDEEP
c#sql
neel
neel

Reputation: 5293

How to get 30 days from today including the month january

My sql table contains date of birth of many people.dates are in mm/dd/yyyy format. I want select the persons details whose birth day in next 30days. And i use the following query for that,

 SELECT Mem_FirstNA, Mem_LastNA, Mem_DOB FROM MemberDetails WHERE 
 ltrim(str(year(GETDATE()))) + -' + ltrim(str(month(Mem_DOB))) + '-' + 
 ltrim(str(day(Mem_DOB))) >= getdate() - 1 AND 
ltrim(str(year(GETDATE()))) + '-' + ltrim(str(month(Mem_DOB))) + '-' + 
 ltrim(str(day(Mem_DOB))) <= getdate() + 30

And full code is 

public List<MemberData> GetThisMonthBirthday()
    {
        List<MemberData> MD = new List<MemberData>();
        using (SqlConnection con = new SqlConnection(Config.ConnectionString))
        {
           using (SqlCommand cmd = new SqlCommand("SELECT Mem_FirstNA, Mem_LastNA, Mem_DOB FROM MemberDetails WHERE ltrim(str(year(GETDATE()))) + '-' + ltrim(str(month(Mem_DOB))) + '-' +  ltrim(str(day(Mem_DOB))) >= getdate() - 1 AND ltrim(str(year(GETDATE()))) + '-' + ltrim(str(month(Mem_DOB))) + '-' +  ltrim(str(day(Mem_DOB))) <= getdate() + 30", con))

           {
                try
                {
                    con.Open();
                    SqlDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                     MemberData mb = new MemberData();
                     mb.Mem_NA = (string)reader["Mem_FirstNA"];
                     mb.Mem_LastNA =(string)reader["Mem_LastNA"];
                     mb.Mem_DOB = (Convert.ToDateTime(reader["Mem_DOB"]));
                     MD.Add(mb);
                   }

                }
                catch (Exception e) { throw e; }
                finally { if (con.State == System.Data.ConnectionState.Open) con.Close(); }
                return MD;
            }
        }

The problem is that this check only till December 31, if the persons birthday in 01/01/1987 , the query is not selected that details. Please help me to solve this problem. Thank you

Upvotes: 0

Views: 210

Answers (1)

to StackOverflow
to StackOverflow

Reputation: 124726

There are lots of ways to do this, you need conditional logic depending on whether or not you are within 30 days of the end of the year. If you're using SQL Server, I'd wrap the logic in a function to make it more readable, e.g.:

CREATE FUNCTION [dbo].[IsBirthdayInRange]
(
    @Birthday DATETIME,
    @StartDate DATETIME,
    @EndDate DATETIME
)
RETURNS BIT
AS
BEGIN
    DECLARE @StartMonthDay INT
    DECLARE @EndMonthDay INT
    DECLARE @BirthdayMonthDay INT

    SET @StartMonthDay = MONTH(@StartDate) * 100 + DAY(@StartDate)
    SET @EndMonthDay = MONTH(@EndDate) * 100 + DAY(@EndDate)
    SET @BirthdayMonthDay = MONTH(@Birthday) * 100 + DAY(@Birthday)

    IF YEAR(@StartDate) <> YEAR(@EndDate) 
    BEGIN
        IF @BirthdayMonthDay >= @StartMonthDay OR @BirthdayMonthDay <= @EndMonthDay 
        BEGIN
            RETURN 1
        END
    END
    ELSE
        BEGIN
        IF @BirthdayMonthDay >= @StartMonthDay AND @BirthdayMonthDay <= @EndMonthDay 
        BEGIN
            RETURN 1
        END
    END
    RETURN 0
END

You can then use it as:

...
WHERE IsBirthdayInRange(Mem_DOB, GETDATE(), GETDATE() + 30)

Upvotes: 1

Related Questions