27,579
社区成员
发帖
与我相关
我的任务
分享
--test data
If object_id('#') is not NULL
drop table #
Go
--建立测试表
Create table # (shijian datetime,ID int IDENTITY)
Go
--录入测试数据
Insert into #
select getdate()-340 Union ALL --录入2008-1-1
select getdate()-(340-31) Union ALL --录入2008-2-1
select getdate()-(340-31-29) Union ALL --录入2008-3-1
select getdate()-(340-31-29-31) Union ALL --录入2008-4-1
select getdate()-(340-31-29-31-30) Union ALL --录入2008-5-1
select getdate()-(340-31-29-31-30-31) Union ALL --录入2008-6-1
select getdate()-(340-31-29-31-30-31-30) Union ALL --录入2008-7-1
select getdate()-(340-31-29-31-30-31-30-31) Union ALL --录入2008-8-1
select getdate()-(340-31-29-31-30-31-30-31-31) Union ALL --录入2008-9-1
select getdate()-(340-31-29-31-30-31-30-31-31-30) Union ALL --录入2008-10-1
select getdate()-(340-31-29-31-30-31-30-31-31-30-31) Union ALL --录入2008-11-1
select getdate()-(340-31-29-31-30-31-30-31-31-30-31-30) --录入2008-12-1
--select * from #
--测试不同语句显示结果
select *
from #
where convert(varchar(100),shijian,23) >= '2008-01-01'
/*
shijian ID
2008-01-01 15:25:20.030 1
2008-02-01 15:25:20.030 2
2008-03-01 15:25:20.030 3
2008-04-01 15:25:20.030 4
2008-05-01 15:25:20.030 5
2008-06-01 15:25:20.030 6
2008-07-01 15:25:20.030 7
2008-08-01 15:25:20.030 8
2008-09-01 15:25:20.030 9
2008-10-01 15:25:20.030 10
2008-11-01 15:25:20.030 11
2008-12-01 15:25:20.030 12
*/
select *
from #
where convert(varchar(100),shijian,23) >= '2008-1-1'
/*
shijian ID
2008-11-01 15:25:20.030 11
2008-12-01 15:25:20.030 12
*/
use tempdb
go
declare @s varchar(20)
declare @s1 varchar(20)
declare @s2 varchar(20)
set @s = '2008-1-1'
set @s1 = '2008-10-1'
set @s2 = '2008-11-1'
if @s1 >= @s
print '"2008-10-1" >= "2008-1-"'
else
print '"2008-10-1" < "2008-1-"'
if @s2 >= @s
print '"2008-11-1" >= "2008-1-"'
else
print '"2008-11-1" < "2008-1-"'
/**
"2008-10-1" < "2008-1-"
"2008-11-1" >= "2008-1-"
**/