Sorting a table by a range of dates

Ignition
1

I have a table which must be able to be sorted by a range of dates. I currently have this query written for the tables dataset:

SELECT A.RECEIPT_NUM,A.LOT_MTY,A.RECEIVE_TIME, B.USERNAME,A.DOCK,A.MILL_ORIGIN,A.AMOUNT_SHIPMENT,A.FREIGHT_COMPANY,A.DRIVER,A.PLATE_NUMBER, C.ORIGIN, A.HARVEST FROM RECEIVING A LEFT JOIN COMPANY_USERS B ON A.USER_NUM = B.USER_NUM LEFT JOIN ORIGIN C ON A.ORIGIN_NUM = C.ORIGIN_NUM WHERE A.RECEIVE_TIME BETWEEN 'DEC 5, 2010 4:30 PM' AND 'DEC 7, 2010 4:30 PM'

The ‘WHERE’ dates currently in the code produce a query with desired rows returned. The problem lies in the fact that I cannot use a property to dynamically sort the data. When I attempt to use a property to sort, I receive an error which states that SQL is having problems converting type varchar to type datetime.

Any solutions?

Thank you.

2

You can dynamically insert any property value into your query whether is be in the where clause or the order by clause. For example if you want to sort the data by some other property do the following:SELECT A.RECEIPT_NUM,A.LOT_MTY,A.RECEIVE_TIME, B.USERNAME,A.DOCK,A.MILL_ORIGIN,A.AMOUNT_SHIPMENT,A.FREIGHT_COMPANY,A.DRIVER,A.PLATE_NUMBER, C.ORIGIN, A.HARVEST FROM RECEIVING A LEFT JOIN COMPANY_USERS B ON A.USER_NUM = B.USER_NUM LEFT JOIN ORIGIN C ON A.ORIGIN_NUM = C.ORIGIN_NUM WHERE A.RECEIVE_TIME BETWEEN 'DEC 5, 2010 4:30 PM' AND 'DEC 7, 2010 4:30 PM' ORDER BY {Root Container.Component.property}Just replace the {Root Container.Component.property} with a reference to your property. The property should be a string that is a column or expression to sort on.

If you want to use dates in your where clause do the following:SELECT A.RECEIPT_NUM,A.LOT_MTY,A.RECEIVE_TIME, B.USERNAME,A.DOCK,A.MILL_ORIGIN,A.AMOUNT_SHIPMENT,A.FREIGHT_COMPANY,A.DRIVER,A.PLATE_NUMBER, C.ORIGIN, A.HARVEST FROM RECEIVING A LEFT JOIN COMPANY_USERS B ON A.USER_NUM = B.USER_NUM LEFT JOIN ORIGIN C ON A.ORIGIN_NUM = C.ORIGIN_NUM WHERE A.RECEIVE_TIME BETWEEN '{Root Container.Component.property}' AND '{Root Container.Component.property2}'Again replace it with your property reference. The only thing here is that you want the property to be the same format the query expects which is Dec 5, 2010 4:30 PM for example. You can use dynamic properties and expressions to format dates to match that criteria. I hope this helps.