The EntitySpaces Community

Share and learn about the EntitySpaces Architecture.

Welcome to The EntitySpaces Community Sign in | Join | Help

Home

Forums

Photos

The EntitySpaces Community » EntitySpaces API » Dynamic Query API » Re: Querying with Date Values

Querying with Date Values

Last post 07-13-2008, 11:51 PM by David.Parsons. 1 replies.


	Sort Posts: Previous Next

07-13-2008, 9:54 PM 10227

Buda56
Joined on 02-24-2007
Adelaide Australia
Posts 34

Querying with Date Values

Hi,

I am having issues trying to query an SQL database using dates as the input value, the records exist in the database fro that date but Entityspaces is returning no values.

I am querying to determine if the record exisits and if it does then load the record and update it, otherwise add a new record. Unfortunately using dates returns no recvords.

JobsOpenCollection JColl = new JobsOpenCollection();

JobsOpen Jent = new JobsOpen();

if (myDate.DayOfWeek == DayOfWeek.Monday)

JColl.Query.Where(JColl.Query.Date.Between(myDate.AddDays(-3), myDate.AddDays(-3)));

else

JColl.Query.Where(JColl.Query.Date.Between(myDate.AddDays(-1), myDate.AddDays(-1)));

JColl.Query.Load();

if (JColl.Count > 0)

{

//Update currecnt record

if (myDate.DayOfWeek == DayOfWeek.Monday)

Jent.Query.Where(Jent.Query.Date.Between(myDate.AddDays(-3), myDate.AddDays(-3)));

else

Jent.Query.Where(Jent.Query.Date.Between(myDate.AddDays(-1), myDate.AddDays(-1)));

Jent.Query.Load();

if (myDate.DayOfWeek == DayOfWeek.Monday)

{

//Don't update open figures, only closed

if (ChClosed > Jent.ChClosed)

Jent.ChClosed = ChClosed;

if (CaClosed > Jent.CaClosed)

Jent.CaClosed = CaClosed;

if (TskClosed > Jent.TskClosed)

Jent.TskClosed = TskClosed;

}

else

{

if (ChOpen > Jent.ChOpen)

Jent.ChOpen = ChOpen;

if (CaOpen > Jent.CaOpen)

Jent.CaOpen = CaOpen;

if (TskOpen > Jent.TskOpen)

Jent.TskOpen = TskOpen;

if (ChClosed > Jent.ChClosed)

Jent.ChClosed = ChClosed;

if (CaClosed > Jent.CaClosed)

Jent.CaClosed = CaClosed;

if (TskClosed > Jent.TskClosed)

Jent.TskClosed = TskClosed;

}

else

{

//Add new record

Jent.AddNew();

if (myDate.DayOfWeek == DayOfWeek.Monday)

Jent.Date = myDate.AddDays(-3);

else

Jent.Date = myDate.AddDays(-1);

Jent.ChOpen = ChOpen;

Jent.CaOpen = CaOpen;

Jent.TskOpen = TskOpen;

Jent.ChClosed = ChClosed;

Jent.CaClosed = CaClosed;

Jent.TskClosed = TskClosed;

}

Jent.Save();

I have found a work around for the minute by using the "Between" Operator, is there a better way to do this???

Regards...

Peter Confused

Filed under: DynamicQuery API

Report abuse

07-13-2008, 11:51 PM 10229 in reply to 10227

David.Parsons
Joined on 01-14-2007
Posts 1,420

Re: Querying with Date Values

Both .NET and SQL Server store DateTime. If all you are interested in is the date, then EntitySpaces has a Date() SubOperator for DynamicQuery, and, depending on what is stored in myDate, you may want to get just the Date portion of that as well.

Code:

DateTime myDate = Convert.ToDateTime("1948-12-09 11:32:59.123");

EmployeesCollection collection = new EmployeesCollection();

collection.Query.Where(collection.Query.BirthDate.Date() ==
    myDate.Date.AddDays(-1));
collection.Query.Load();

David Neal Parsons
www.entityspaces.net

Report abuse