SambaPOS 5 '{Start}' '{End}' Issue in Data Exports

Hi SambaPOS Community,

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.

I am using SambaPOS 5.2.22

It looks like your missing an end curly bracket.

Woops, thanks for the call. I accidentally removed it while formatting this post.
Unfortunately this does not solve my issue :frowning:

I would guess date format from local may be an issue and perhaps try changing temporarily to check if that is the case.

Here is a portion of a script I use.

WHERE 1=1
AND [PaymentData]!=''
AND DATE > '{Start}'
AND DATE < '{End}'
ORDER BY jsonData.[pdDateTime]

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?

Yes it gets date from windows.

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.

May be you cast the Date wrong?

Did you try that so?

[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}
}

Did you try that so?

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 :slight_smile:

But thanks a lot for your suggestions.

This was solved in Report Scripts reads Date Range wrong