无论是否是闰年,都试图从正常年份获取数据。 [英] Trying to get data from preivous year regardless if it is a leap year or not.
问题描述
我正在使用SQL Server 2012和我们的ERP SAP Business One,我发现有些年份可能是闰年。目前我正在尝试使用静态日期,看看我是否可以获得上一年的销售额。在ERP中,我将使用特殊变量
替换日期,最终用户可以选择日期。但是在SQL Manager中运行我的代码时出现此错误
" 消息402,级别16,状态1,行3¥b $ b数据类型varchar和varchar在减法运算符中不兼容。"
SELECT YEAR(getdate()) - 1 As '年',(SELECT ISNULL(SUM(DocTotal - VatSum),0)
来自OINV T0
WHERE T0.DocDate> =(CONVERT(十进制(10,2),'2019-03- 01') - CASE
WHEN(CONVERT(十进制(10,2),YEAR(T0。[DocDate]))%4 = 0.00)
THEN'366'
WHEN(CONVERT(十进制(10,2),年(T0。[DocDate]))%4!= 0.00)
THEN
'365'
ELSE'0'
END)和T0 。[DocDate]< ='2019-03-31'-CASE
WHEN(CONVERT(十进制(10,2),YEAR(T0。[DocDate]))%4 = 0.00)
THEN '366'
WHEN(CONVERT(十进制(10,2),YEAR(T0。[DocDate]))%4!= 0.00)
THEN
'365'
ELSE '0'
END和DocCur ='$') -
(SELECT ISNULL(SUM(DocTotal - VatSum),0)
来自ORI N T1
WHERE T1.DocDate BETWEEN'2019-03-01' - CASE
WHEN(CONVERT(十进制(10,2),年份(T1。[DocDate]))%4 = 0.00)
THEN'366'
WHEN(CONVERT(十进制(10,2),年份(T1。[DocDate]))%4!= 0.00)
THEN
'365'
ELSE'0'
END AND'2019-03-31' - CASE
WHEN(CONVERT(十进制(10,2),年份(T1。[DocDate]))%4 = 0.00 )
THEN'366'
WHEN(CONVERT(十进制(10,2),年份(T1。[DocDate]))%4!= 0.00)
THEN
'365 '
ELSE'0'
END AND DocCur ='$')AS'总CND销售额',
(SELECT ISNULL(SUM(DocTotalFC - VatSumFC),0)
来自OINV T0
WHERE T0.DocDate BETWEEN'2019-03-01' - CASE
WHEN(CONVERT(十进制(10,2),年(T0。[DocDate]))%4 = 0.00)
THEN'366'
WHEN(CONVERT(十进制(10,2),YEAR(T0。[DocDate]))%4!= 0.00)
THEN
'365'
ELSE'0'
END AND'2019-03-31'-CASE
WHEN(CONVERT(十进制(10,2),年(T0。[DocDate])) %4 = 0.00)
那么'366'
WHEN(CONVERT(十进制(10,2),YEAR(T0。[DocDate]))%4!= 0.00)
THEN
'365'
ELSE'0'
END AND DocCur ='USD') -
(SELECT ISNULL(SUM(DocTotalFC - VatSumFC),0)
FROM ORIN T1
WHERE T1.DocDate BETWEEN'2019-03-01' - CASE
WHEN(CONVERT(十进制(10,2),年份(T1。[DocDate]))%4 = 0.00)
THEN'366'
WHEN(CONVERT(十进制)(10 ,2),YEAR(T1。[DocDate]))%4!= 0.00)
THEN
'365'
ELSE'0'
END AND'2019-03- 31' - CASE
WHEN(CONVERT(十进制(10,2),年份(T1。[DocDate]))%4 = 0.00)
THEN'366'
WHEN(CONVERT(十进制) (10,2),YEAR(T1。[DocDate]))%4!= 0.00)
THEN
'365'
ELSE'0'
END AND DocCur =' USD')AS'美国总销售额',
(SELECT SUM(TaxSum)
来自OINV T0
INNER JOIN INV4 T1 ON T1.DocEntry = T0.DocEntry AND staType = 2
WHERE T0.DocDate BETWEEN'2019-03-01' - CASE
WHEN(CONVERT(十进制(10,2),年(T0。[DocDate]))%4 = 0.00)
THEN'366'
WHEN(CONVERT(十进制(10,2),YEAR(T0。[DocDate]))%4!= 0.00)
THEN
'365'
ELSE'0'
END AND'2019-03-31' - CASE
WHEN(CONVERT(十进制(10,2),年份(T0。[DocDate]))%4 = 0.00)
那么'366'
WHEN(CONVERT(十进制(10,2),年(T0。[DocDate]))%4!= 0.00)
THEN
' 365'
ELSE'0'
END) -
(SELECT SUM(TaxSum)
FROM ORIN T0
INNER JOIN RIN4 T1 ON T1.DocEntry = T0.DocEntry AND staType = 2
WHERE T0.DocDate BETWEEN'2019-03-01'-CASE
WHEN(CONVERT(十进制(10,2),年(T0。[DocDate]))%4 = 0.00 )
THEN'366'
WHEN(CONVERT(十进制(10,2),年份(T0。[DocDate]))%4!= 0.00)
THEN
'365 '
ELSE'0'
END AND'2019-03-31' - CASE
WHEN(CONVERT(十进制(10,2),年份(T0。[DocDate]))%4 = 0.00)
THEN'366'
WHEN(CONVERT(十进制(10,2),YEAR(T0。[DocDate]))%4!= 0.00)
THEN
'365'
ELSE'0'
END)AS'总消费税',
(SELECT SUM(TaxSum)
来自OINV T0
INNER JOIN INV4 T1 ON T1.DocEntry = T0.DocEntry AND staType = 1
WHERE T0。 DocDate BETWEEN'2019-03-01' - CASE
WHEN(CONVERT(十进制(10,2),年(T0。[DocDate]))%4 = 0.00)
THEN'366'
WHEN(CONVERT(十进制(10,2),YEAR(T0。[DocDate]))%4!= 0.00)
THEN
'365'
ELSE'0'
结束和'2019-03-31' - CASE
WHEN(CONVERT(十进制(10,2),年(T0。[DocDate]))%4 = 0.00)
THEN'366'
WHEN(CONVERT(十进制(10,2),YEAR(T0。[DocDate]))%4!= 0.00)
THEN
'365'
ELSE'0'
END) -
(SELECT SUM(TaxSum)
FROM ORIN T0
INNER JOIN RIN4 T1 ON T1.DocEntry = T0.DocEntry AND staType = 1
WHERE T0 .DocDate BETWEEN'2019-03-01' - CASE
WHEN(CONVERT(十进制(10,2),年(T0。[DocDate]))%4 = 0.00)
THEN'366'
WHEN(CONVERT(十进制(10,2),YEAR(T0。[DocDate]))%4!= 0.00)
THEN
'365'
ELSE'0'
结束AND'2019-03-31' - CASE
WHEN(CONVERT(十进制(10,2),年份(T0。[DocDate]))%4 = 0.00)
THEN'366'
WHEN(CONVERT(十进制(10,2),YEAR(T0。[DocDate]))%4!= 0.00)
THEN
'365'
ELSE'0'
END)AS'总PST'
Hi
jbrotto,
由于您的错误显示您将使用数字进行数学计算,然后显示您想要的结果。
如果在进行减法之前进行转换,则SQL不知道如何从另一个字符串中减去一个字符串。
有一些错误:
------> 1.CONVERT(十进制(10,2),'2019-03-01')
选择CONVERT(十进制(10,2),'2019-03-01')
/ *
Msg 8114,Level 16,State 5,Line 10
将数据类型varchar转换为数字时出错。
* /
------> 2.'2019-03-31'-CASE
WHEN(CONVERT(十进制(10,2),YEAR(T0。[DocDate]))%4 = 0.00) 那么'366'
WHEN(CONVERT(十进制(10,2),YEAR(T0。[DocDate]))%4!= 0.00) 那么'365'
ELSE'0'
END
我认为这是导致你得到错误信息的原因。另外'='和'!='包含所有可能性,所以ELSE'0'可能是多余的。
根据您的描述,您想要上一年的记录,无论当前年份是否为闰年。
为什么不你直接过滤时间来获取这些记录吗?请尝试遵循WHERE子句。
- 选择dateadd(year,datediff(年,0,'2019-03-01'),0)
- 选择dateadd(年份,datediff(年,0,'2019-03-01') - 1,0)
- - / *
-------------------------
--2018-01-01 00:00:00.000
- * /
- 选择dateadd(day,-1,dateadd(year,datediff(year,0,'2019-03-01'),0))
- / *
-------------------------
--2018-12-31 00:00:00.000
- - * /
......
WHAD DocDate
dateadd(年份,年份,年份,0,'2019-03-01') - 1,0)
和dateadd(day,-1,dateadd(year,datediff(year,0,'2019-03-01'),0))
希望它可以帮到你。
最好的问候,
拉结
I am using SQL server 2012 with our ERP SAP Business One and I see that some years might be a leap year. For now I am trying to with static dates see if I could get the previous year sales. In the ERP I will be replacing the dates with a special variable that end users can select dates. But I get this error when running my code in SQL Manager
"Msg 402, Level 16, State 1, Line 3
The data types varchar and varchar are incompatible in the subtract operator."
SELECT YEAR(getdate())-1 As 'Year', (SELECT ISNULL(SUM(DocTotal - VatSum) , 0) FROM OINV T0 WHERE T0.DocDate >= (CONVERT(decimal(10,2),'2019-03-01') - CASE WHEN (CONVERT(decimal(10,2), YEAR(T0.[DocDate]))%4 = 0.00 ) THEN '366' WHEN (CONVERT(decimal(10,2), YEAR(T0.[DocDate]))%4 != 0.00 ) THEN '365' ELSE '0' END ) AND T0.[DocDate] <= '2019-03-31' -CASE WHEN (CONVERT(decimal(10,2), YEAR(T0.[DocDate]))%4 = 0.00 ) THEN '366' WHEN (CONVERT(decimal(10,2), YEAR(T0.[DocDate]))%4 != 0.00 ) THEN '365' ELSE '0' END AND DocCur = '$') - (SELECT ISNULL(SUM(DocTotal - VatSum) , 0) FROM ORIN T1 WHERE T1.DocDate BETWEEN '2019-03-01' - CASE WHEN (CONVERT(decimal(10,2), YEAR(T1.[DocDate]))%4 = 0.00 ) THEN '366' WHEN (CONVERT(decimal(10,2), YEAR(T1.[DocDate]))%4 != 0.00 ) THEN '365' ELSE '0' END AND '2019-03-31' - CASE WHEN (CONVERT(decimal(10,2), YEAR(T1.[DocDate]))%4 = 0.00 ) THEN '366' WHEN (CONVERT(decimal(10,2), YEAR(T1.[DocDate]))%4 != 0.00 ) THEN '365' ELSE '0' END AND DocCur = '$') AS 'Total CND Sales', (SELECT ISNULL(SUM(DocTotalFC - VatSumFC), 0) FROM OINV T0 WHERE T0.DocDate BETWEEN '2019-03-01' - CASE WHEN (CONVERT(decimal(10,2), YEAR(T0.[DocDate]))%4 = 0.00 ) THEN '366' WHEN (CONVERT(decimal(10,2), YEAR(T0.[DocDate]))%4 != 0.00 ) THEN '365' ELSE '0' END AND '2019-03-31' -CASE WHEN (CONVERT(decimal(10,2), YEAR(T0.[DocDate]))%4 = 0.00 ) THEN '366' WHEN (CONVERT(decimal(10,2), YEAR(T0.[DocDate]))%4 != 0.00 ) THEN '365' ELSE '0' END AND DocCur = 'USD') - (SELECT ISNULL(SUM(DocTotalFC - VatSumFC), 0) FROM ORIN T1 WHERE T1.DocDate BETWEEN '2019-03-01' - CASE WHEN (CONVERT(decimal(10,2), YEAR(T1.[DocDate]))%4 = 0.00 ) THEN '366' WHEN (CONVERT(decimal(10,2), YEAR(T1.[DocDate]))%4 != 0.00 ) THEN '365' ELSE '0' END AND '2019-03-31' - CASE WHEN (CONVERT(decimal(10,2), YEAR(T1.[DocDate]))%4 = 0.00 ) THEN '366' WHEN (CONVERT(decimal(10,2), YEAR(T1.[DocDate]))%4 != 0.00 ) THEN '365' ELSE '0' END AND DocCur = 'USD') AS 'Total US Sales', (SELECT SUM(TaxSum) FROM OINV T0 INNER JOIN INV4 T1 ON T1.DocEntry = T0.DocEntry AND staType = 2 WHERE T0.DocDate BETWEEN '2019-03-01' - CASE WHEN (CONVERT(decimal(10,2), YEAR(T0.[DocDate]))%4 = 0.00 ) THEN '366' WHEN (CONVERT(decimal(10,2), YEAR(T0.[DocDate]))%4 != 0.00 ) THEN '365' ELSE '0' END AND '2019-03-31' - CASE WHEN (CONVERT(decimal(10,2), YEAR(T0.[DocDate]))%4 = 0.00 ) THEN '366' WHEN (CONVERT(decimal(10,2), YEAR(T0.[DocDate]))%4 != 0.00 ) THEN '365' ELSE '0' END) - (SELECT SUM(TaxSum) FROM ORIN T0 INNER JOIN RIN4 T1 ON T1.DocEntry = T0.DocEntry AND staType = 2 WHERE T0.DocDate BETWEEN '2019-03-01' -CASE WHEN (CONVERT(decimal(10,2), YEAR(T0.[DocDate]))%4 = 0.00 ) THEN '366' WHEN (CONVERT(decimal(10,2), YEAR(T0.[DocDate]))%4 != 0.00 ) THEN '365' ELSE '0' END AND '2019-03-31' - CASE WHEN (CONVERT(decimal(10,2), YEAR(T0.[DocDate]))%4 = 0.00 ) THEN '366' WHEN (CONVERT(decimal(10,2), YEAR(T0.[DocDate]))%4 != 0.00 ) THEN '365' ELSE '0' END) AS 'Total GST', (SELECT SUM(TaxSum) FROM OINV T0 INNER JOIN INV4 T1 ON T1.DocEntry = T0.DocEntry AND staType = 1 WHERE T0.DocDate BETWEEN '2019-03-01' - CASE WHEN (CONVERT(decimal(10,2), YEAR(T0.[DocDate]))%4 = 0.00 ) THEN '366' WHEN (CONVERT(decimal(10,2), YEAR(T0.[DocDate]))%4 != 0.00 ) THEN '365' ELSE '0' END AND '2019-03-31' - CASE WHEN (CONVERT(decimal(10,2), YEAR(T0.[DocDate]))%4 = 0.00 ) THEN '366' WHEN (CONVERT(decimal(10,2), YEAR(T0.[DocDate]))%4 != 0.00 ) THEN '365' ELSE '0' END) - (SELECT SUM(TaxSum) FROM ORIN T0 INNER JOIN RIN4 T1 ON T1.DocEntry = T0.DocEntry AND staType = 1 WHERE T0.DocDate BETWEEN '2019-03-01' - CASE WHEN (CONVERT(decimal(10,2), YEAR(T0.[DocDate]))%4 = 0.00 ) THEN '366' WHEN (CONVERT(decimal(10,2), YEAR(T0.[DocDate]))%4 != 0.00 ) THEN '365' ELSE '0' END AND '2019-03-31' - CASE WHEN (CONVERT(decimal(10,2), YEAR(T0.[DocDate]))%4 = 0.00 ) THEN '366' WHEN (CONVERT(decimal(10,2), YEAR(T0.[DocDate]))%4 != 0.00 ) THEN '365' ELSE '0' END) AS 'Total PST'
Hi jbrotto,
As your error shows that you will do the math with numbers and then display the results however you want. If you convert before doing subtraction, SQL has no clue how to subtract one string from another.
There are some mistakes:
------>1.CONVERT(decimal(10,2),'2019-03-01')
select CONVERT(decimal(10,2),'2019-03-01') /* Msg 8114, Level 16, State 5, Line 10 Error converting data type varchar to numeric. */
------>2.'2019-03-31' -CASE
WHEN (CONVERT(decimal(10,2), YEAR(T0.[DocDate]))%4 = 0.00 )THEN '366'
WHEN (CONVERT(decimal(10,2), YEAR(T0.[DocDate]))%4 != 0.00 )THEN '365'
ELSE '0' END
I think this is what causes the error message you get. Also '=' and '!=' contain all possibility, so ELSE '0' might be redundant.
Per your description , you would like the record of the previous year, regardless of whether the current year is a leap year. Why don't you filter the time directly to get these records? Please try following WHERE clause.
--select dateadd(year, datediff(year, 0, '2019-03-01'), 0) --select dateadd(year, datediff(year, 0, '2019-03-01')-1, 0) --/* ------------------------- --2018-01-01 00:00:00.000 --*/ --select dateadd(day,-1,dateadd(year, datediff(year, 0, '2019-03-01'), 0)) --/* ------------------------- --2018-12-31 00:00:00.000 --*/ …… WHERE DocDate between dateadd(year, datediff(year, 0, '2019-03-01')-1, 0) and dateadd(day,-1,dateadd(year, datediff(year, 0, '2019-03-01'), 0))
Hope it can help you.
Best Regards,
Rachel
这篇关于无论是否是闰年,都试图从正常年份获取数据。的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!