Report Scripts reads Date Range wrong

V5 Question
1

Hello Guys
I have here an script for some ticket details for my country. They need that this way for some reason. but when i search it with a date range it not works correctly its showing me the wrong range. Is there something wrong? On the PC from the Person who wrote this script it works… and we have no idea atm…

{REPORT SQL DETAILS:
SELECT COUNT(*) + 1 as Quantity, (
SELECT SUM(P.Amount)
FROM Payments as P
INNER JOIN TicketEntities as TE on TE.Ticket_Id = P.TicketId
INNER JOIN Entities as E on E.Id = TE.EntityId
WHERE P.Date >= ‘{Start}’ AND P.Date < ‘{End}’
AND E.EntityTypeId = 3
) as Total, ‘{Start}’ as Period
FROM Payments as P
INNER JOIN TicketEntities as TE on TE.Ticket_Id = P.TicketId
INNER JOIN Entities as E on E.Id = TE.EntityId
WHERE P.Date >= ‘{Start}’ AND P.Date < ‘{End}’
AND E.EntityTypeId = 3
AND P.PaymentTypeId != 7
AND TE.EntityName != ‘44444401-7’
:F.Period,
F.Quantity,
F.Total
::1 [=FD(’{0}’,‘yyyyMM’)] 2 921 221 0000000 0 TEST PERSON 0 0 [=F(’{1}’,‘0’)] [=F(’{2}’,‘0’)] 2}
{REPORT SQL DETAILS:
SELECT P.Date, P.PaymentTypeId, E.CustomData, E.EntityTypeId, E.Name as RUC, T.TicketStates as TicketStates, (P.Amount - (P.Amount / 11)) as Amount, (P.Amount / 11) as Tax10, P.Amount as Total
FROM Payments as P
INNER JOIN Tickets as T on T.Id = P.TicketId
INNER JOIN TicketEntities as TE on TE.Ticket_Id = T.Id
INNER JOIN Entities as E on E.Id = TE.EntityId
WHERE P.Date >= ‘{Start}’ AND P.Date < ‘{End}’
AND E.EntityTypeId = 3
AND P.PaymentTypeId != 7
AND TE.EntityName != ‘44444401-7’
:F.RUC,
F.CustomData(Name=Razon Social)Value,
F.TicketStates(SN=FacturaID)S,
F.Date,
F.Amount,
F.Tax10,
F.Total
::2 [=’{0}’.substr(0,’{0}’.length-2)] [=’{0}’.substr(’{0}’.length-1, ‘{0}’.length)] {1}, 1 001-002-[=F(’{2}’,‘0000000’)] [=FD(’{3}’,‘dd/MM/yyyy’)] [=F(’{4}’,‘0’)] [=F(’{5}’,‘0’)] 0 0 0 [=F(’{6}’,‘0’)] 1 0 13164106}
{REPORT SQL DETAILS:
SELECT SUM(P.Amount - P.Amount / 11) as Amount, SUM(P.Amount / 11) as Tax10, SUM(P.Amount) as Total, ‘{End}’ as Date
FROM Payments as P
INNER JOIN Tickets as T on T.Id = P.TicketId
INNER JOIN TicketEntities as TE on TE.Ticket_Id = T.Id
INNER JOIN Entities as E on E.Id = TE.EntityId
WHERE P.Date >= ‘{Start}’ AND P.Date < ‘{End}’
AND E.EntityTypeId = 3
AND (P.PaymentTypeId = 7 OR TE.EntityName = ‘44444401-7’)
:F.Date,
F.Amount,
F.Tax10,
F.Total
::2 44444401 7 Importes Consolidados, 0 0 [=FD(’{0}’,‘dd/MM/yyyy’)] [=F(’{1}’,‘0’)] [=F(’{2}’,‘0’)] 0 0 0 [=F(’{3}’,‘0’)] 1 0 13164106}

2

If it works where the script was written check the format date of the system in both pcs, or change your date format to dd/MM/yyyy

3

It turns out, the issue was with the database connection settings. We needed to change the login default language to “us_english” to get the mdy format in the DB settings.

ALTER LOGIN [username] WITH DEFAULT_LANGUAGE=[us_english];

Afterwards you have to disconnect and reconnect to the DB.

You can check the settings with DBCC USEROPTIONS

This also solves: SambaPOS 5 '{Start}' '{End}' Issue in Data Exports

4

lol thank you yes this was it :smiley:

5

FYI you can format the date from within the SQL query:

SELECT FORMAT(SELECT P.Date, 'dd/MM/yyy') AS Date