DevForce Community Forum : Query for DatePart http://www.ideablade.com/forum/ This is an XML content feed of; DevForce Community Forum : DevForce Classic : Query for DatePart Thu, 11 Jun 2026 00:08:38 -700 Tue, 22 Jul 2008 13:27:43 -700 http://blogs.law.harvard.edu/tech/rss Web Wiz Forums 9.69 360 www.ideablade.com/forum/RSS_post_feed.asp?TID=894 DevForce Community Forum http://www.ideablade.com/forum/forum_images/IdeaBlade_logo_tm.png http://www.ideablade.com/forum/ Query for DatePart : thanks for the suggestion. the... http://www.ideablade.com/forum/forum_posts.asp?TID=894&PID=3242#3242 Author: pucsoftware
Subject: 894
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
]]> Tue, 22 Jul 2008 13:27:43 -700 http://www.ideablade.com/forum/forum_posts.asp?TID=894&PID=3242#3242 Query for DatePart : ah, yes, you did in fact say "a... http://www.ideablade.com/forum/forum_posts.asp?TID=894&PID=3241#3241 Author: jeffdoolittle
Subject: 894
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.




]]> Tue, 22 Jul 2008 12:09:28 -700 http://www.ideablade.com/forum/forum_posts.asp?TID=894&PID=3241#3241 Query for DatePart : Thanks for the suggestoin. I had... http://www.ideablade.com/forum/forum_posts.asp?TID=894&PID=3240#3240 Author: pucsoftware
Subject: 894
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
]]> Tue, 22 Jul 2008 10:37:40 -700 http://www.ideablade.com/forum/forum_posts.asp?TID=894&PID=3240#3240 Query for DatePart : I'm fairly certain that this... http://www.ideablade.com/forum/forum_posts.asp?TID=894&PID=3239#3239 Author: jeffdoolittle
Subject: 894
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;
        }


]]> Tue, 22 Jul 2008 09:38:43 -700 http://www.ideablade.com/forum/forum_posts.asp?TID=894&PID=3239#3239 Query for DatePart : I need to create a query looking... http://www.ideablade.com/forum/forum_posts.asp?TID=894&PID=3238#3238 Author: pucsoftware
Subject: 894
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]]> Tue, 22 Jul 2008 08:19:10 -700 http://www.ideablade.com/forum/forum_posts.asp?TID=894&PID=3238#3238