Hi,
It’s possible to paste the SQL code from the default report so I can copy paste the things I would like to my Custom Report?
I think it could be usefull for those have little (or 0 idea like me) from SQL
I just would like to show the underlined items
Thank you and sorry in advance for silly questions,
Regards
This code:
[Resumen Ventas:3, 1]
Ventas|R$ [=F(TN('{ACCOUNT CREDIT TOTAL:Sales}'),'#,#0.00')]
Taxa Serviço|R$ [=F(TN('{ACCOUNT CREDIT TOTAL:Taxa Serviço}'),'#,#0.00')]
Descuentos|R$ [=F(TN('{ACCOUNT DEBIT TOTAL:Discount}'),'#,#0.00')]
Total|R$ [=F(TN('{ACCOUNT CREDIT TOTAL:Sales}')+TN('{ACCOUNT CREDIT TOTAL:Taxa Serviço}')-TN('{ACCOUNT DEBIT TOTAL:Discount}'),'#,#0.00')]
[Resumen de Ingresos:3, 1]
Dinheiro|R$ [=F(TN('{ACCOUNT TRANSACTION TOTAL:Payment Transaction:Dinheiro}'),'#,#0.00')]
Cartao Credito|R$ [=F(TN('{ACCOUNT TRANSACTION TOTAL:Payment Transaction:Cartao}'),'#,#0.00')]
Cuenta Corriente|R$ [=F(TN('{ACCOUNT TOTAL:Customer Accounts}'),'#,#0.00')]
Total|R$ [=F(TN('{ACCOUNT TRANSACTION TOTAL:Payment Transaction:Payment Accounts}')+TN('{ACCOUNT TOTAL:Customer Accounts}'),'#,#0.00')]
will do this:
hope this helps!!!
G.
Using SQL is more involved, so I recommend using @gerlandog’s method instead.
Output:
Template:
[Daily Report:70, 30]
>TotalType|Total
@@DailyReport
SQL:
You will need to make some edits regarding your Tax Names. The Taxes used here are called T1 and T2. You you need to find/replace each with your Tax Names.
/******************************************************************************************/
/******************************************************************************************/
/*** ***/
/*** WORK PERIOD SUMMARY ***/
/*** ***/
/*** see USER SECTION (after declarations) to set Date for Report ***/
/*** ***/
/******************************************************************************************/
/******************************************************************************************/
/*****************************************/
/************* DO NOT TOUCH **************/
/***** Declarations & Initialization *****/
/*****************************************/
USE [SambaPOS4]
declare @int_WorkPeriod_ID int = 0
declare @var_WorkPeriod_ID varchar(7) = '0'
declare @dat_WorkPeriod_Beg Datetime = '9999-12-31 23:59:59'
declare @var_WorkPeriod_Beg varchar(19) = '9999-12-31 23:59:59'
declare @dat_WorkPeriod_End Datetime = '9999-12-31 23:59:59'
declare @var_WorkPeriod_End varchar(19) = '9999-12-31 23:59:59'
declare @dat_WorkPeriod_End_Check Datetime = '9999-12-31 23:59:59'
declare @int_Ticket_Beg int = 0
declare @var_Ticket_Beg varchar(6) = '0'
declare @int_Ticket_End int = 0
declare @var_Ticket_End varchar(6) = '0'
declare @int_N int = 0
--declare @dec_XR money = 1
declare @T1 money = 1
declare @T2 money = 1
/******************************************************************************************/
/******************************************************************************************/
/*** ***/
/*** USER SECTION (Begin) ***/
/*** set WorkPeriod Begin & End Dates ***/
/*** select Foreign Currency Exchange Rate ***/
/*** ***/
/******************************************************************************************/
/******************************************************************************************/
-- TODAY
set @dat_WorkPeriod_Beg = CONVERT(VARCHAR(10), GETDATE(), 120)
--set @dat_WorkPeriod_Beg = '2013-11-20'
set @dat_WorkPeriod_End = CONVERT(VARCHAR(10), DATEADD(day,1,@dat_WorkPeriod_Beg), 120)
-- OTHER DAY
--set @dat_WorkPeriod_Beg = '2014-02-12 06:59:19.193'
--set @dat_WorkPeriod_End = CONVERT(VARCHAR(10), DATEADD(day,1,@dat_WorkPeriod_Beg), 120)
--set @dat_WorkPeriod_End = '2014-02-13 00:00:44.653'
-- ForeignCurrency ExchangeRate
--SELECT @dec_XR = [ExchangeRate] FROM [dbo].[ForeignCurrencies] WHERE [Name] = 'USD'
-- Taxes
SELECT @T1 = [Rate] FROM [dbo].[TaxTemplates] WHERE [Name] = 'T1'
SELECT @T2 = [Rate] FROM [dbo].[TaxTemplates] WHERE [Name] = 'T2'
/******************************************************************************************/
/******************************************************************************************/
/*** ***/
/*** USER SECTION (End) ***/
/*** ***/
/******************************************************************************************/
/******************************************************************************************/
/*****************************************/
/************* DO NOT TOUCH **************/
/** Declarations for (in-memory) Tables **/
/*****************************************/
declare @tbl_Transactions table
(
[ID] INT IDENTITY(1,1) NOT NULL
,[TicketNumber] int
,[Date] Datetime
,[TotalAmount] money
,[TransactionDocument_Id] int
,[Amount] money
--,[Amount_USD] money
,[Name] varchar(255)
)
declare @tbl_Tx table
(
[ID] INT IDENTITY(1,1) NOT NULL
,[Date] Datetime
,[Ticket] int
,[T1] money
,[T2] money
,[Tax] money
,[Amount] money
,[TotalAmount] money
)
declare @tbl_Report table
(
[ID] INT IDENTITY(1,1) NOT NULL
,[Z_num] int
,[WP_Beg] Datetime
,[WP_End] Datetime
,[Ticket_Beg] int
,[Ticket_End] int
,[Total] money
--,[Total_USD] money
,[Total_Type] varchar(255)
)
declare @tbl_Sales table
(
[ID] INT IDENTITY(1,1) NOT NULL
,[Z_num] int
,[WP_Beg] Datetime
,[WP_End] Datetime
,[Ticket_Beg] int
,[Ticket_End] int
,[Total] money
--,[Total_USD] money
,[Total_Type] varchar(255)
)
declare @tbl_Incomes table
(
[ID] INT IDENTITY(1,1) NOT NULL
,[Z_num] int
,[WP_Beg] Datetime
,[WP_End] Datetime
,[Ticket_Beg] int
,[Ticket_End] int
,[Total] money
--,[Total_USD] money
,[Total_Type] varchar(255)
)
/*****************************************/
/************* CREATE REPORT *************/
/*****************************************/
select @int_WorkPeriod_ID = [Id] FROM [dbo].[WorkPeriods] where [StartDate] >= @dat_WorkPeriod_Beg and [EndDate] <= @dat_WorkPeriod_End
select @dat_WorkPeriod_Beg = [StartDate] FROM [dbo].[WorkPeriods] where [Id] = @int_WorkPeriod_ID
select @dat_WorkPeriod_End_Check = [EndDate] FROM [dbo].[WorkPeriods] where [Id] = @int_WorkPeriod_ID
select @dat_WorkPeriod_End =
case @dat_WorkPeriod_End_Check
when @dat_WorkPeriod_Beg then @dat_WorkPeriod_End
else @dat_WorkPeriod_End_Check
end
set @var_WorkPeriod_ID = right('0000000'+convert(varchar(7),@int_WorkPeriod_ID),7)
set @var_WorkPeriod_Beg = convert(varchar(19),@dat_WorkPeriod_Beg, 120)
set @var_WorkPeriod_End = convert(varchar(19),@dat_WorkPeriod_End, 120)
print @int_WorkPeriod_ID
print @dat_WorkPeriod_Beg
print @dat_WorkPeriod_End
print @dat_WorkPeriod_End_Check
/*****************************************/
/************* Transactions **************/
/*****************************************/
INSERT INTO @tbl_Transactions
SELECT
tkt.[TicketNumber]
,tkt.[Date]
,tkt.[TotalAmount]
,tkt.[TransactionDocument_Id]
,tx.[Amount]
--,tx.[Amount]/@dec_XR
,tx.[Name]
FROM [dbo].[Tickets] tkt
left join [dbo].[AccountTransactions] tx
on tkt.TransactionDocument_Id = tx.AccountTransactionDocumentId
WHERE tkt.[Date] >= @dat_WorkPeriod_Beg AND tkt.[Date] <= @dat_WorkPeriod_End
SELECT
@int_Ticket_Beg = min([TicketNumber])
,@int_Ticket_End = max([TicketNumber])
FROM @tbl_Transactions
set @var_Ticket_Beg = right('000000'+convert(varchar(6),@int_Ticket_Beg),6)
set @var_Ticket_End = right('000000'+convert(varchar(6),@int_Ticket_End),6)
print @int_Ticket_Beg
print @int_Ticket_End
/*****************************************/
/******************* Tx ******************/
/*****************************************/
INSERT INTO @tbl_Tx
SELECT DISTINCT
[Date] --[Date] Datetime
,[TicketNumber] --[Ticket] int
,0 --[T1] money
,0 --[T2] money
,0 --[Tax] money
,0 --[Amount] money
,0 --[TotalAmount] money
FROM @tbl_Transactions
WHERE coalesce([TotalAmount],0) > 0
ORDER BY [TicketNumber]
set @int_N = @int_Ticket_Beg
WHILE @int_N <= @int_Ticket_End
BEGIN
UPDATE @tbl_Tx SET
[TotalAmount] = (SELECT CASE WHEN EXISTS (SELECT TR.[TotalAmount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'Sale%')
THEN (SELECT TR.[TotalAmount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'Sale%')
ELSE (SELECT 0)
END)
, [Amount] = (SELECT CASE WHEN EXISTS (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'Sale%')
THEN (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'Sale%')
ELSE (SELECT 0)
END)
, [T1] = (SELECT CASE WHEN EXISTS (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'T1%')
THEN (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'T1%')
ELSE (SELECT 0)
END)
, [T2] = (SELECT CASE WHEN EXISTS (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'T2%')
THEN (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'T2%')
ELSE (SELECT 0)
END)
, [Tax] = (SELECT CASE WHEN EXISTS (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'T1%')
THEN (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'T1%')
ELSE (SELECT 0)
END)
+ (SELECT CASE WHEN EXISTS (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'T2%')
THEN (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'T2%')
ELSE (SELECT 0)
END)
WHERE [Ticket] = @int_N
set @int_N = @int_N + 1
END
--select * from @tbl_Transactions
--select * from @tbl_Tx
/*****************************************/
/****************** SALES ****************/
/*****************************************/
INSERT INTO @tbl_Sales
SELECT
@int_WorkPeriod_ID
, @dat_WorkPeriod_Beg
, @dat_WorkPeriod_End
, @int_Ticket_Beg
, @int_Ticket_End
, sum([Amount])
--, sum([Amount])/@dec_XR
, 'Tax T1'
FROM @tbl_Transactions
WHERE [Name] like '%T1%'
INSERT INTO @tbl_Sales
SELECT
@int_WorkPeriod_ID
, @dat_WorkPeriod_Beg
, @dat_WorkPeriod_End
, @int_Ticket_Beg
, @int_Ticket_End
, sum([Amount])
--, sum([Amount])/@dec_XR
, 'Tax T2'
FROM @tbl_Transactions
WHERE [Name] like '%T2%'
INSERT INTO @tbl_Sales
SELECT
@int_WorkPeriod_ID
, @dat_WorkPeriod_Beg
, @dat_WorkPeriod_End
, @int_Ticket_Beg
, @int_Ticket_End
, sum([Amount])
--, sum([Amount])/@dec_XR
, 'Tax Total'
FROM @tbl_Transactions
WHERE [Name] like '%T1%' or [Name] like '%T2%'
INSERT INTO @tbl_Sales
SELECT
@int_WorkPeriod_ID
, @dat_WorkPeriod_Beg
, @dat_WorkPeriod_End
, @int_Ticket_Beg
, @int_Ticket_End
, sum([Amount])
--, sum([Amount])/@dec_XR
, 'Sales Total Tax Excluded'
FROM @tbl_Transactions
WHERE [Name] like '%Sale%'
INSERT INTO @tbl_Sales
SELECT
@int_WorkPeriod_ID
, @dat_WorkPeriod_Beg
, @dat_WorkPeriod_End
, @int_Ticket_Beg
, @int_Ticket_End
, sum([Amount])
--, sum([Amount])/@dec_XR
, 'SALES TOTAL'
FROM @tbl_Transactions
WHERE [Name] like '%Sale%' or [Name] like '%T1%' or [Name] like '%T2%'
/*****************************************/
/***************** INCOMES ***************/
/*****************************************/
INSERT INTO @tbl_Incomes
SELECT
@int_WorkPeriod_ID
, @dat_WorkPeriod_Beg
, @dat_WorkPeriod_End
, @int_Ticket_Beg
, @int_Ticket_End
, sum(
CASE [Name]
WHEN 'Payment Transaction [Cash]' THEN [Amount]
WHEN 'Change Transaction [Cash]' THEN -1*[Amount]
ELSE 0 END
)
--, sum(
-- CASE [Name]
-- WHEN 'Payment Transaction [Cash]' THEN [Amount]
-- WHEN 'Change Transaction [Cash]' THEN -1*[Amount]
-- ELSE 0 END
-- )/@dec_XR
,'Cash'
FROM @tbl_Transactions
WHERE [Name] = 'Payment Transaction [Cash]' or [Name] = 'Change Transaction [Cash]'
--INSERT INTO @tbl_Incomes
--SELECT
-- @int_WorkPeriod_ID
--, @dat_WorkPeriod_Beg
--, @dat_WorkPeriod_End
--, @int_Ticket_Beg
--, @int_Ticket_End
--, sum(
-- CASE [Name]
-- WHEN 'Payment Transaction [Cash USD]' THEN [Amount]
-- WHEN 'Change Transaction [Cash USD]' THEN -1*[Amount]
-- ELSE 0 END
-- )
--, sum(
-- CASE [Name]
-- WHEN 'Payment Transaction [Cash USD]' THEN [Amount]
-- WHEN 'Change Transaction [Cash USD]' THEN -1*[Amount]
-- ELSE 0 END
-- )/@dec_XR
--,'Cash USD'
--FROM @tbl_Transactions
--WHERE [Name] = 'Payment Transaction [Cash USD]' or [Name] = 'Change Transaction [Cash USD]'
INSERT INTO @tbl_Incomes
SELECT
@int_WorkPeriod_ID
, @dat_WorkPeriod_Beg
, @dat_WorkPeriod_End
, @int_Ticket_Beg
, @int_Ticket_End
, sum([Amount])
--, sum([Amount])/@dec_XR
,'Credit Card'
FROM @tbl_Transactions
WHERE [Name] = 'Payment Transaction [Credit Card]'
INSERT INTO @tbl_Incomes
SELECT
@int_WorkPeriod_ID
, @dat_WorkPeriod_Beg
, @dat_WorkPeriod_End
, @int_Ticket_Beg
, @int_Ticket_End
, isnull(sum([Amount]),0)
--, isnull(sum([Amount]),0)/@dec_XR
,'Customer Account'
FROM @tbl_Transactions
WHERE [Name] like '%Customer Account Transaction%'
INSERT INTO @tbl_Incomes
SELECT
@int_WorkPeriod_ID
, @dat_WorkPeriod_Beg
, @dat_WorkPeriod_End
, @int_Ticket_Beg
, @int_Ticket_End
, isnull(sum([Debit]),0)
--, isnull(sum([Debit]),0)/@dec_XR
,'Customer Account Cash Payments'
FROM [dbo].[AccountTransactionValues] txval
left join [dbo].[AccountTransactions] tx
on [AccountTransactionId] = txval.[Id]
where [Debit] > 0
and [Date] >= @dat_WorkPeriod_Beg
and [Date] <= @dat_WorkPeriod_End
and txval.[Name] like 'Cash Payment%'
INSERT INTO @tbl_Incomes
SELECT
@int_WorkPeriod_ID
, @dat_WorkPeriod_Beg
, @dat_WorkPeriod_End
, @int_Ticket_Beg
, @int_Ticket_End
, isnull(sum([Debit]),0)
--, isnull(sum([Debit]),0)/@dec_XR
,'Customer Account CC Payments'
FROM [dbo].[AccountTransactionValues] txval
left join [dbo].[AccountTransactions] tx
on [AccountTransactionId] = txval.[Id]
where [Debit] > 0
and [Date] >= @dat_WorkPeriod_Beg
and [Date] <= @dat_WorkPeriod_End
and txval.[Name] like 'Credit Card Payment%'
INSERT INTO @tbl_Incomes
SELECT
@int_WorkPeriod_ID
, @dat_WorkPeriod_Beg
, @dat_WorkPeriod_End
, @int_Ticket_Beg
, @int_Ticket_End
, sum([Total])
--, sum([Total_USD])
,'INCOME TOTAL'
FROM @tbl_Incomes
/*****************************************/
/****************** REPORT ***************/
/*****************************************/
INSERT INTO @tbl_Report
(
[Z_num]
,[WP_Beg]
,[WP_End]
,[Ticket_Beg]
,[Ticket_End]
,[Total]
--,[Total_USD]
,[Total_Type]
) VALUES (
@int_WorkPeriod_ID
, @dat_WorkPeriod_Beg
, @dat_WorkPeriod_End
, @int_Ticket_Beg
, @int_Ticket_End
, null
--, null
,'*---------- SALES'
)
INSERT INTO @tbl_Report
select
[Z_num]
,[WP_Beg]
,[WP_End]
,[Ticket_Beg]
,[Ticket_End]
,[Total]
--,[Total_USD]
,[Total_Type]
FROM @tbl_Sales ORDER BY [ID]
INSERT INTO @tbl_Report
(
[Z_num]
,[WP_Beg]
,[WP_End]
,[Ticket_Beg]
,[Ticket_End]
,[Total]
--,[Total_USD]
,[Total_Type]
) VALUES (
@int_WorkPeriod_ID
, @dat_WorkPeriod_Beg
, @dat_WorkPeriod_End
, @int_Ticket_Beg
, @int_Ticket_End
, null
--, null
,'*-------- INCOMES'
)
INSERT INTO @tbl_Report
select
[Z_num]
,[WP_Beg]
,[WP_End]
,[Ticket_Beg]
,[Ticket_End]
,[Total]
--,[Total_USD]
,[Total_Type]
FROM @tbl_Incomes ORDER BY [ID]
INSERT INTO @tbl_Report
(
[Z_num]
,[WP_Beg]
,[WP_End]
,[Ticket_Beg]
,[Ticket_End]
,[Total]
--,[Total_USD]
,[Total_Type]
) VALUES (
@int_WorkPeriod_ID
, @dat_WorkPeriod_Beg
, @dat_WorkPeriod_End
, @int_Ticket_Beg
, @int_Ticket_End
, null
--, null
,'*------------ END'
)
SELECT
[Total_Type]
,[Total]
--,[Total_USD]
FROM @tbl_Report ORDER BY [ID]Hi,
Thanks for both answer .
What I’m trying to do right now is to show only the taxes from sales.
I tried with something like:
IVA|{ACCOUNT TOTAL:IVA}
But it doesn’t work, in my transaction types the taxes is named as IVA, should be that name?
Look in a Printer Template for hints on Tags that you can use, for example:
{ACCOUNT TOTAL:X} Account Total
{ACCOUNT DEBIT TOTAL:X} Account Debit Total
{ACCOUNT CREDIT TOTAL:X} Account Credit Total
{ACCOUNT TRANSACTION TOTAL:X} Account Transaction Total
{ACCOUNT TRANSACTION DEBIT TOTAL:X} Account Transaction Debit Total
{ACCOUNT TRANSACTION CREDIT TOTAL:X} Account Transaction Credit Total
{ACCOUNT TRANSACTION DETAILS:X} Account Transaction Details
So you could try:
{ACCOUNT TOTAL:X} in your case: {ACCOUNT TOTAL:IVA 10%}
or
{ACCOUNT TRANSACTION TOTAL:X} in your case {ACCOUNT TRANSACTION TOTAL:IVA}
Some detailed info
http://sambapos.com/wiki/doku.php?id=creating_accounting_reports_with_custom_reporting_tags
@QMcKay, I tried the line you pasted
{ACCOUNT TRANSACTION TOTAL:IVA}
but it shows 0, why?
the only one It works was this one {ACCOUNT TRANSACTION DETAILS: IVA} but I don’t know why it show each line x2, it is normal or its bug?
By the way, I though if I can get the total TAX I can do this to get the TOTAL BEFORE TAX:
{ACCOUNT TRANSACTION TOTAL:Cash}+{ACCOUNT TRANSACTION TOTAL:credit Card} - {ACCOUNT TRANSACTION TOTAL:IVA}
P.S DEBIT means CASH payment and CREDIT means Credit Card?
P.S2: @QMcKay I tried the SQL code you paste into the script but when I execute it show this error:
which means something like varchar out of intervale to datetime
@donut while listing transactions via details tag you should define both transaction type name and account name. SambaPOS accounting based on Double entry bookkeeping system so all transactions generates double transactions for both source and target accounts.
No. Those terms have nothing to do with Payment Type. They are Accounting terms referring to a Transaction: either money leaving an account or money arriving at an account.
The conversion from data type varchar to datetime produced a value out of range.
It’s difficult to say which line is causing that error. Try running the SQL in Management Studio or similar - it should indicate the line(s) that are throwing the error(s). Lines like these are probably causing this, and it may have to do with your system settings, or the DB Engine you are using:
set @dat_WorkPeriod_Beg = CONVERT(VARCHAR(10), GETDATE(), 120)
set @dat_WorkPeriod_End = CONVERT(VARCHAR(10), DATEADD(day,1,@dat_WorkPeriod_Beg), 120)
Also, try running this command in Management Studio and let me know the ouput, verbatim:
print CONVERT(VARCHAR(10), GETDATE(), 120)
I have executed on managment studio the entire code and it return error in lines 21,23,25 and 48 which I guess are:
declare @var_WorkPeriod_Beg varchar(19) = ‘9999-12-31 23:59:59’
declare @var_WorkPeriod_End varchar(19) = ‘9999-12-31 23:59:59’
declare @dat_WorkPeriod_End_Check Datetime = ‘9999-12-31 23:59:59’
set @dat_WorkPeriod_Beg = CONVERT(VARCHAR(10), GETDATE(), 120)
–set @dat_WorkPeriod_Beg = ‘2013-11-20’
the print convert sentence return this:
2014-11-14
By the other way, now I understand better whats the difference between DEBIT and CREDIT and right now trying to solve the double lines following emre instructions.
Thank you very much
Due to the nature of the error, they must actually be the lines that precede the reported line:
declare @dat_WorkPeriod_Beg Datetime = '9999-12-31 23:59:59'
declare @dat_WorkPeriod_End Datetime = '9999-12-31 23:59:59'
declare @dat_WorkPeriod_End_Check Datetime = '9999-12-31 23:59:59'
set @dat_WorkPeriod_Beg = CONVERT(VARCHAR(10), GETDATE(), 120)
I wouldn’t be surprised to find out that the cause is the codepage on your computer, so try this… change the above lines to the following (each line contains an N somewhere):
declare @dat_WorkPeriod_Beg Datetime = N'9999-12-31 23:59:59'
declare @dat_WorkPeriod_End Datetime = N'9999-12-31 23:59:59'
declare @dat_WorkPeriod_End_Check Datetime = N'9999-12-31 23:59:59'
set @dat_WorkPeriod_Beg = CONVERT(NVARCHAR(10), GETDATE(), 120)
Let me know if the errors go away for those lines; if so, then I’ll need to convert the whole script to use NVARCHAR instead of VARCHAR (which is what I should have done in the first place).
Converting data type nvarchar to datetime produced a value out of range.
Finally I achieved to show TAX, the problem was I didn’t created TAX account in Account Types…
I fixed it following this steps:
Now I have my little custom report 
How can I center the text inside the Cell?
Not sure. Makes no sense that 23,25 fail, but 27 does not. The statements are the same. I could suggest that you change them to assign a lower date, like N'2112-12-21 21:12:21' but even if that works, there is still line 48, which is completely different…
Using datetime2 type can be a solution?
@emre, using datetime2 might do the trick, since it has a larger range. What I can’t figure though is: why does the script not work as-is on his system, while it works fine on mine?
What DB type and version is being used? 32-bit or 64-bit?
I know this script works on x64 from 2008 thru 2014.
What collation is being used in the DB?
Found this.
Strange to realize ANSI SQL format is not language neutral. ISO 8601 format seems fine.
http://www.karaszi.com/SQLServer/info_datetime.asp#RecommendationsInput
