QUE1
select * from table1 WHERE YM = ××
××是我在页面输入的年月的值的下一个月,YM是varchar(6)
下面这句话是我读出输入月份的下一个
SELECT TOP 1 YM FROM table1 WHERE YM > @YM
我现在想查出输入月份下一个的数据:如输入200708需查200709
求这句SQL
QUE2
我要查出输入月份前一年的数据怎么查,如输入200708
现在要查以前数据的和就是200607--200708的数据的和还有200608-200708的和
select * from table1 WHERE YM = ××
××是我在页面输入的年月的值的下一个月,YM是varchar(6)
下面这句话是我读出输入月份的下一个
SELECT TOP 1 YM FROM table1 WHERE YM > @YM
我现在想查出输入月份下一个的数据:如输入200708需查200709
求这句SQL
QUE2
我要查出输入月份前一年的数据怎么查,如输入200708
现在要查以前数据的和就是200607--200708的数据的和还有200608-200708的和
--200607--200708的数据
Select SUM(統計字段) As 和 from table1 WHERE DateDiff(mm, YM + '01', @YM + '01') Between 0 And 13--200608--200708的数据
Select SUM(統計字段) As 和 from table1 WHERE DateDiff(mm, YM + '01', @YM + '01') Between 0 And 12
select * from table1 WHERE YM = ××
××是我在页面输入的年月的值的下一个月,YM是varchar(6)
下面这句话是我读出输入月份的下一个
SELECT TOP 1 YM FROM table1 WHERE YM > @YM
我现在想查出输入月份下一个的数据:如输入200708需查200709
求这句SQL---------------
select * from table1 where ym=convert( varchar(6),dateadd(mm,1,cast(@ym as datetime)),112)
我要查出输入月份前一年的数据怎么查,如输入200708
现在要查以前数据的和就是200607--200708的数据的和还有200608-200708的和
--------------------
select * from table1
where ym between convert( varchar(6),dateadd(yy,-1,cast(@ym as datetime)),112)
and
convert( varchar(6),cast(@ym as datetime),112)
QUE1
select * from table1 WHERE YM = ××
××是我在页面输入的年月的值的下一个月,YM是varchar(6)
下面这句话是我读出输入月份的下一个
SELECT TOP 1 YM FROM table1 WHERE YM > @YM
我现在想查出输入月份下一个的数据:如输入200708需查200709
求这句SQL---------------
select * from table1 where ym=convert( varchar(6),dateadd(mm,1,cast(@ym as datetime)),112)
------------
這個有問題的。注意格式是200708這種的改為這樣cast(@ym + '01' as datetime)
select * from table1 WHERE YM = ××
××是我在页面输入的年月的值的下一个月,YM是varchar(6)
下面这句话是我读出输入月份的下一个
SELECT TOP 1 YM FROM table1 WHERE YM > @YM
我现在想查出输入月份下一个的数据:如输入200708需查200709declare @ym as varchar(6)
set @ym = '200708'
if left(@ym,2) <> 12
select * from table1 WHERE YM = cast(cast(@ym as int) + 1 as varchar)
else
select * from table1 WHERE YM = cast(cast(left(@ym,4) as int) + 1) + '01' 求这句SQL
QUE2
我要查出输入月份前一年的数据怎么查,如输入200708
现在要查以前数据的和就是200607--200708的数据的和还有200608-200708的和declare @ym as varchar(6)
set @ym = '200708'
select * from table1 WHERE YM >= cast(cast(left(@ym,4) as int) - 1) + right(@ym,2) and YM < @ym
------
200709(所影响的行数为 1 行)鱼兄说得对
select * from table1 WHERE YM = ××
××是我在页面输入的年月的值的下一个月,YM是varchar(6)
下面这句话是我读出输入月份的下一个
SELECT TOP 1 YM FROM table1 WHERE YM > @YM
我现在想查出输入月份下一个的数据:如输入200708需查200709declare @ym as varchar(6)
set @ym = '200708'
if right(@ym,2) <> 12
select * from table1 WHERE YM = cast(cast(@ym as int) + 1 as varchar)
else
select * from table1 WHERE YM = cast(cast(left(@ym,4) as int) + 1) + '01' 求这句SQL
QUE2
我要查出输入月份前一年的数据怎么查,如输入200708
现在要查以前数据的和就是200607--200708的数据的和还有200608-200708的和declare @ym as varchar(6)
set @ym = '200708'
select * from table1 WHERE YM >= cast(cast(left(@ym,4) as int) - 1) + right(@ym,2) and YM <= @ym
qu2
select * from table1
where ym between convert( varchar(6),dateadd(yy,-1,cast(@ym+'01' as datetime)),112)
and convert( varchar(6),cast(@ym+'01' as datetime),112)
select * from table1 WHERE YM = ××
××是我在页面输入的年月的值的下一个月,YM是varchar(6)
下面这句话是我读出输入月份的下一个
SELECT TOP 1 YM FROM table1 WHERE YM > @YM
我现在想查出输入月份下一个的数据:如输入200708需查200709declare @ym as varchar(6)
set @ym = '200708'
if right(@ym,2) <> '12'
select * from table1 WHERE YM = cast(cast(@ym as int) + 1 as varchar)
else
select * from table1 WHERE YM = cast(cast(left(@ym,4) as int) + 1) + '01' 求这句SQL
QUE2
我要查出输入月份前一年的数据怎么查,如输入200708
现在要查以前数据的和就是200607--200708的数据的和还有200608-200708的和declare @ym as varchar(6)
set @ym = '200708'
select * from table1 WHERE YM >= cast(cast(left(@ym,4) as int) - 1) + right(@ym,2) and YM <= @ym
YM' 在選取清單中無效,因為它並未包含在彙總函數或 GROUP BY 子句中。
-----------
你運行的語句貼出來看看
DateDiff(mm, convert(datetime(datetime,YM + '01'),convert(datetime,@YM + '01'))
DateDiff(mm, convert(datetime(datetime,YM + '01'),convert(datetime,@YM + '01'))
----------------------------------------
晕,写错了
DateDiff(mm, convert(datetime,YM + '01'),convert(datetime,@YM + '01'))
declare @ym as varchar(6)
set @ym = '200708'
select * from table1 WHERE YM >= cast(cast(left(@ym,4) as int) - 1) + right(@ym,2) and YM < @ym
'cast' 附近的語法不正確,必須是 'AS'。
set @ym='200708'
declare @PRODID nvarchar
set @PRODID='61F4C001'
SELECT SUM(T_AC_EXP_ACHIEVE_ASSESSMENT.REACH_ADD) AS [24],
SUM(T_AC_EXP_ACHIEVE_ASSESSMENT.REACH_DEDUCT) AS [25],
SUM(T_AC_EXP_ACHIEVE_ASSESSMENT.SUM_AGREE_QTY) AS [26],
SUM(T_AC_EXP_ACHIEVE_ASSESSMENT.SUM_DELIV_QTY) AS [28],
SUM(T_AC_EXP_ACHIEVE_ASSESSMENT.AVERAGE_APP) AS [29],
SUM(T_AC_EXP_ACHIEVE_ASSESSMENT.BM_APP) AS [30]
FROM T_AC_EXP_ACHIEVE_ASSESSMENT INNER JOIN
T_AC_SINGLE_ACHIEVE ON
T_AC_EXP_ACHIEVE_ASSESSMENT.ACHIEVE_YM = T_AC_SINGLE_ACHIEVE.ACHIEVE_YM
WHERE (T_AC_EXP_ACHIEVE_ASSESSMENT.PROD_CLASS_ID = @PRODID) AND
(T_AC_SINGLE_ACHIEVE.ACHIEVE_YM >= cast(cast(left(@ym,4) as int) - 1) + right(@ym,2) and T_AC_SINGLE_ACHIEVE.ACHIEVE_YM < @ym
)
set @ym='200708'
declare @PRODID nvarchar
set @PRODID='61F4C001'
SELECT SUM(T_AC_EXP_ACHIEVE_ASSESSMENT.REACH_ADD) AS [24],
SUM(T_AC_EXP_ACHIEVE_ASSESSMENT.REACH_DEDUCT) AS [25],
SUM(T_AC_EXP_ACHIEVE_ASSESSMENT.SUM_AGREE_QTY) AS [26],
SUM(T_AC_EXP_ACHIEVE_ASSESSMENT.SUM_DELIV_QTY) AS [28],
SUM(T_AC_EXP_ACHIEVE_ASSESSMENT.AVERAGE_APP) AS [29],
SUM(T_AC_EXP_ACHIEVE_ASSESSMENT.BM_APP) AS [30]
FROM T_AC_EXP_ACHIEVE_ASSESSMENT INNER JOIN
T_AC_SINGLE_ACHIEVE ON
T_AC_EXP_ACHIEVE_ASSESSMENT.ACHIEVE_YM = T_AC_SINGLE_ACHIEVE.ACHIEVE_YM
WHERE (T_AC_EXP_ACHIEVE_ASSESSMENT.PROD_CLASS_ID = @PRODID) AND
(T_AC_SINGLE_ACHIEVE.ACHIEVE_YM >= cast(cast(left(@ym,4) as int) - 1) + right(@ym,2) and T_AC_SINGLE_ACHIEVE.ACHIEVE_YM < @ym)
YM' 在選取清單中無效,因為它並未包含在彙總函數或 GROUP BY 子句中。
-----------
這個是哪個語句的錯誤?用這個查詢下,看看表中有沒有非法的日期數據
Select * From table1 Where IsDate(YM + '01') = 0PS:不用另外開帖子,我一直在關注這個帖子。
declare @ym as varchar(6)
set @ym = '200708'
select * from table1 WHERE YM >= cast(cast(left(@ym,4) as int) - 1 as varchar) + right(@ym,2) and YM < @ym再试试
可以查出来
日期都是200708
--------------
什麼意思?如果這個查詢可以查出數據Select * From table1 Where IsDate(YM + '01') = 0說明你的表中有非法的日期數據