Quick Report - SQL Statement - where date is yesterday

Hi ​@nate.w 

Yesterday can be defined as this

ARRIVAL_DATE = TRUNC(SYSDATE) - 1

Try this 

SELECT CONTRACT, VENDOR_NO, ORDER_NO, LINE_NO, PART_NO, QTY_ARRIVED, ARRIVAL_DATE, DELIVERY_DATE, RECEIVER, OBJSTATE, PART_OWNERSHIP
FROM PURCHASE_RECEIPT_NEW
WHERE DEMAND_CODE = 'IO'
ORDER BY ARRIVAL_DATE DESC
AND ARRIVAL_DATE = TRUNC(SYSDATE) - 1

Best Regards,
Charana

Hi ​@Charana Udugama 

Using ARRIVAL_DATE = TRUNC(SYSDATE) - 1 cleared my error, so that is good. But I did not get any data when I tried running the report. Out of curiosity, I tried the query again but I used the DELIVERY_DATE field instead of the ARRIVAL_DATE field. This worked. It seems to be because these are date/time fields. All of the values in the Arrival Date field have the date and a specific time. The values in the Delivery Date field have the date followed by 12:00AM for every record. I am guessing that because the system date has 12:00AM included in it, the formula only works if the other date field in the formula also has 12:00AM. I think for this particular report, using the Delivery Date field will work fine. So I am good here. Do you have any ideas on how I could address this though if I did need the Arrival Date for some reason?

Here is an example of how you can deal with the dates that have time in them:
ARRIVAL_DATE BETWEEN TRUNC(SYSDATE) - 1 AND TRUNC(SYSDATE) - (1/24/60/60)

You could go for the easier condition to truncate the arrival date also but that will get worse performance if there is a usable index on that column (For this example it has no index but I would not make it a habit to use this more simpler syntax.):
trunc(ARRIVAL_DATE) = TRUNC(SYSDATE) - 1

Using trunc, without any more parameters, on a date truncates the time in the date back to 00:00:00 (or 12:00 AM in your format). That is why the values are not the same if you only truncate SYSDATE and not the arrival date that contains timestamps.
 

​@Tomas Ruderfelt - Thank you, that was helpful.

I have my report complete now. It looks good when I view the report in IFS. I need to schedule this report to be sent daily to a group of users. I tested sending it to myself first. The report visually looks good in Excel, but the data in the cells has extra carriage returns and spaces. Is there a way that I can remove those so the Excel report that gets sent out will not include the leading carriage returns and spaces? If I export the report into Excel after viewing it in IFS, I do not get the leading characters. But when I schedule the report, they get added in somehow. You can see below that it visually looks fine, but if somebody wanted to copy the data from C2 they would get a carriage return + two spaces in front of the order number.

I can only find an old reporting of a similar thing with initial spaces that should have been fixed in:
22R2 SU23 | 23R1 SU16 | 23R2 SU09 | 24R1 SU03

If you are on a later version I would suggest creating a ticket for it.