How to get 30 days from today including the month january

Question

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

Assuming you're going to do this in SQL, what does it have to do with C#? — Jon Skeet, Dec 06 '13 at 06:46
Convert your brith date string to a real date and use dateadd() to go 30 days ahead — juergen d, Dec 06 '13 at 06:47
Why don't you use the native datetime type, instead of storing the data in a varchar? From your few past questions, I'm feeling sorry for you to have to work with such an obviously crazy database :) Maybe you should just convince whoever leads the project that this is insane and that you need to take advantage of database features, rather than storing everything in plain strings... — Luaan, Dec 06 '13 at 06:56
@Parvathiiiii: But you're not expecting the solution to *be* in C#, are you? The code you've given is all in SQL, so I assume you're expecting a SQL query. The fact that you're calling that SQL from C# is irrelevant. — Jon Skeet, Dec 06 '13 at 07:00
@JonSkeet: i am edited my code, actually i do the above things — neel, Dec 06 '13 at 07:08
Yes, you *have* some C# code - but the question you want answering is *just* in SQL, right? You don't want to fetch everyone's records and do the filtering in C#. So the C# part is irrelevant. Additionally, your edit makes the SQL *much* harder to read by putting it all on one line. — Jon Skeet, Dec 06 '13 at 07:09
ref to http://stackoverflow.com/questions/83531/sql-select-upcoming-birthdays — lastr2d2, Dec 06 '13 at 07:22

Joe · Answer 1 · 2013-12-06T07:36:20.170

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)

How to get 30 days from today including the month january

1 Answers1