Print Page | Close Window

Query for DatePart

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce Classic
Forum Discription: For .NET 2.0
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=894
Printed Date: 17-Apr-2025 at 8:16pm


Topic: Query for DatePart
Posted By: pucsoftware
Subject: Query for DatePart
Date Posted: 22-Jul-2008 at 8:19am
I need to create a query looking for all the employees who have a birthday in the current month. I have a column that represents the birthdate of an employee. How do I find all the employees with a birthdate in a current month? Since this is a date field in SQL I would use the DatePart function. I could also use this same strategy for getting all the records in a year or on a specific day. From what I've read about creating queries I could do something similar to this:
 

public static RdbQuery GetEmpoyeeDobByMonth(string sMonth) {

RdbQuery query = new RdbQuery(typeof(Employee));

query.AddClause(Employee.DOBEntityColumn, EntityQueryOp.StartsWith, sMonth);

return query;

}

Even if this would work for finding the month, it doesn't address how I could get the day or year. Where can I get more info on working with date columns in the OQL language?

Darren


Replies:
Posted By: jeffdoolittle
Date Posted: 22-Jul-2008 at 9:38am
I'm fairly certain that this will not work.  The "StartsWith" operator works with strings, but I'm assuming your "DOB" property is a DateTime.

How about this approach?

        public static EntityQuery GetByBirthMonthQuery(int month, int year) {
            EntityQuery empQuery = new EntityQuery(typeof(Employee));
            DateTime startDate = new DateTime(year, month, 1);
            DateTime endDate = startDate.AddMonths(1).AddTicks(-1);
            empQuery.AddClause(Employee.DateOfBirthEntityColumn, EntityQueryOp.Between, startDate, endDate);
            return empQuery;
        }

Posted By: pucsoftware
Date Posted: 22-Jul-2008 at 10:37am

Thanks for the suggestoin. I had thought about this, too. But I really can't use a date field because I'm looking for who has a birthday this month of this year. If I use a date field, which includes the year, then I am looking for anyone who was BORN in the current month and year. We wouldn't have any employees that ever met that critieria. :)

It looks like I will have to use a passthrough query object. Unless there is some way to have the OQL language parse out a date field by using MONTH(), DAY(), or YEAR() functions then this isn't going to work with IEntityObjects. Bummer.
 
Darren

Posted By: jeffdoolittle
Date Posted: 22-Jul-2008 at 12:09pm
ah, yes, you did in fact say "a birthday in the current month."  I should have read more carefully.

Since EntityQueries cannot work with derived properties, these are your options:

1) create a passthrough query
2) create a view that does the Month(), Day(), Year() calculations.  Something like this:

    CREATE VIEW viwEmployeeDOB
    AS
    SELECT e.EmployeeId, Month(e.DateOfBirth) AS Month, Day(e.DateOfBirth) AS Day, Year(e.DateOfBirth) AS Year
    FROM Employee e
  
and then map this as a one-to-one related entity of the Employee entity.  then you could do a sub query against this related entity's values.

3) create a stored procedure that returns employee objects
4) create a stored procedure or dynamic entity query that returns employee ids and then create an entity query that uses the "In" operator to retrieve the employees with matching id values

These options have will side-effects if you have changed DOB in the employee object but haven't persisted yet.  Checking the database value when a local save hasn't persisted may result in unexpected behavior, so you'll need to be prepared to handle this situation.

5) if you don't have a lot of employees, or if there is some other way you can limit the number of results, you could just load all the employees into memory and use a foreach loop (or LINQ).  Of course this won't scale well unless you have another way to limit the results.

Posted By: pucsoftware
Date Posted: 22-Jul-2008 at 1:27pm
thanks for the suggestion. the idea of using a view and relating it back to the employees brings up a lot of options I hadn't thought about. Thanks a lot. I'll give this a try.
 
Darren


Print Page | Close Window