ALTER PROCEDURE [dbo].[Google_GetDataFromXCI]
(
-- Add the parameters for the stored procedure here
@FromDate datetime,
@ToDate datetime,
@PM nvarchar(50),
@ProductLine nvarchar(255),
@Status nvarchar(50),
@Client varchar(50)
) 这是存储过程的函数, 我调用的时候
exec [dbo].[Google_GetDataFromXCI] '2011-4-1','2011-4-30','%','%','%','%'
出现 Error converting data type varchar to datetime. 这个错误,
然后我把时间的引号去掉
exec [dbo].[Google_GetDataFromXCI] 2011-4-1,2011-4-30,'%','%','%','%'
他又说Incorrect syntax near '-'.到底怎么调用啊?这个时间格式怎么调用?
(
-- Add the parameters for the stored procedure here
@FromDate datetime,
@ToDate datetime,
@PM nvarchar(50),
@ProductLine nvarchar(255),
@Status nvarchar(50),
@Client varchar(50)
) 这是存储过程的函数, 我调用的时候
exec [dbo].[Google_GetDataFromXCI] '2011-4-1','2011-4-30','%','%','%','%'
出现 Error converting data type varchar to datetime. 这个错误,
然后我把时间的引号去掉
exec [dbo].[Google_GetDataFromXCI] 2011-4-1,2011-4-30,'%','%','%','%'
他又说Incorrect syntax near '-'.到底怎么调用啊?这个时间格式怎么调用?
exec [dbo].[Google_GetDataFromXCI] N'2011-04-01 00:00:00.000',N'2011-04-31 00:00:00.000','%','%','%','%'
是这样吗? 还是出错Error converting data type nvarchar to datetime.
(
-- Add the parameters for the stored procedure here
@FromDate varchar,
@ToDate varchar,
@PM nvarchar(50),
@ProductLine nvarchar(255),
@Status nvarchar(50),
@Client varchar(50)
)
AS
DECLARE @TempList table
(
Status int
) DECLARE @StatusVar varchar(10), @Pos int SET @Status = LTRIM(RTRIM(@Status))+ ','
SET @Pos = CHARINDEX(',', @Status, 1) IF REPLACE(@Status, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @StatusVar = LTRIM(RTRIM(LEFT(@Status, @Pos - 1)))
IF @StatusVar <> ''
BEGIN
INSERT INTO @TempList (Status) VALUES (CAST(@StatusVar AS int)) --Use Appropriate conversion
END
SET @Status = RIGHT(@Status, LEN(@Status) - @Pos)
SET @Pos = CHARINDEX(',', @Status, 1) END
END
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON; -- Insert statements for procedure here
SELECT [dbo].[GetProject].[ProjectNumber]
,[dbo].[GetProject].[ProductLine]
,[dbo].[ProjectInfo].[Platform]
,[dbo].[GetProject].[ProjectName]
,[dbo].[GetProject].[Description]
,[dbo].[GetProject].[PM]
,[dbo].[GetProject].[OrderDate]
,[dbo].[GetProject].[QuoteDate]
,CONVERT(VARCHAR(10), [dbo].[ProjectInfo].[SentForTranslationDate], 101) as StartDate
,CONVERT(VARCHAR(10), [dbo].[MaxTransDueDate].[TranslationDueDate], 101) as TranslationDueDate
,CONVERT(VARCHAR(10), [dbo].[ProjectInfo].[DeliveryDate], 101) as DueDate
,CONVERT(VARCHAR(10), [dbo].[GetProject].[CompDate], 101) as CompDate
,Cast([dbo].[ProjectInfo].[GrandTotalCost]as Decimal(9,3)) as ProjectTotal
,case
when [dbo].[GetProject].status = 1 then
'Quote'
when [dbo].[GetProject].Status=2 or [dbo].[GetProject].Status = 3 then
'Active'
when [dbo].[GetProject].Status=4 then
'To Bill'
when [dbo].[GetProject].Status = 5 then
'Marketing'
when [dbo].[GetProject].status > 5 and [dbo].[GetProject].Status < 20 then
'Final'
end as Status
, [dbo].[ProjectInfo].[flag], [dbo].[ProjectInfo].[InvoiceFlag]
FROM [dbo].[GetProject]
left join [dbo].[ProjectInfo]
on [dbo].[GetProject].[ProjectName] =[dbo].[ProjectInfo].[GoogleJobID] COLLATE SQL_Latin1_General_CP850_CI_AI
left join [dbo].[MaxTransDueDate]
on [dbo].[GetProject].[ProjectName]=[dbo].[MaxTransDueDate].[JobID] COLLATE SQL_Latin1_General_CP850_CI_AI
where [dbo].[GetProject].[ProjectNumber] like @Client and [dbo].[GetProject].status in ( Select Status from @TempList) and datediff(day, @FromDate, dbo.GetProject.OrderDate)>=0 and datediff(day, dbo.GetProject.OrderDate,@ToDate)>=0 and [dbo].[GetProject].[PM] like @PM and ([dbo].[GetProject].[ProductLine] like @ProductLine or [dbo].[GetProject].[ProductLine] is null)
order by ProjectNumber desc
END
@ToDate varchar,@FromDate varchar(10),
@ToDate varchar(10),
你要给参数长度 要不下面查询时 时间计算肯定会报错的
@FromDate datetime,
@ToDate datetime,
是这样的
看是不是你传进来的参数值 若是 则就不要纠结 在传参这了
就是你下面的 sql 语句有问题 简单看了下你存储过程没什么问题 执行这句话
SELECT * from [dbo].[GetProject]
left join [dbo].[ProjectInfo]
on [dbo].[GetProject].[ProjectName] =[dbo].[ProjectInfo].[GoogleJobID] COLLATE SQL_Latin1_General_CP850_CI_AI
left join [dbo].[MaxTransDueDate]
on [dbo].[GetProject].[ProjectName]=[dbo].[MaxTransDueDate].[JobID] COLLATE SQL_Latin1_General_CP850_CI_AI
where datediff(day, @FromDate, dbo.GetProject.OrderDate)>=0 and datediff(day, dbo.GetProject.OrderDate,@ToDate)>=0 看这句话会不会报错 如果有 则就是你数据有问题了