I am trying to create a data export with a REPORT SQL DETAILS tag. Somehow the '{Start}' and '{End}' selectors work correctly on my test installation, but not on my production machine. In testing all data shows up perfectly fine. In production I do not get any results (for the same date settings). If I remove the WHERE clause for date it shows all data. So I guess the issue is related to this date clause.
Here is a stripped down version of my SQL code:
{REPORT SQL DETAILS:
SELECT P.Date
FROM Payments as P
WHERE P.Date >= '{Start}' AND P.Date < '{End}'
AND P.PaymentTypeId != 7
:F.Date
::{0}}
I also tried to use CAST('{Start}' AS date) to be sure the date is parsed correctly.
Could the OS locale affect this? In my testing environment I have the system in English, but in production the OS is in Spanish.
Thanks for your suggestions. Changing the region and date format in Windows did not help. I also tried the solution from @Jesse using WHERE DATE > '{Start} AND DATE < '{End}' without success.
Any other idea, what could be the issue?
The data and SambaPOS config on both environments should be the same. I used a DB backup from my production machine in my test environment. Therefore I guess it is something with Windows. Does SambaPOS rely on external Windows settings?
I’ve not done a report using sql and dates within samba.
I would maybe try setting the date format in windows to yyyy-mm-dd as that’s the format used in sql.
I have to do this to keep my sanity in my PMS integration rather than mess about reformatting formatting dates but don’t know if this will neserceraly be your issue.
[Test:1]
{REPORT SQL DETAILS:
SELECT P.Date
FROM Payments as P
WHERE
(CAST(P.Date AS DATE) >= '{Start}') AND (CAST(P.Date AS DATE) <= '{End}')
AND P.PaymentTypeId != 7
:F.Date::{0}
}
I did, yes. The result did not change.
Nevertheless I got it working but I do not know how. I did some tests changing the dates, clicked refresh in the Report, exported the data and nothing changed. Afterwards I changed the dates again, clicked save and saved the .xps file. Then I deleted the .xps file, changed the dates again, clicked refresh and exported data export. Suddenly everything was working as expected.
I have no idea why it works now. Maybe someone has an explanation for me