query data between two dates with petapoco

Bjarne Fyrstenborg 1280 posts 3990 karma points MVP 7x c-trib

Oct 04, 2018 @ 12:18

Extending Umbraco and using the API

Umbraco 7

I have tried quering the data in the database - or more specific in uCommerce_PurchaseOrder, where I need to get orders within a daterange, where the column CompletedDate is a datetime allowing null values.

var sqlQuery = "SELECT * FROM uCommerce_PurchaseOrder o";
var sql = new Sql(sqlQuery);

Sql filterSql = new Sql();

var pageIndex = 0;
var pageSize = 100;

var start = new DateTime(2018, 1, 1);
var end = DateTime.Now;

Sql filterSql = new Sql();

filterSql.Where("o.CompletedDate IS NOT NULL")
         .Where("o.CompletedDate >= @start", new { start = start }, DatabaseContext.SqlSyntax)
         .Where("o.CompletedDate <= @end", new { end = end }, DatabaseContext.SqlSyntax);

sql.Append(filterSql);

// Get page of results and total count
var pagedResult = _db.Page<OrderModel>(pageIndex + 1, pageSize, sql.SQL);
int totalRecords = Convert.ToInt32(pagedResult.TotalItems);

With the .Where() clauses for start and end date it doesn't return any results, but without these it does.

I have tried with a different format as well without luck:

filterSql.Append("WHERE o.CompletedDate IS NOT NULL");
filterSql.Append("AND o.CompletedDate >= @start AND o.CompletedDate <= @end",
    new
    {
        start = start,
        end = end
    }
);

Any idea what I am missing in the queries?

/Bjarne

Copy Link

Bjarne Fyrstenborg 1280 posts 3990 karma points MVP 7x c-trib

Oct 06, 2018 @ 15:55

I found another way to solve this, but I don't know why regular SQL expression didn't seem to work with the datetime parameters.

public class MyClass
{
    private int[] _excludeOrderStatusIds = new int[] { 1, 2 };

    public void Get()
    {
        var d1 = DateTime.Now.AddMonths(-3);
        var d2 = DateTime.Now;

        var tbl = Database.PocoData.ForType(typeof(OrderDto)).TableInfo.TableName;

        var sqlOrders = new Sql().Select("uCommerce_PurchaseOrder.OrderId, uCommerce_PurchaseOrder.OrderNumber, uCommerce_PurchaseOrder.CreatedDate, uCommerce_PurchaseOrder.CompletedDate, uCommerce_PurchaseOrder.OrderStatusId, uCommerce_PurchaseOrder.CustomerId, uCommerce_Customer.FirstName, uCommerce_Customer.LastName")
                                .From<OrderDto>(DatabaseContext.SqlSyntax)
                                .InnerJoin<CustomerDto>(DatabaseContext.SqlSyntax)
                                .On<OrderDto, CustomerDto>(DatabaseContext.SqlSyntax, left => left.CustomerId, right => right.CustomerId)
                                .Where<OrderDto>(x => x.CompletedDate >= d1 && x.CompletedDate <= d2, DatabaseContext.SqlSyntax)
                                .Where("uCommerce_PurchaseOrder.OrderStatusId NOT IN (@ids)", new { ids = _excludeOrderStatusIds });

        var r = _db.Page<OrderDto>(1, 100, sqlOrders);
        var c = Convert.ToInt32(r.TotalItems);
    }
}


[TableName("uCommerce_PurchaseOrder")]
[PrimaryKey("OrderId", autoIncrement = true)]
[ExplicitColumns]
internal class OrderDto
{
    [Column("OrderId")]
    public int OrderId { get; set; }

    [Column("OrderNumber")]
    [NullSetting(NullSetting = NullSettings.Null)]
    public string OrderNumber { get; set; }

    [Column("CustomerId")]
    [ForeignKey(typeof(CustomerDto), Column="CustomerId")]
    [NullSetting(NullSetting = NullSettings.Null)]
    public int CustomerId { get; set; }

    [Column("OrderStatusId")]
    public int OrderStatusId { get; set; }

    [Column("CreatedDate")]
    public DateTime CreatedDate { get; set; }

    [Column("CompletedDate")]
    [NullSetting(NullSetting = NullSettings.Null)]
    public DateTime? CompletedDate { get; set; }

    [ResultColumn]
    public CustomerDto CustomerDto { get; set; }
}

[TableName("uCommerce_Customer")]
[PrimaryKey("CustomerId", autoIncrement = true)]
[ExplicitColumns]
internal class CustomerDto
{
    [Column("CustomerId")]
    public int CustomerId { get; set; }

    [Column("FirstName")]
    public string FirstName { get; set; }

    [Column("LastName")]
    public string LastName { get; set; }

    [Column("EmailAddress")]
    [NullSetting(NullSetting = NullSettings.Null)]
    public string EmailAddress { get; set; }

    [Column("PhoneNumber")]
    [NullSetting(NullSetting = NullSettings.Null)]
    public string PhoneNumber { get; set; }

    [Column("MobilePhoneNumber")]
    [NullSetting(NullSetting = NullSettings.Null)]
    public string MobilePhoneNumber { get; set; }

    [Column("MemberId")]
    [NullSetting(NullSetting = NullSettings.Null)]
    public string MemberId { get; set; }
}

Copy Link

Colin Wiseman 47 posts 178 karma points

Jun 14, 2020 @ 12:42

Just in case someone else queries this too, I used the standard BETWEEN clause in SQL:

.Where(@"EventsId IN (@eventsIds) AND ( (StartDAte BETWEEN @1 AND @2) OR (EndDate BETWEEN @1 AND @2) OR (StartDate <= @1 AND EndDate >= @2))",
                                new { eventsIds =  eventsIds },
                                fromDate.Date,
                                toDate.Date)); //  + 1 to make sure the we find a full day.

This finds all events that start or end within the date range and for events that span the selected date range.

Copy Link

is working on a reply...

Flag this post as spam?

Query data between two dates with PetaPoco