declare @date date = '2010-07-21' select @date as d -- 没问题 /* d ---------- 2010-07-21 */
sorry,这个应该是sql2008新增的类型,我目前用过最高的版本是sql2005按照msdn的描述http://msdn.microsoft.com/zh-cn/library/ms180878(SQL.100).aspx date 只存储日期。 time 只存储时间。 按照你这样赋值应该是没有问题的
ALTER PROCEDURE [dbo].[YK_Market_AchieveCount] @BeginTime date, @EndTime date, @StartIndex int, @EndIndex int AS BEGIN BEGIN TRAN BEGIN TRY DECLARE @SQL NVARCHAR(MAX), @V_CHAR NVARCHAR(50), @Cou int, @SQLCou nvarchar(max), @Identical nvarchar(max) SET @V_CHAR = ''''; SET @V_CHAR=SUBSTRING(@V_CHAR,1,1); set @SQL=N' SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY P.ContractTime) as pos, m.CompanyName as CompanyName, m.ParentCompanyName as ParentCompanyName,p.ContractNum as ContractNum , p.Money as Money, p.Pioneer as Pioneer,p.ContractTime as ContractTime FROM YK_PublicCase p LEFT JOIN YK_MarketClient m ON p.MarketClientID = m.ID'+' WHERE p.ContractNum<> '+ @V_CHAR+@V_CHAR IF @BeginTime<>''and @EndTime<>''+@V_CHAR+@V_CHAR BEGIN SET @SQL = @SQL+' AND p.ContractTime between '+@V_CHAR+Convert(nvarchar,@BeginTime)+@V_CHAR+' and '+@V_CHAR+Convert(nvarchar,@EndTime)+@V_CHAR END IF @BeginTime<>'' and @EndTime='' BEGIN SET @SQL = @SQL+' AND p.ContractTime between '+@V_CHAR+Convert(nvarchar,@BeginTime)+@V_CHAR+' and '+@V_CHAR+Convert(nvarchar,CONVERT (date, SYSDATETIME()))+@V_CHAR END
IF @BeginTime='' and @EndTime<>'' BEGIN SET @SQL = @SQL+' AND p.ContractTime between '+@V_CHAR+Convert(nvarchar,@BeginTime)+@V_CHAR+' and '+@V_CHAR+Convert(nvarchar,@EndTime)+@V_CHAR END
IF @BeginTime='' and @EndTime='' BEGIN SET @SQL = @SQL+' AND p.ContractTime between '+@V_CHAR+Convert(nvarchar,@BeginTime)+@V_CHAR+' and '+@V_CHAR+Convert(nvarchar,CONVERT (date, SYSDATETIME()))+@V_CHAR END set @SQL=@SQL+') AS SP WHERE pos between '+CONVERT(VARCHAR,@StartIndex,23)+' and '+CONVERT(VARCHAR,@EndIndex,23) EXEC(@SQL) commit tran END TRY BEGIN CATCH ROLLBACK TRAN SELECT ERROR_MESSAGE() as ErrorMessage END CATCH END这是我的存储过程, EXEC YK_Market_AchieveCount '','',1,100 这样就没错,但是我加上自己的参数 EXEC YK_Market_AchieveCount '2010-05-06','2010-08-08','','','',1,100 就提示上面的错误 有点麻烦,还请大家帮帮忙
between 那里串日期部分错了
谢谢各位了,是我的SQL语句拼接错误了,下面附上正确代码: ALTER PROCEDURE [dbo].[YK_Market_AchieveCount] @BeginTime date, @EndTime date, @Pioneer NVARCHAR(50), @CompanyName NVARCHAR(50), @ParentCompanyName NVARCHAR(50), @StartIndex int, @EndIndex int AS BEGIN BEGIN TRAN BEGIN TRY DECLARE @SQL NVARCHAR(MAX), @V_CHAR NVARCHAR(50), @Cou int, @SQLCou nvarchar(max), @Identical nvarchar(max) SET @V_CHAR = ''''; SET @V_CHAR=SUBSTRING(@V_CHAR,1,1); set @SQL=N' SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY P.ContractTime) as pos, m.CompanyName as CompanyName, m.ParentCompanyName as ParentCompanyName,p.ContractNum as ContractNum , p.Money as Money, p.Pioneer as Pioneer,p.ContractTime as ContractTime FROM YK_PublicCase p LEFT JOIN YK_MarketClient m ON p.MarketClientID = m.ID'+' WHERE p.ContractNum<> '+ @V_CHAR+@V_CHAR+' and '+@V_CHAR+CONVERT(nvarchar,@BeginTime)+@V_CHAR+' and ' if ISNULL(@EndTime,'')<>'' begin set @SQL=@SQL+@V_CHAR+CONVERT(nvarchar,@EndTime)+@V_CHAR end else begin set @SQL=@SQL+@V_CHAR+Convert(nvarchar,CONVERT (date, SYSUTCDATETIME()))+@V_CHAR end
IF ISNULL(@Pioneer,'')<>'' BEGIN SET @SQL = @SQL+' AND p.Pioneer like ''%'+@Pioneer+'%''' END
IF ISNULL(@CompanyName,'')<>'' BEGIN SET @SQL = @SQL+' AND CompanyName like ''%'+@CompanyName+'%''' END
IF ISNULL(@ParentCompanyName,'')<>'' BEGIN SET @SQL = @SQL+' AND ParentCompanyName like ''%'+@ParentCompanyName+'%''' END set @SQL=@SQL+') AS SP WHERE pos between '+CONVERT(VARCHAR,@StartIndex,23)+' and '+CONVERT(VARCHAR,@EndIndex,23) PRINT(@SQL) commit tran END TRY BEGIN CATCH ROLLBACK TRAN SELECT ERROR_MESSAGE() as ErrorMessage END CATCH END
-- 晕,7楼的是这里错了 IF @BeginTime<>''and @EndTime<>''+@V_CHAR+@V_CHAR --改为 IF @BeginTime<>''and @EndTime<>''--+@V_CHAR+@V_CHAR
这是我的建表界面,明明有date类型的
select @date as d -- 没问题
/*
d
----------
2010-07-21
*/
sorry,这个应该是sql2008新增的类型,我目前用过最高的版本是sql2005按照msdn的描述http://msdn.microsoft.com/zh-cn/library/ms180878(SQL.100).aspx
date 只存储日期。
time 只存储时间。
按照你这样赋值应该是没有问题的
ALTER PROCEDURE [dbo].[YK_Market_AchieveCount]
@BeginTime date,
@EndTime date,
@StartIndex int,
@EndIndex int
AS
BEGIN
BEGIN TRAN
BEGIN TRY
DECLARE @SQL NVARCHAR(MAX),
@V_CHAR NVARCHAR(50),
@Cou int,
@SQLCou nvarchar(max),
@Identical nvarchar(max)
SET @V_CHAR = '''';
SET @V_CHAR=SUBSTRING(@V_CHAR,1,1);
set @SQL=N'
SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY P.ContractTime) as pos, m.CompanyName as CompanyName, m.ParentCompanyName as ParentCompanyName,p.ContractNum as ContractNum , p.Money as Money, p.Pioneer as Pioneer,p.ContractTime as ContractTime
FROM YK_PublicCase p
LEFT JOIN YK_MarketClient m
ON p.MarketClientID = m.ID'+'
WHERE p.ContractNum<> '+ @V_CHAR+@V_CHAR
IF @BeginTime<>''and @EndTime<>''+@V_CHAR+@V_CHAR
BEGIN
SET @SQL = @SQL+' AND p.ContractTime between '+@V_CHAR+Convert(nvarchar,@BeginTime)+@V_CHAR+' and '+@V_CHAR+Convert(nvarchar,@EndTime)+@V_CHAR
END
IF @BeginTime<>'' and @EndTime=''
BEGIN
SET @SQL = @SQL+' AND p.ContractTime between '+@V_CHAR+Convert(nvarchar,@BeginTime)+@V_CHAR+' and '+@V_CHAR+Convert(nvarchar,CONVERT (date, SYSDATETIME()))+@V_CHAR
END
IF @BeginTime='' and @EndTime<>''
BEGIN
SET @SQL = @SQL+' AND p.ContractTime between '+@V_CHAR+Convert(nvarchar,@BeginTime)+@V_CHAR+' and '+@V_CHAR+Convert(nvarchar,@EndTime)+@V_CHAR
END
IF @BeginTime='' and @EndTime=''
BEGIN
SET @SQL = @SQL+' AND p.ContractTime between '+@V_CHAR+Convert(nvarchar,@BeginTime)+@V_CHAR+' and '+@V_CHAR+Convert(nvarchar,CONVERT (date, SYSDATETIME()))+@V_CHAR
END
set @SQL=@SQL+') AS SP WHERE pos between '+CONVERT(VARCHAR,@StartIndex,23)+' and '+CONVERT(VARCHAR,@EndIndex,23)
EXEC(@SQL)
commit tran
END TRY
BEGIN CATCH
ROLLBACK TRAN
SELECT ERROR_MESSAGE() as ErrorMessage
END CATCH
END这是我的存储过程,
EXEC YK_Market_AchieveCount '','',1,100
这样就没错,但是我加上自己的参数
EXEC YK_Market_AchieveCount '2010-05-06','2010-08-08','','','',1,100
就提示上面的错误
有点麻烦,还请大家帮帮忙
ALTER PROCEDURE [dbo].[YK_Market_AchieveCount]
@BeginTime date,
@EndTime date,
@Pioneer NVARCHAR(50),
@CompanyName NVARCHAR(50),
@ParentCompanyName NVARCHAR(50),
@StartIndex int,
@EndIndex int
AS
BEGIN
BEGIN TRAN
BEGIN TRY
DECLARE @SQL NVARCHAR(MAX),
@V_CHAR NVARCHAR(50),
@Cou int,
@SQLCou nvarchar(max),
@Identical nvarchar(max)
SET @V_CHAR = '''';
SET @V_CHAR=SUBSTRING(@V_CHAR,1,1);
set @SQL=N'
SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY P.ContractTime) as pos, m.CompanyName as CompanyName, m.ParentCompanyName as ParentCompanyName,p.ContractNum as ContractNum , p.Money as Money, p.Pioneer as Pioneer,p.ContractTime as ContractTime
FROM YK_PublicCase p
LEFT JOIN YK_MarketClient m
ON p.MarketClientID = m.ID'+'
WHERE p.ContractNum<> '+ @V_CHAR+@V_CHAR+' and '+@V_CHAR+CONVERT(nvarchar,@BeginTime)+@V_CHAR+' and '
if ISNULL(@EndTime,'')<>''
begin
set @SQL=@SQL+@V_CHAR+CONVERT(nvarchar,@EndTime)+@V_CHAR
end
else
begin
set @SQL=@SQL+@V_CHAR+Convert(nvarchar,CONVERT (date, SYSUTCDATETIME()))+@V_CHAR
end
IF ISNULL(@Pioneer,'')<>''
BEGIN
SET @SQL = @SQL+' AND p.Pioneer like ''%'+@Pioneer+'%'''
END
IF ISNULL(@CompanyName,'')<>''
BEGIN
SET @SQL = @SQL+' AND CompanyName like ''%'+@CompanyName+'%'''
END
IF ISNULL(@ParentCompanyName,'')<>''
BEGIN
SET @SQL = @SQL+' AND ParentCompanyName like ''%'+@ParentCompanyName+'%'''
END
set @SQL=@SQL+') AS SP WHERE pos between '+CONVERT(VARCHAR,@StartIndex,23)+' and '+CONVERT(VARCHAR,@EndIndex,23)
PRINT(@SQL)
commit tran
END TRY
BEGIN CATCH
ROLLBACK TRAN
SELECT ERROR_MESSAGE() as ErrorMessage
END CATCH
END
IF @BeginTime<>''and @EndTime<>''+@V_CHAR+@V_CHAR
--改为
IF @BeginTime<>''and @EndTime<>''--+@V_CHAR+@V_CHAR