Date between not working, returns empty result

[Mike-the-one]

I am having problems to use date in between. My order table has orderDateTime field, I would like to search the orders between two dates, but it just returns empty result.

Here is the code

predicateDate = builder.between(root.get("orderDateTime"), d1, d2);

I return the predicateDate in the Specification class.

[darrachequesne]

Hi! Could you set the log level of org.hibernate.SQL to DEBUG, to see the generated query? What is the type of your field orderDateTime?

[darrachequesne]

I think this is the same issue as https://github.com/darrachequesne/spring-data-jpa-datatables/issues/94

Please see my comment: https://github.com/darrachequesne/spring-data-jpa-datatables/issues/94#issuecomment-441827285

[Mike-the-one]

Thanks @darrachequesne .

I already emptied the field. Here is my code:

class SalesOrderAdditionalSpecification implements Specification<SalesOrder> {

        private static final long serialVersionUID = -5299972584411179584L;
        private DataTablesInput input;

        public SalesOrderAdditionalSpecification(DataTablesInput input) {
            this.input = input;
        }

        @Override
        public Predicate toPredicate(Root<SalesOrder> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
            // get the product name which is column 9
            Column col = input.getColumns().get(9);
            String val = col.getSearch().getValue();

            Predicate predicateProductName = null;
            Predicate predicate = null;
            if (val != null && !val.trim().isEmpty()) {
                Join<SalesOrder, OrderItem> orderItemsJoin = root.join("orderItems");
                Join<OrderItem, Product> productJoin = orderItemsJoin.join("product");

                predicateProductName = builder.like(productJoin.<String>get("productName"), "%" + val + "%");
                predicate = predicateProductName;
            }

            // get the product sku which is column 4
            col = input.getColumns().get(4);
            val = col.getSearch().getValue();
            Predicate predicateProductSku = null;

            if (val != null && !val.trim().isEmpty()) {
                Join<SalesOrder, OrderItem> orderItemsJoin = root.join("orderItems");
                Join<OrderItem, Product> productJoin = orderItemsJoin.join("product");

                predicateProductSku = builder.like(productJoin.<String>get("sku"), "%" + val + "%");
                if (predicate != null)
                    predicate = builder.and(predicate, predicateProductSku);
                else
                    predicate = predicateProductSku;
            }

            // date range
            col = input.getColumns().get(2);
            val = col.getSearch().getValue();
            col.getSearch().setValue("");
            Predicate predicateDate = null;

            if (val != null && !val.trim().isEmpty()) {
                SimpleDateFormat formatter = new SimpleDateFormat("MM/dd/yyyy");
                int index = val.indexOf("-");

                String start = val.substring(0, index).trim();
                String end = val.substring(index + 1).trim();

                if (start.isEmpty() == false && end.isEmpty() == false) {
                    try {
                        Date d1 = formatter.parse(start);
                        Date d2 = formatter.parse(end);

                        predicateDate = builder.between(root.<Date>get("orderDateTime"), d1, d2);

                    } catch (Exception e) {

                    }
                }

                if (predicate != null)
                    predicate = builder.and(predicate, predicateDate);
                else
                    predicate = predicateDate;
            }

            query.distinct(true);

            return predicate;

        }
    }

[Mike-the-one]

The field is DATE. I am trying the debug option now

[Mike-the-one]

this is the query:

[DEBUG] 2018-11-27 03:07:57.394 [http-nio-0.0.0.0-8000-exec-7] SQL - select distinct salesorder0_.orders_id as orders_i1_11_0_, channel2_.chann_id as chann_id1_10_1_, salesorder0_.orders_buyer_addr1 as orders_b2_11_0_, salesorder0_.orders_buyer_addr2 as orders_b3_11_0_, salesorder0_.orders_buyer_id as orders_b4_11_0_, salesorder0_.orders_chann_fk as orders_25_11_0_, salesorder0_.orders_buyer_city as orders_b5_11_0_, salesorder0_.orders_buyer_company as orders_b6_11_0_, salesorder0_.orders_buyer_country as orders_b7_11_0_, salesorder0_.orders_currency as orders_c8_11_0_, salesorder0_.orders_buyer_email as orders_b9_11_0_, salesorder0_.orders_ems_status as orders_10_11_0_, salesorder0_.orders_marked_shipped as orders_11_11_0_, salesorder0_.orders_buyer_name as orders_12_11_0_, salesorder0_.orders_order_datetime as orders_13_11_0_, salesorder0_.orders_orderid as orders_14_11_0_, salesorder0_.orders_buyer_phone as orders_15_11_0_, salesorder0_.orders_total_shipping_charges as orders_16_11_0_, salesorder0_.orders_shipping_method as orders_17_11_0_, salesorder0_.orders_buyer_state as orders_18_11_0_, salesorder0_.orders_status as orders_19_11_0_, salesorder0_.orders_tax_amount as orders_20_11_0_, salesorder0_.orders_total_amount as orders_21_11_0_, salesorder0_.orders_total_shipments as orders_22_11_0_, salesorder0_.orders_txid as orders_23_11_0_, salesorder0_.orders_uacc_fk as orders_26_11_0_, salesorder0_.orders_buyer_zip as orders_24_11_0_, channel2_.chann_active as chann_ac2_10_1_, channel2_.chann_autosync as chann_au3_10_1_, channel2_.chann_autosync_msg as chann_au4_10_1_, channel2_.chann_create_date as chann_cr5_10_1_, channel2_.chann_customer_update as chann_cu6_10_1_, channel2_.chann_data as chann_da7_10_1_, channel2_.chann_is_product_fetched as chann_is8_10_1_, channel2_.chann_logo_image as chann_lo9_10_1_, channel2_.chann_name as chann_n10_10_1_, channel2_.chann_packing_slip_message as chann_p11_10_1_, channel2_.chann_rate_provider as chann_r12_10_1_, channel2_.chann_saddr_fk as chann_s21_10_1_, channel2_.chann_store_update as chann_s13_10_1_, channel2_.chann_last_finish_sync_datetime as chann_l14_10_1_, channel2_.chann_sync_frequency_in_minutes as chann_s15_10_1_, channel2_.chann_last_start_sync_datetime as chann_l16_10_1_, channel2_.chann_sync_state as chann_s17_10_1_, channel2_.chann_token_expire_date as chann_t18_10_1_, channel2_.chann_type as chann_t19_10_1_, channel2_.chann_url as chann_u20_10_1_, channel2_.chann_uacc_fk as chann_u22_10_1_ from ems_sales_orders salesorder0_ left outer join ems_sales_channels channel1_ on salesorder0_.orders_chann_fk=channel1_.chann_id left outer join ems_sales_channels channel2_ on salesorder0_.orders_chann_fk=channel2_.chann_id where (lower(cast(salesorder0_.orders_order_datetime as char)) like ? escape ?) and salesorder0_.orders_uacc_fk=? order by salesorder0_.orders_orderid desc limit ?

the query doesn't look right... the way I am doing is not correct?

[Mike-the-one]

Finally, I found the problem... yes it is the same issue as above, I cleared the search field but it is in the new class, the calling method still has the value there... that was the problem.

Thanks!