adddate字段,比如要比较它是否大于当前的日期,就可以这样写: where (CONVERT(varchar, adddate,120) >= CONVERT(varchar, GETDATE(),120))
convert(varchar(),字段名称,120) 注:120指的是转成:yyyy-mm-dd hh:mm:ss 111表示yy/mm/dd 112表示yymmdd如:select * from table where convert(char,时间字段,111) = convert(char,getdate(),111)--查询日期等于当天
只见过把string转为datetime比较的!
cast(convert(varchar,getdate(),120) as datetime 或通过函数实现
直接用日期比较“ RIQI < '2009-2-1”要快很多......
ALTER procedure [dbo].[GetPagedLogger] @userName nvarchar(50),@start int,@num int,@sort nvarchar(50),@dir nvarchar(50),@limit int,@startDate datetime,@endDate datetime as BEGIN declare @cmd nvarchar(500) set @cmd= 'select UserName as UserName, LoggerDateTime as LoggerDateTime, Operation as Operation, Complete as Complete, Res as Res FROM ( SELECT *,ROW_NUMBER() OVER (order by '+@sort+' '+@dir+')as RowNumber FROM T_Logger where convert(nvarchar(20),LoggerDateTime,111) between '+ convert(nvarchar(20),@startDate,111)+' AND '+ convert(nvarchar(20),@endDate,111)+') T WHERE T.RowNumber>'+ cast(@start AS NVARCHAR) +'AND T.RowNumber<='+cast((@start+@num) AS NVARCHAR) exec(@cmd) end 这是我用字符串形式写的
if @dir='ASC' BEGIN select UserName as UserName, convert(nvarchar(20),LoggerDateTime,120) as LoggerDateTime, Operation as Operation, Complete as Complete, Res as Res FROM ( SELECT *,ROW_NUMBER() OVER (order by @sort ASC)as RowNumber FROM T_Logger where LoggerDateTime between @startDate and @endDate) T WHERE UserName=@userName and T.RowNumber>@start AND T.RowNumber<=(@start+@num) end else BEGIN select UserName as UserName, LoggerDateTime as LoggerDateTime, Operation as Operation, Complete as Complete, Res as Res FROM ( SELECT *,ROW_NUMBER() OVER (order by @sort DESC)as RowNumber FROM T_Logger where LoggerDateTime between @startDate and @endDate) T WHERE UserName=@userName and T.RowNumber>@start AND T.RowNumber<=(@start+@num) end这是用非字符串形式写的执行else时desc没起作用
http://msdn.microsoft.com/en-us/library/ms187928.aspx
select '1'
else
select '2'result 1
if('2008-1-01' < '2008-02-01')
select '1'
else
select '2'result 2只不过日期格式要一直
where (CONVERT(varchar, adddate,120) >= CONVERT(varchar, GETDATE(),120))
注:120指的是转成:yyyy-mm-dd hh:mm:ss
111表示yy/mm/dd
112表示yymmdd如:select * from table where convert(char,时间字段,111) = convert(char,getdate(),111)--查询日期等于当天
或通过函数实现
as
BEGIN
declare @cmd nvarchar(500)
set
@cmd=
'select
UserName as UserName,
LoggerDateTime as LoggerDateTime,
Operation as Operation,
Complete as Complete,
Res as Res
FROM (
SELECT *,ROW_NUMBER() OVER (order by '+@sort+' '+@dir+')as RowNumber
FROM T_Logger where convert(nvarchar(20),LoggerDateTime,111) between '+ convert(nvarchar(20),@startDate,111)+' AND '+ convert(nvarchar(20),@endDate,111)+') T
WHERE T.RowNumber>'+ cast(@start AS NVARCHAR) +'AND T.RowNumber<='+cast((@start+@num) AS NVARCHAR)
exec(@cmd)
end
这是我用字符串形式写的
BEGIN
select
UserName as UserName,
convert(nvarchar(20),LoggerDateTime,120) as LoggerDateTime,
Operation as Operation,
Complete as Complete,
Res as Res
FROM (
SELECT *,ROW_NUMBER() OVER (order by @sort ASC)as RowNumber
FROM T_Logger where LoggerDateTime between @startDate and @endDate) T
WHERE UserName=@userName and T.RowNumber>@start AND T.RowNumber<=(@start+@num)
end
else
BEGIN
select
UserName as UserName,
LoggerDateTime as LoggerDateTime,
Operation as Operation,
Complete as Complete,
Res as Res
FROM (
SELECT *,ROW_NUMBER() OVER (order by @sort DESC)as RowNumber
FROM T_Logger where LoggerDateTime between @startDate and @endDate) T
WHERE UserName=@userName and T.RowNumber>@start AND T.RowNumber<=(@start+@num)
end这是用非字符串形式写的执行else时desc没起作用