各位,为何如下会提示错误?
USE [MES]
GO
/****** Object: StoredProcedure [dbo].[SP_CQ_REPORT] Script Date: 12/31/2008 13:08:11 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_CQ_REPORT] AS
IF NOT EXISTS (SELECT * FROM REPORT WHERE RQ=CONVERT(NVARCHAR(10),GETDATE(),111))
BEGIN
INSERT INTO REPORT(ACCOUNT_ID,FULL_NAME,RQ,ZW,BMID,KBID,XBID,BC,CBID)
SELECT ACCOUNT_ID,FULL_NAME, convert (nvarchar(10),getdate(),111),ZW,BMID,KBID,XBID,BC ,CBID
FROM RES_USER
WHERE STATUS<>2 AND BMID NOT LIKE 'N%'
END
UPDATE REPORT SET REPORT.JDSTATUS=1,REPORT.JDBM=CQ_JD.XBM,REPORT.JDKB=CQ_JD.XKB,REPORT.JDXB=CQ_JD.XXB
FROM REPORT,CQ_JD
WHERE REPORT.ACCOUNT_ID=CQ_JD.ACCOUNT_ID AND
CQ_JD.SXRQ<=convert(smalldatetime,convert(nvarchar(10), GETDATE())) AND CQ_JD.EXRQ>=convert(smalldatetime,convert(nvarchar(10), GETDATE()))
UPDATE REPORT
SET REPORT.TNSTATUS=C.NX,REPORT.DJSTATUS=C.DJ,REPORT.CJSTATUS=C.CJ
FROM REPORT,(
select ACCOUNT_ID,NX,
CASE WHEN ( (SHJ>0) OR ( BJ>0) OR ( CJ>0) OR ( HJ>0) OR ( GJ>0) OR ( SJ>0) ) THEN 1 ELSE 0 END AS QJ,
CASE WHEN ((QJTime-T)>=2) THEN 1 ELSE 0 END AS CJ,
CASE WHEN ((QJTime-T)<2) and ((QJTime-T)>0) and ( NX=0) THEN 1 ELSE 0 END AS DJ
FROM (
SELECT ACCOUNT_ID,
case when ( DATEDIFF(minute,TNXJST, convert(smalldatetime, convert(nvarchar(10),getdate(),111)))>=0) AND ( DATEDIFF(minute,TNXJET, convert(smalldatetime, convert(nvarchar(10),getdate(),111)))<=0) THEN 1 ELSE 0 END AS NX,
case when ( DATEDIFF(minute, convert(smalldatetime, convert(nvarchar(10),getdate(),111)),SHJST)>=0) AND ( DATEDIFF(minute,SHJET, convert(smalldatetime, convert(nvarchar(10),getdate(),111)))<=0) THEN 1 ELSE 0 END AS SHJ,
case when ( DATEDIFF(minute, convert(smalldatetime, convert(nvarchar(10),getdate(),111)), BJST)>=0) AND ( DATEDIFF(minute,BJET, convert(smalldatetime, convert(nvarchar(10),getdate(),111)))<=0) THEN 1 ELSE 0 END AS BJ,
case when ( DATEDIFF(minute, convert(smalldatetime, convert(nvarchar(10),getdate(),111)), CJST)>=0) AND ( DATEDIFF(minute,CJET, convert(smalldatetime, convert(nvarchar(10),getdate(),111)))<=0) THEN 1 ELSE 0 END AS CJ,
case when ( DATEDIFF(minute, convert(smalldatetime, convert(nvarchar(10),getdate(),111)), HJST)>=0) AND ( DATEDIFF(minute,HJET, convert(smalldatetime, convert(nvarchar(10),getdate(),111)))<=0) THEN 1 ELSE 0 END AS HJ,
case when ( DATEDIFF(minute, convert(smalldatetime, convert(nvarchar(10),getdate(),111)), GJST)>=0) AND ( DATEDIFF(minute,GJET, convert(smalldatetime, convert(nvarchar(10),getdate(),111)))<=0) THEN 1 ELSE 0 END AS GJ,
case when ( DATEDIFF(minute, convert(smalldatetime, convert(nvarchar(10),getdate(),111)), SJST)>=0) AND ( DATEDIFF(minute,SJET, convert(smalldatetime, convert(nvarchar(10),getdate(),111)))<=0) THEN 1 ELSE 0 END AS SJ,
case when ( ( DATEDIFF(minute, convert(smalldatetime, convert(nvarchar(10),getdate(),111)), SHJST)>=0) AND ( DATEDIFF(minute,SHJET, convert(smalldatetime, convert(nvarchar(10),getdate(),111)))<=0) ) or
( ( DATEDIFF(minute,convert(smalldatetime, convert(nvarchar(10),getdate(),111)) , BJST)>=0) AND ( DATEDIFF(minute,BJET, convert(smalldatetime, convert(nvarchar(10),getdate(),111)))<=0) ) or
( ( DATEDIFF(minute, convert(smalldatetime, convert(nvarchar(10),getdate(),111)), CJST)>=0) AND ( DATEDIFF(minute,CJET, convert(smalldatetime, convert(nvarchar(10),getdate(),111)))<=0) ) or
( ( DATEDIFF(minute, convert(smalldatetime, convert(nvarchar(10),getdate(),111)) , HJST)>=0) AND ( DATEDIFF(minute,HJET, convert(smalldatetime, convert(nvarchar(10),getdate(),111)))<=0) ) or
( ( DATEDIFF(minute, convert(smalldatetime, convert(nvarchar(10),getdate(),111)), GJST)>=0) AND ( DATEDIFF(minute,GJET, convert(smalldatetime, convert(nvarchar(10),getdate(),111)))<=0) ) or
( ( DATEDIFF(minute, convert(smalldatetime, convert(nvarchar(10),getdate(),111)), SJST)>=0) AND ( DATEDIFF(minute,SJET, convert(smalldatetime, convert(nvarchar(10),getdate(),111)))<=0) )
THEN datediff(day,SXRQ,EXRQ)+1 ELSE 0 END AS QJTime,
case when TNXJ=1 THEN datediff(day,TNXJST,TNXJET)+1 else 0 end as T
FROM CQ_QJ ) AS TMP01
) AS C
WHERE REPORT.ACCOUNT_ID=C.ACCOUNT_ID UPDATE REPORT
SET REPORT.KGSTATUS=1
FROM REPORT,(
SELECT ACCOUNT_ID
FROM (
SELECT ACCOUNT_ID,
ST,ET,datediff(HH,ST,ET) as T,
CASE WHEN (DATEDIFF(DAY,ST,GETDATE())>=0) AND (DATEDIFF(DAY,ET,GETDATE())<=0) THEN 1 ELSE 0 END AS KG
from
(
select ACCOUNT_ID,
case when KSSJ>convert(smalldatetime,CONVERT(NVARCHAR(4),GETDATE(),111)+'/'+CONVERT(NVARCHAR(2),MONTH(GETDATE()))+'/01') then KSSJ else convert(smalldatetime,CONVERT(NVARCHAR(4),GETDATE(),108)+'/'+CONVERT(NVARCHAR(2),MONTH(GETDATE()))+'/01') end as ST,
case when JSSJ<dateadd(day,day(dateadd(day,-1,convert(char(8),dateadd(month,1,getdate() ),120)+'1')) -1, convert(smalldatetime, CONVERT(NVARCHAR(4),GETDATE(),111)+'/'+CONVERT(NVARCHAR(2),MONTH(GETDATE()))+'/01')) then JSSJ else dateadd(day,day(dateadd(day,-1,convert(char(8),dateadd(month,1,getdate() ),120)+'1')) -1, convert(smalldatetime, CONVERT(NVARCHAR(4),GETDATE(),111)+'/'+CONVERT(NVARCHAR(2),MONTH(GETDATE()))+'/01')) end as ET
from CQ_KG
where KSSJ>convert(smalldatetime,CONVERT(NVARCHAR(4),GETDATE(),111)+'/'+CONVERT(NVARCHAR(2),MONTH(GETDATE()))+'/01')
) as Tmp02
) AS Tmp002
WHERE KG=1) AS Tmp0002
where REPORT.ACCOUNT_ID=Tmp0002.ACCOUNT_ID
UPDATE REPORT
SET REPORT.GFHSTATUS=TMP4.GHF
FROM REPORT,
(
select ACCOUNT_ID,'1' AS GHF from cq_gwhf
where DATEDIFF(DAY,CQ_GWHF.HFRQ,GETDATE())=0 AND CQ_GWHF.STATUS=1
) AS TMP4
WHERE REPORT.ACCOUNT_ID=TMP4.ACCOUNT_ID
UPDATE REPORT
SET REPORT.GCMSTATUS=TMP004002.GCM
FROM REPORT,
(
SELECT ACCOUNT_ID,'1' AS GCM FROM CQ_GWHF
WHERE ( DATEDIFF(DAY,CMRQ,GETDATE())>=0 AND DATEDIFF(DAY,CMRQ,GETDATE())<=15 ) AND STATUS=0
) AS TMP004002
WHERE REPORT.ACCOUNT_ID=TMP004002.ACCOUNT_ID
USE [MES]
GO
/****** Object: StoredProcedure [dbo].[SP_CQ_REPORT] Script Date: 12/31/2008 13:08:11 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_CQ_REPORT] AS
IF NOT EXISTS (SELECT * FROM REPORT WHERE RQ=CONVERT(NVARCHAR(10),GETDATE(),111))
BEGIN
INSERT INTO REPORT(ACCOUNT_ID,FULL_NAME,RQ,ZW,BMID,KBID,XBID,BC,CBID)
SELECT ACCOUNT_ID,FULL_NAME, convert (nvarchar(10),getdate(),111),ZW,BMID,KBID,XBID,BC ,CBID
FROM RES_USER
WHERE STATUS<>2 AND BMID NOT LIKE 'N%'
END
UPDATE REPORT SET REPORT.JDSTATUS=1,REPORT.JDBM=CQ_JD.XBM,REPORT.JDKB=CQ_JD.XKB,REPORT.JDXB=CQ_JD.XXB
FROM REPORT,CQ_JD
WHERE REPORT.ACCOUNT_ID=CQ_JD.ACCOUNT_ID AND
CQ_JD.SXRQ<=convert(smalldatetime,convert(nvarchar(10), GETDATE())) AND CQ_JD.EXRQ>=convert(smalldatetime,convert(nvarchar(10), GETDATE()))
UPDATE REPORT
SET REPORT.TNSTATUS=C.NX,REPORT.DJSTATUS=C.DJ,REPORT.CJSTATUS=C.CJ
FROM REPORT,(
select ACCOUNT_ID,NX,
CASE WHEN ( (SHJ>0) OR ( BJ>0) OR ( CJ>0) OR ( HJ>0) OR ( GJ>0) OR ( SJ>0) ) THEN 1 ELSE 0 END AS QJ,
CASE WHEN ((QJTime-T)>=2) THEN 1 ELSE 0 END AS CJ,
CASE WHEN ((QJTime-T)<2) and ((QJTime-T)>0) and ( NX=0) THEN 1 ELSE 0 END AS DJ
FROM (
SELECT ACCOUNT_ID,
case when ( DATEDIFF(minute,TNXJST, convert(smalldatetime, convert(nvarchar(10),getdate(),111)))>=0) AND ( DATEDIFF(minute,TNXJET, convert(smalldatetime, convert(nvarchar(10),getdate(),111)))<=0) THEN 1 ELSE 0 END AS NX,
case when ( DATEDIFF(minute, convert(smalldatetime, convert(nvarchar(10),getdate(),111)),SHJST)>=0) AND ( DATEDIFF(minute,SHJET, convert(smalldatetime, convert(nvarchar(10),getdate(),111)))<=0) THEN 1 ELSE 0 END AS SHJ,
case when ( DATEDIFF(minute, convert(smalldatetime, convert(nvarchar(10),getdate(),111)), BJST)>=0) AND ( DATEDIFF(minute,BJET, convert(smalldatetime, convert(nvarchar(10),getdate(),111)))<=0) THEN 1 ELSE 0 END AS BJ,
case when ( DATEDIFF(minute, convert(smalldatetime, convert(nvarchar(10),getdate(),111)), CJST)>=0) AND ( DATEDIFF(minute,CJET, convert(smalldatetime, convert(nvarchar(10),getdate(),111)))<=0) THEN 1 ELSE 0 END AS CJ,
case when ( DATEDIFF(minute, convert(smalldatetime, convert(nvarchar(10),getdate(),111)), HJST)>=0) AND ( DATEDIFF(minute,HJET, convert(smalldatetime, convert(nvarchar(10),getdate(),111)))<=0) THEN 1 ELSE 0 END AS HJ,
case when ( DATEDIFF(minute, convert(smalldatetime, convert(nvarchar(10),getdate(),111)), GJST)>=0) AND ( DATEDIFF(minute,GJET, convert(smalldatetime, convert(nvarchar(10),getdate(),111)))<=0) THEN 1 ELSE 0 END AS GJ,
case when ( DATEDIFF(minute, convert(smalldatetime, convert(nvarchar(10),getdate(),111)), SJST)>=0) AND ( DATEDIFF(minute,SJET, convert(smalldatetime, convert(nvarchar(10),getdate(),111)))<=0) THEN 1 ELSE 0 END AS SJ,
case when ( ( DATEDIFF(minute, convert(smalldatetime, convert(nvarchar(10),getdate(),111)), SHJST)>=0) AND ( DATEDIFF(minute,SHJET, convert(smalldatetime, convert(nvarchar(10),getdate(),111)))<=0) ) or
( ( DATEDIFF(minute,convert(smalldatetime, convert(nvarchar(10),getdate(),111)) , BJST)>=0) AND ( DATEDIFF(minute,BJET, convert(smalldatetime, convert(nvarchar(10),getdate(),111)))<=0) ) or
( ( DATEDIFF(minute, convert(smalldatetime, convert(nvarchar(10),getdate(),111)), CJST)>=0) AND ( DATEDIFF(minute,CJET, convert(smalldatetime, convert(nvarchar(10),getdate(),111)))<=0) ) or
( ( DATEDIFF(minute, convert(smalldatetime, convert(nvarchar(10),getdate(),111)) , HJST)>=0) AND ( DATEDIFF(minute,HJET, convert(smalldatetime, convert(nvarchar(10),getdate(),111)))<=0) ) or
( ( DATEDIFF(minute, convert(smalldatetime, convert(nvarchar(10),getdate(),111)), GJST)>=0) AND ( DATEDIFF(minute,GJET, convert(smalldatetime, convert(nvarchar(10),getdate(),111)))<=0) ) or
( ( DATEDIFF(minute, convert(smalldatetime, convert(nvarchar(10),getdate(),111)), SJST)>=0) AND ( DATEDIFF(minute,SJET, convert(smalldatetime, convert(nvarchar(10),getdate(),111)))<=0) )
THEN datediff(day,SXRQ,EXRQ)+1 ELSE 0 END AS QJTime,
case when TNXJ=1 THEN datediff(day,TNXJST,TNXJET)+1 else 0 end as T
FROM CQ_QJ ) AS TMP01
) AS C
WHERE REPORT.ACCOUNT_ID=C.ACCOUNT_ID UPDATE REPORT
SET REPORT.KGSTATUS=1
FROM REPORT,(
SELECT ACCOUNT_ID
FROM (
SELECT ACCOUNT_ID,
ST,ET,datediff(HH,ST,ET) as T,
CASE WHEN (DATEDIFF(DAY,ST,GETDATE())>=0) AND (DATEDIFF(DAY,ET,GETDATE())<=0) THEN 1 ELSE 0 END AS KG
from
(
select ACCOUNT_ID,
case when KSSJ>convert(smalldatetime,CONVERT(NVARCHAR(4),GETDATE(),111)+'/'+CONVERT(NVARCHAR(2),MONTH(GETDATE()))+'/01') then KSSJ else convert(smalldatetime,CONVERT(NVARCHAR(4),GETDATE(),108)+'/'+CONVERT(NVARCHAR(2),MONTH(GETDATE()))+'/01') end as ST,
case when JSSJ<dateadd(day,day(dateadd(day,-1,convert(char(8),dateadd(month,1,getdate() ),120)+'1')) -1, convert(smalldatetime, CONVERT(NVARCHAR(4),GETDATE(),111)+'/'+CONVERT(NVARCHAR(2),MONTH(GETDATE()))+'/01')) then JSSJ else dateadd(day,day(dateadd(day,-1,convert(char(8),dateadd(month,1,getdate() ),120)+'1')) -1, convert(smalldatetime, CONVERT(NVARCHAR(4),GETDATE(),111)+'/'+CONVERT(NVARCHAR(2),MONTH(GETDATE()))+'/01')) end as ET
from CQ_KG
where KSSJ>convert(smalldatetime,CONVERT(NVARCHAR(4),GETDATE(),111)+'/'+CONVERT(NVARCHAR(2),MONTH(GETDATE()))+'/01')
) as Tmp02
) AS Tmp002
WHERE KG=1) AS Tmp0002
where REPORT.ACCOUNT_ID=Tmp0002.ACCOUNT_ID
UPDATE REPORT
SET REPORT.GFHSTATUS=TMP4.GHF
FROM REPORT,
(
select ACCOUNT_ID,'1' AS GHF from cq_gwhf
where DATEDIFF(DAY,CQ_GWHF.HFRQ,GETDATE())=0 AND CQ_GWHF.STATUS=1
) AS TMP4
WHERE REPORT.ACCOUNT_ID=TMP4.ACCOUNT_ID
UPDATE REPORT
SET REPORT.GCMSTATUS=TMP004002.GCM
FROM REPORT,
(
SELECT ACCOUNT_ID,'1' AS GCM FROM CQ_GWHF
WHERE ( DATEDIFF(DAY,CMRQ,GETDATE())>=0 AND DATEDIFF(DAY,CMRQ,GETDATE())<=15 ) AND STATUS=0
) AS TMP004002
WHERE REPORT.ACCOUNT_ID=TMP004002.ACCOUNT_ID
case when KSSJ>convert(smalldatetime,CONVERT(NVARCHAR(4),GETDATE(),111)+'/'+CONVERT(NVARCHAR(2),MONTH(GETDATE()))+'/01') then KSSJ else convert(smalldatetime,CONVERT(NVARCHAR(4),GETDATE(),108)+'/'+CONVERT(NVARCHAR(2),MONTH(GETDATE()))+'/01') end as ST,
case when JSSJ<dateadd(day,day(dateadd(day,-1,convert(char(8),dateadd(month,1,getdate() ),120)+'1')) -1, convert(smalldatetime, CONVERT(NVARCHAR(4),GETDATE(),111)+'/'+CONVERT(NVARCHAR(2),MONTH(GETDATE()))+'/01')) then JSSJ else dateadd(day,day(dateadd(day,-1,convert(char(8),dateadd(month,1,getdate() ),120)+'1')) -1, convert(smalldatetime, CONVERT(NVARCHAR(4),GETDATE(),111)+'/'+CONVERT(NVARCHAR(2),MONTH(GETDATE()))+'/01')) end as ET
from CQ_KG
where KSSJ>convert(smalldatetime,CONVERT(NVARCHAR(4),GETDATE(),111)+'/'+CONVERT(NVARCHAR(2),MONTH(GETDATE()))+'/01')有问题,因为你这里convert(smalldatetime,CONVERT(NVARCHAR(4),GETDATE(),108)应改为convert(smalldatetime,CONVERT(NVARCHAR(4),GETDATE(),111),转换为日期吧。