Sql Server 日期转换有关问题！

2012-01-24

Sql Server 日期转换问题！！！SQL codeALTER Procedure [dbo].[proc_Test](@StartTime datetime NULL,@End

Sql Server 日期转换问题！！！

SQL code

ALTER Procedure [dbo].[proc_Test](    @StartTime datetime = NULL,    @EndTime      datetime = NULL)AS exec('SELECT          *    FROM        Test T    WHERE        ('+@StartTime+'             is NULL or DATEDIFF(day,'+@StartTime+', T.CreateTime) >= 0) and        ( '+@EndTime+' is NULL or DATEDIFF(day,'+@EndTime+', T.CreateTime) <= 0)     ORDER BY        ID ')

报错为消息 241，级别 16，状态 1，过程 proc_OrderForm_Select，第 48 行
从字符串转换日期和/或时间时，转换失败。

不知如何解决啊

[解决办法]
try-

SQL code

ALTER Procedure [dbo].[proc_Test](    @StartTime datetime = NULL,    @EndTime      datetime = NULL)AS exec('SELECT          *    FROM        Test T    WHERE        ('+@StartTime+'             is NULL or DATEDIFF(day,'+convert(varchar(19),@StartTime,120)+', T.CreateTime) >= 0) and        ( '+convert(varchar(19),@EndTime,120)+' is NULL or DATEDIFF(day,'+convert(varchar(19),@EndTime,120)+', T.CreateTime) <= 0)     ORDER BY        ID ')
[解决办法]
这个没必要动态执行,还有你检查一下是否都能转成日期呀,提示很明显
[解决办法]
SQL codeexec('SELECT          *    FROM        Test T    WHERE        ('+@StartTime+'             is NULL or DATEDIFF(day,'+cast(@StartTime as varchar(50))+', T.CreateTime) >= 0) and        ( '+@EndTime+' is NULL or DATEDIFF(day,'+@EndTime+', T.CreateTime) <= 0)     ORDER BY        ID ')
[解决办法]
SQL codeSELECT          *    FROM        Test T    WHERE        @StartTime            is NULL or DATEDIFF(day,@StartTime, T.CreateTime) >= 0) and        @EndTime is NULL or DATEDIFF(day,@EndTime, T.CreateTime) <= 0)     ORDER BY        ID )
[解决办法]
SQL code'SELECT          *    FROM        Test T    WHERE        ('+CONVERT(NVARCHAR,@StartTime,111)+'             is NULL or DATEDIFF(day,'+CONVERT(NVARCHAR,@StartTime,111)+', T.CreateTime) >= 0) and        ( '+CONVERT(NVARCHAR,@EndTime,111)+' is NULL or DATEDIFF(day,'+CONVERT(NVARCHAR,@EndTime,111)+', T.CreateTime) <= 0)     ORDER BY        ID '
[解决办法]
SQL codeSELECT          *    FROM        Test T    WHERE        @StartTime            is NULL or DATEDIFF(day,@StartTime, T.CreateTime) >= 0) and        @EndTime is NULL or DATEDIFF(day,@EndTime, T.CreateTime) <= 0)     ORDER BY        ID

热点排行

SQL Server

Sql Server 日期转换有关问题！