I use the ZED Report from here:
The report works fine in V5 on localDB V14
I import the localDB as SQL export file in SQL-Server 2014.
When i call the report i got an error:
“convert varchar to datetime out of range”
Hard to tell unless we can determine what line of the script the error is ocurring, but try this:
/******************************************************************************************/
/******************************************************************************************/
/*** ***/
/*** ZED REPORT ***/
/*** ***/
/*** see USER SECTION (after declarations) to set: ***/
/*** Date for Report ***/
/*** Tax Rates via Template Names ***/
/*** Foreign Currency Exchange (optional) ***/
/*** ***/
/******************************************************************************************/
/******************************************************************************************/
/*****************************************/
/************* DO NOT TOUCH **************/
/***** Declarations & Initialization *****/
/*****************************************/
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(30) = '9999-12-31 23:59:59'
declare @dat_WorkPeriod_End Datetime = '9999-12-31 23:59:59'
declare @var_WorkPeriod_End varchar(30) = '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(7) = '0'
declare @int_Ticket_End int = 0
declare @var_Ticket_End varchar(7) = '0'
declare @int_N int = 0
declare @dec_XR money = 1
declare @T1 money = 1
declare @T2 money = 1
declare @T3 money = 1
declare @T1_TTL_c varchar(19) = ''
declare @T2_TTL_c varchar(19) = ''
declare @T3_TTL_c varchar(19) = ''
declare @Tax_TTL_c varchar(19) = ''
declare @SalesTaxExcl_TTL_c varchar(19) = ''
declare @SalesNoTax_TTL_c varchar(19) = ''
declare @Sales_TTL_c varchar(19) = ''
declare @T1_TTL money = 0
declare @T2_TTL money = 0
declare @T3_TTL money = 0
declare @Tax_TTL money = 0
declare @SalesTaxExcl_TTL money = 0
declare @SalesNoTax_TTL money = 0
declare @Sales_TTL money = 0
declare @T1_taxout money = 0
declare @T2_taxout money = 0
declare @T3_taxout money = 0
declare @T1_taxout_c varchar(10) = ''
declare @T2_taxout_c varchar(10) = ''
declare @T3_taxout_c varchar(10) = ''
declare @int_PrintZED int = 0
declare @BASEPATH varchar(100) = ''
/******************************************************************************************/
/******************************************************************************************/
/*** ***/
/*** USER SECTION (Begin) ***/
/*** set WorkPeriod Begin & End Dates ***/
/*** select Tax Rates via Template Names ***/
/*** select Foreign Currency Exchange Rate ***/
/*** ***/
/******************************************************************************************/
/******************************************************************************************/
--set @BASEPATH = 'C:\D\Programs\POS\SALES\'
set @int_PrintZED = 0
-- TODAY
--set @dat_WorkPeriod_Beg = CONVERT(VARCHAR(10), GETDATE(), 126)
--set @dat_WorkPeriod_End = CONVERT(VARCHAR(10), DATEADD(day,1,@dat_WorkPeriod_Beg), 126)
-- OTHER DAY
set @dat_WorkPeriod_Beg = '{Start}'
--set @dat_WorkPeriod_End = CONVERT(VARCHAR(10), DATEADD(day,1,@dat_WorkPeriod_Beg), 126)
set @dat_WorkPeriod_End = '{End}'
set @dat_WorkPeriod_End = dateadd(Second,1,@dat_WorkPeriod_End)
-- DATE CONSTRAINED
select @int_WorkPeriod_ID = [Id] FROM [dbo].[WorkPeriods] where [StartDate] >= @dat_WorkPeriod_Beg and [EndDate] <= @dat_WorkPeriod_End
-- LATEST CLOSED WorkPeriod
--select @int_WorkPeriod_ID = max([Id]) FROM [dbo].[WorkPeriods] WHERE [StartDate] != [EndDate]
--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
-- check end date: if the workperiod is still open, end date will be the same as beg date, so change end to be 9999-12-31
--select @dat_WorkPeriod_End =
--case @dat_WorkPeriod_End_Check
--when @dat_WorkPeriod_Beg then @dat_WorkPeriod_End
--else @dat_WorkPeriod_End_Check
--end
-- ForeignCurrency ExchangeRate
--SELECT @dec_XR = [ExchangeRate] FROM [dbo].[ForeignCurrencies] WHERE [Name] = 'USD'
-- Taxes
SELECT @T1 = isnull([Rate],0) FROM [dbo].[TaxTemplates] WHERE [Name] = 'T1'
SELECT @T2 = isnull([Rate],0) FROM [dbo].[TaxTemplates] WHERE [Name] = 'T2'
SELECT @T3 = isnull([Rate],0) FROM [dbo].[TaxTemplates] WHERE [Name] = 'T3'
/******************************************************************************************/
/******************************************************************************************/
/*** ***/
/*** 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_Sales_Tx table
(
[ID] INT IDENTITY(1,1) NOT NULL
,[Date] Datetime
,[Ticket] int
,[T1] money
,[T2] money
,[T3] money
,[Tax] money
,[Amount] money
,[Tip] money
,[GCsale] money
,[TotalAmount] money
)
declare @tbl_Tx_Z table
(
[ID] INT IDENTITY(1,1) NOT NULL
,[Date] Datetime
,[Ticket] int
,[T1] money
,[T2] money
,[T3] money
,[Tax] money
,[Amount] money
,[TotalAmount] money
)
declare @tbl_ZED table
(
[ID] INT IDENTITY(1,1) NOT NULL
,[WPID] int
,[F1] varchar(255)
,[F2] varchar(255)
)
/******************************************************************************************/
/************* CREATE REPORT **************************************************************/
/******************************************************************************************/
set @var_WorkPeriod_ID = right('0000000'+convert(varchar(7),@int_WorkPeriod_ID),7)
set @var_WorkPeriod_Beg = convert(varchar(30),@dat_WorkPeriod_Beg, 126)
set @var_WorkPeriod_End = convert(varchar(30),@dat_WorkPeriod_End, 126)
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('0000000'+convert(varchar(7),@int_Ticket_Beg),7)
set @var_Ticket_End = right('0000000'+convert(varchar(7),@int_Ticket_End),7)
print @int_Ticket_Beg
print @int_Ticket_End
/*****************************************/
/******************* Tx ******************/
/*****************************************/
INSERT INTO @tbl_Sales_Tx
SELECT DISTINCT
[Date] --[Date] Datetime
,[TicketNumber] --[Ticket] int
,0 --[T1] money
,0 --[T2] money
,0 --[T3] money
,0 --[Tax] money
,0 --[Amount] money
,0 --[Tip] money
,0 --[GCsale] 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_Sales_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)
, [GCsale] = (SELECT CASE WHEN EXISTS (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'GC Purchase%')
THEN (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'GC Purchase%')
ELSE (SELECT 0)
END)
, [Tip] = (SELECT CASE WHEN EXISTS (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'Tip%')
THEN (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'Tip%')
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)
, [T3] = (SELECT CASE WHEN EXISTS (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'T3%')
THEN (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'T3%')
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)
+ (SELECT CASE WHEN EXISTS (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'T3%')
THEN (SELECT TR.[Amount] FROM @tbl_Transactions TR WHERE TR.[TicketNumber]=@int_N and TR.[Name] like 'T3%')
ELSE (SELECT 0)
END)
WHERE [Ticket] = @int_N
set @int_N = @int_N + 1
END
update @tbl_Sales_Tx set [TotalAmount] = [TotalAmount] - ([Tip] + [GCsale]) where [TotalAmount] > 0
--select * from @tbl_Transactions
--select * from @tbl_Sales_Tx
INSERT INTO @tbl_Tx_Z
SELECT
[Date]
,[Ticket]
,[T1]
,[T2]
,[T3]
,[Tax]
,[Amount]
,[TotalAmount]
FROM @tbl_Sales_Tx
ORDER BY [Ticket]
--select * from @tbl_Tx_Z
set @T1_taxout = (select isnull(sum([T1]),0)/@T1*100 from @tbl_Tx_Z)
set @T1_taxout_c = right(' '+convert(varchar(10),@T1_taxout),8)
set @T2_taxout = (select isnull(sum([T2]),0)/@T2*100 from @tbl_Tx_Z)
set @T2_taxout_c = right(' '+convert(varchar(10),@T2_taxout),8)
set @T3_taxout = (select isnull(sum([T3]),0)/@T3*100 from @tbl_Tx_Z)
set @T3_taxout_c = right(' '+convert(varchar(10),@T3_taxout),8)
set @T1_TTL = (SELECT isnull(sum([T1]),0) FROM @tbl_Tx_Z)
set @T1_TTL_c = right(' '+convert(varchar(19),@T1_TTL),19)
set @T2_TTL = (SELECT isnull(sum([T2]),0) FROM @tbl_Tx_Z)
set @T2_TTL_c = right(' '+convert(varchar(19),@T2_TTL),19)
set @T3_TTL = (SELECT isnull(sum([T3]),0) FROM @tbl_Tx_Z)
set @T3_TTL_c = right(' '+convert(varchar(19),@T3_TTL),19)
set @Tax_TTL = (SELECT isnull(sum([Tax]),0) FROM @tbl_Tx_Z)
set @Tax_TTL_c = right(' '+convert(varchar(19),@Tax_TTL),19)
set @SalesTaxExcl_TTL = @T1_taxout + @T2_taxout + @T3_taxout
set @SalesTaxExcl_TTL_c = right(' '+convert(varchar(19),@SalesTaxExcl_TTL),19)
set @SalesNoTax_TTL = (SELECT isnull(sum([TotalAmount]),0)-(@SalesTaxExcl_TTL + @Tax_TTL) FROM @tbl_Tx_Z)
set @SalesNoTax_TTL_c = right(' '+convert(varchar(19),@SalesNoTax_TTL),19)
set @Sales_TTL = (SELECT isnull(sum([Amount]),0)+isnull(sum([Tax]),0) FROM @tbl_Tx_Z)
set @Sales_TTL_c = right(' '+convert(varchar(19),@Sales_TTL),19)
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, '///////////////////','\\\\\\\\\\\\\\\\\\\'
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, ' Your Business ',' Your Corporation '
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, 'Z-num: ' + @var_WorkPeriod_ID,'REG: #############'
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, @var_WorkPeriod_Beg,'Ticket_Beg: '+@var_Ticket_Beg
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, @var_WorkPeriod_End,'Ticket_End: '+@var_Ticket_End
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, '[------------TYPE-]','[----------AMOUNT-]'
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, 'T1['+substring(convert(varchar(5),@T1),1,4)+']('+@T1_taxout_c+'):', @T1_TTL_c
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, 'T2['+substring(convert(varchar(5),@T2),1,4)+']('+@T2_taxout_c+'):', @T2_TTL_c
--T3 INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, 'T3['+substring(convert(varchar(5),@T3),1,4)+']('+@T3_taxout_c+'):', @T3_TTL_c
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, ' Tax Total:', @Tax_TTL_c
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, ' Sales (Tax Excl):', @SalesTaxExcl_TTL_c
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, ' Sales (NonTaxed):', @SalesNoTax_TTL_c
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, ' Sales Total:', @Sales_TTL_c
INSERT INTO @tbl_ZED SELECT @int_WorkPeriod_ID, '\\\\\\\\\\\\\\\\\\\','///////////////////'
SELECT [F1],[F2] FROM @tbl_ZED ORDER BY [ID]
There are 2 basic changes:
For lines that look like this:
declare @var_WorkPeriod_End varchar(19) = '9999-12-31 23:59:59'
Change them to look like this:
declare @var_WorkPeriod_End varchar(30) = '9999-12-31 23:59:59'
^^
And for lines that look like this:
set @var_WorkPeriod_Beg = convert(varchar(19),@dat_WorkPeriod_Beg, 120)
Change them to look like this:
set @var_WorkPeriod_Beg = convert(varchar(30),@dat_WorkPeriod_Beg, 126)
^^ ^^^
2 Likes
The script changes produce the same error.
I use this script and get this output as Report.xps file - I hope it helps to determine the line
Report-script.zip (92,2 KB)
It looks like the errors occur on these lines…
set @var_WorkPeriod_Beg = convert(varchar(30),@dat_WorkPeriod_Beg, 126)
set @var_WorkPeriod_End = convert(varchar(30),@dat_WorkPeriod_End, 126)
Try running the script in SSMS to confirm.
Are you certain you are running SQL LocalDB or SQL Express and not CE ?
Double-click the top-left corner where it says “SambaPOS” to bring it into Windowed mode and look at the title bar. It should say “SQ” there. If it says “CE” or “TX” then you are not running SQL.
