34,587
社区成员
发帖
与我相关
我的任务
分享
@dtmStartTime nvarchar(20),
@dtmEndTime nvarchar(20),
@intUserType int
AS
declare @chvnSql NVARCHAR(1000)
set @chvnSql=N'SELECT id,Url,from,userName,userType FROM 表名 '
IF(LEN(@dtmStartTime)>0 and LEN(@dtmEndTime)>0 or @intUserType>0)
set @chvnSql=@chvnSql+' where id>0 '
--查询条件
IF(LEN(@dtmStartTime)>0 or LEN(@dtmEndTime)>0)
set @chvnSql=@chvnSql+ ' and [userVisitTime] between convert(datetime,'''+@dtmStartTime+''') and convert(datetime,'''+@dtmStartTime+''')'
IF(@intUserType>0)
set @chvnSql=@chvnSql+ ' and [userType] ='+STR(@intUserType)
EXECUTE sp_executesql @chvnSql
GO
SELECT * FROM pageVisits where [userVisitTime] between '2008-7-4' and '2008-7-28' and [userType] =3
@dtmStartTime nvarchar(20),
@dtmEndTime nvarchar(20),
@intUserType int
AS
declare @chvnSql NVARCHAR(1000)
set @chvnSql=N'SELECT id,Url,from,userName,userType FROM 表名 '
IF(LEN(@dtmStartTime)>0 and LEN(@dtmEndTime)>0 or @intUserType>0)
set @chvnSql=@chvnSql+' where id>0 '
--查询条件
IF(LEN(@dtmStartTime)>0 or LEN(@dtmEndTime)>0)
set @chvnSql=@chvnSql+ ' and [userVisitTime] between convert(datetime,'''+@dtmStartTime+''') and convert(datetime,'''+@dtmStartTime+''')'
IF(@intUserType>0)
set @chvnSql=@chvnSql+ ' and [userType] ='+STR(@intUserType)
print @chvnSql
-- EXECUTE sp_executesql @chvnSql
GO
IF(LEN(@dtmStartTime)>0 and LEN(@dtmEndTime)>0 or @intUserType>0)
set @chvnSql=@chvnSql+' where id>0 '
SELECT id,Url,from,userName,userType FROM 表名 and [userVisitTime] between '2008-7-4' and '2008-7-28' and [userType] =3
set @chvnSql=N'SELECT id,Url,from,userName,userType FROM 表名 '
IF(LEN(@dtmStartTime)>0 and LEN(@dtmEndTime)>0 or @intUserType>0)
set @chvnSql=@chvnSql+' where id>0 '
set @chvnSql=N'SELECT id,Url,from,userName,userType FROM 表名 WHERE 1=1 ' -- 加上WHERE 1=1
IF(LEN(@dtmStartTime)>0 and LEN(@dtmEndTime)>0 or @intUserType>0)
set @sqlWhere=@sqlWhere+' AND id>0 ' -- 去掉WHERE ,加上 AND
1.确认时间格式一致
2.Str(@intUserType)==》Ltrim(Str(@intUserType))
set @chvnSql=@chvnSql+ ' and [userVisitTime] between convert(datetime,'''+@dtmStartTime+''') and convert(datetime,'''+@dtmStartTime+''')'
declare @dtmStartTime nvarchar(20),
@dtmEndTime nvarchar(20),
@intUserType int
select @dtmStartTime=getdate(),@dtmEndTime=getdate(),@intUserType=1
declare @chvnSql NVARCHAR(1000)
set @chvnSql=N'SELECT id,Url,from,userName,userType FROM 表名 '
IF(LEN(@dtmStartTime)>0 and LEN(@dtmEndTime)>0 or @intUserType>0)
set @chvnSql=@chvnSql+' where id>0 '
--查询条件
IF(LEN(@dtmStartTime)>0 or LEN(@dtmEndTime)>0)
set @chvnSql=@chvnSql+ ' and [userVisitTime] between convert(datetime,'''+@dtmStartTime+''') and convert(datetime,'''+@dtmStartTime+''')'
IF(@intUserType>0)
set @chvnSql=@chvnSql+ ' and [userType] ='+STR(@intUserType)
print @chvnSql
GO
结果
SELECT id,Url,from,userName,userType FROM 表名 where id>0 and [userVisitTime] between convert(datetime,'07 28 2008 1:00PM') and convert(datetime,'07 28 2008 1:00PM') and [userType] = 1