CODEWITHSUNDEEP
c#sql
RayLoveless
RayLoveless

Reputation: 21068

c#, build sql to select a user given an age range

I have a talent table holding all my users with a column holding their birthdays. what would be the best way Talent within a specified age range. Here's what I have, but is seems to be off by a couple days. is there a better way?

// BUILD SQL FROM FORM DATA
sqlString += "SELECT * from Talent";

if (minAge != 0 || maxAge != 120)
{
     // The age criteria has been change, filter by age.

     // select all talents that have birthdays between the following 2 dates.
     DateTime startDate = (DateTime.Now - new TimeSpan((maxAge * 365), 0, 0, 0));  // maxAge * 365 = totalDays
     DateTime endDate = (DateTime.Now - new TimeSpan((minAge * 365), 0, 0, 0));
     sqlString += " WHERE Birthdate BETWEEN '" + startDate.ToString() + "' AND '" + endDate.ToString() + "'";
}

Upvotes: 0

Views: 518

Answers (5)

Will Marcouiller
Will Marcouiller

Reputation: 24132

Assuming you're using SQL Server...

using (var connection = new SqlConnection(connString))
    using (var command = connection.CreateCommand()) {

        string tsql = @"
            select *
                from Talent
                where DATEDIFF(YEAR, BirthDay, GETDATE()) BETWEEN @minAge AND @maxAge";

        command.CommandText = tsql;
        command.CommandType = CommandType.Text;

        int minAge = 1;
        int maxAge = 120;

        SqlParameter minAgeParam = command.CreateParameter();
        minAgeParam.Direction = ParameterDirection.Input;
        minAgeParam.DbType = SqlDbType.TinyInt;
        minAgeParam.ParameterName = "@minAge";
        minAgeParam.Value = minAge;

        SqlParameter maxAgeParam = command.CreateParameter();
        maxAgeParam.Direction = ParameterDirection.Input;
        maxAgeParam.DbType = SqlDbType.TinyInt;
        maxAgeParam.ParameterName = "@maxAge";
        maxAgeParam.Value = maxAge;

        // Just unsure here whether I must add the parameters to the command,
        // or if they are already part of it since I used the 
        // SqlCommand.CreateParameter() method. 
        // Been too long since I haven't done any ADO.NET
        command.Parameters.Add(minAgeParam);
        command.Parameters.Add(maxAgeParam);

        connection.Open();

        SqlDataReader reader = null;

        try {
            reader = command.ExecuteReader();
            // Process your records here...
        } finally {
            connection.Close()
            command.Dispose();
            connection.Dispose();
            if (reader != null) {
                reader.Dispose();
            }
        }
    }

Where @minAge and @maxAge are your age parameters.

You may also tell the DATEDIFF TSQL function to consider the difference in days, in month, in hours, in minutes, in seconds, etc. Hence, you will have to convert your parameters value accordingly.

Upvotes: 1

mpulcini
mpulcini

Reputation: 121

For what it's worth - your original solution is off by a few days because it is using 365 instead of accounting for leap years.

Upvotes: 0

Cheng Chen
Cheng Chen

Reputation: 43523

Why don't you use DateTime.AddYears method.

DateTime startDate = DateTime.Now.AddYears(-maxAge);

insated of 

DateTime startDate = (DateTime.Now - new TimeSpan((maxAge * 365), 0, 0, 0));

Another thing is: Please don't use + operator between strings to build a sql query, use StringBuilder instead.

Upvotes: 0

Bruno Brant
Bruno Brant

Reputation: 8564

The problem might be related to DateTime.Now, which considers time as well as date. Try replacing it with a DateTime.Today.

Upvotes: 0

Brad Christie
Brad Christie

Reputation: 101614

Personally, I've found using startDate.ToString("yy-MM-dd 00:00:00.000") and endDate.ToString("yy-MM-dd 23:59:59.000") works best (note the ,000 on the end date range. For some reason, in my experience, sql is off (probably due to some kind of rounding error) when it comes to ranges.

As an aside, you can use static methods from the TimeSpan object for time calculations. e.g. TimeSpan.FromDays(...)

Upvotes: 0

Related Questions