Hello, I have a table called Member in my database that I use to store information about users including the date of birth for each person. I have a search function in my application that is supposed to look through the Member table and spit out a list of users with a user-inputted age range (min and max ages). Now, I could have stored ages instead of dob in the table, but I would think that's bad practice since age changes and would need continuous recomputing (which is db intensive) as opposed to dob which stays the same.
So what I'm thinking is getting the min and max user inputted ages, convert them to dob values (of type DateTime) in the application. And then, to query the db and return a list of all users in the Member whose dob falls in between those two dates (is a BETWEEN even possible with DateTime values?).
How is the best way to go about this? There are many sites out there that return users with user specified age ranges. Is there a best way to do this that's the least taxing on the db?
TIA.
You can use the DateDiff function to get there age
http://msdn2.microsoft.com/en-us/library/ms189794.aspx
|||
Thanks for the reply. But what about my database table implementation... is that the right way to go? i'm assuming having an age field is poor practice and I should just have a dob field.
And for the next step, how would I determine the list of users who are say, between the ages of 40 and 55? The DateDiff requires that I know 2 dates ahead of time and that would force me to do a table scan (would be time costly if the table gets huge), computing all dob values in the table with the current time to see if a user falls between 40 and 55. Is there a way that I can convert age into a DateTime? If so, I could compute the DateTimes for the min and max values that are inputted by the user. And then I can maybe do like a "WHERE dob BETWEEN min AND max"...... something to that effect to retrieve the necessary rows. BETWEEN works for integers, not sure for DateTime.
Sorry for the stupid questions, I'm a relative newbie. TIA.
|||
Anybody?
Ok, I think this is the way to go. Just have a DOB field in the Member table. Having an age field is bad practice, I'd think. Then, when a user wants to search for all members within a specified age range (a min and a max age value in years), I would translate the age values into a DateTime format (current time minus age to derive DOB) on the application side. Then I would run an SQL query along the lines of "SELECT * FROM Member WHERE dobDateMin BETWEEN dobDateMax". This should return all members whose DOBs fall within the age range.
The question is, how do I calculate a DOB in DateTime given an age in years? Pseudo-speaking, it would be like.... take the current system time, minus the age in years, and derive the date in DateTime format, so I can feed it into the SQL statement.
Am I on the right track with this? How do all the sites that allow searches for age ranges do this?
TIA
|||
Yes, you would use the dateadd function for this.
SELECT *
FROM MyTable
WHERE DoB Between DATEADD(year,0-@.MaxAge,floor(cast(getutcdate() as float))) AND DATEADD(year,0-@.MinAge,floor(cast(getutcdate() as float)+1))
Assuming that DoB is a datetime field in the MyTable table, and there is an index on that field, it will do an index scan range to return your results. GetUTCDate() may not give you the date you are looking for, adjust as necessary.
|||
Thank you - that seems like what I'm looking for and I appreciate the example so I could visualize how that could be used here. I would have liked to do all of the calculating on the app side to avoid database overhead, but I'll take what I can get. Yes, the DOB field is already indexed to avoid a costly table scan. So do you think other sites use this approach when trying to return results of users within an age range? I'll try it out and see if it works. Thanks.
***** BTW, just out of curiosity, why did you cast the third parameter into float and then floor it? Is this so that you can get pinpoint accuracy at the date level? If so, since the third field accepted a datetime, don't we have to recast it again into a datetime (or smalldatetime)? Like ....
WHERE dob BETWEEN DATEADD(year,0-@.MaxAge,CAST(FLOOR(CAST(getdate() AS float)) AS datetime)) AND DATEADD(year,0-@.MinAge,CAST(FLOOR(CAST(getdate() AS float)+1) AS datetime))
|||
It's a quick hack. Datetimes when converted to floats are in a format that the date portion is stored in a whole numbers, and the time portion as a fraction of a number. By flooring it, we lose the time portion (or more accurately, we get the very smallest time for that date -- exactly midnight).
As for casting it back again, SQL Server will do that as an implicit conversion (It doesn't need to be stated, but you can if you want).
As for where to calculate the dates, I would do it on the server side. It's not really a hard calculation to make for SQL Server, and it abstracts the implementation of the age search to SQL Server. Meaning, if at a later time, we decide that we need to (for whatever reason, performance, scalability, integration) change how we do the search the application code doesn't need to change. We could for example, add an age column to the member table, run a batch process at night that goes through and updates all the user's age, and change the query (assuming it's in a stored procedure, or depends on a view) and the application(s) wouldn't even notice except for possibly better performance. All depends on your environment really. If you aren't using stored procedures then it won't really buy you very much though.
