select * from tb where month(日期)=7 and datepart(dd,日期)=5
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB GO CREATE TABLE TB(COL1 VARCHAR(50)) INSERT INTO TB SELECT '2008-07-05' UNION ALL SELECT '2008-07-06' UNION ALL SELECT '2009-07-05' UNION ALL SELECT '2010-07-05'SELECT * FROM TB WHERE COL1 LIKE '%07-05%' /* 2008-07-05 2009-07-05 2010-07-05 */
---测试数据--- if object_id('[tb]') is not null drop table [tb] go create table [tb]([日期] datetime) insert [tb] select '2008-07-05' union all select '2008-07-06' union all select '2009-07-05' union all select '2010-07-05'
---查询--- select * from tb where convert(varchar(10),日期,120) like '%07-05%'---结果--- 日期 ------------------------------------------------------ 2008-07-05 00:00:00.000 2009-07-05 00:00:00.000 2010-07-05 00:00:00.000(所影响的行数为 3 行)
--> 测试数据: @tb declare @tb table (date datetime) insert into @tb select '2008-07-05' union all select '2008-07-06' union all select '2009-07-05' union all select '2010-07-05'
select * from @tb where convert(varchar(10),date,112) like '%0705%' date ----------------------- 2008-07-05 00:00:00.000 2009-07-05 00:00:00.000 2010-07-05 00:00:00.000(3 行受影响)
模糊查询条件为 2001-01-01 00:00:01 and 2001-12-30 23:59:59
from tb
where month(日期)=7 and datepart(dd,日期)=5
GO
CREATE TABLE TB(COL1 VARCHAR(50))
INSERT INTO TB
SELECT '2008-07-05' UNION ALL
SELECT '2008-07-06' UNION ALL
SELECT '2009-07-05' UNION ALL
SELECT '2010-07-05'SELECT * FROM TB
WHERE COL1 LIKE '%07-05%'
/*
2008-07-05
2009-07-05
2010-07-05
*/
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([日期] datetime)
insert [tb]
select '2008-07-05' union all
select '2008-07-06' union all
select '2009-07-05' union all
select '2010-07-05'
---查询---
select *
from tb
where convert(varchar(10),日期,120) like '%07-05%'---结果---
日期
------------------------------------------------------
2008-07-05 00:00:00.000
2009-07-05 00:00:00.000
2010-07-05 00:00:00.000(所影响的行数为 3 行)
declare @tb table (date datetime)
insert into @tb
select '2008-07-05' union all
select '2008-07-06' union all
select '2009-07-05' union all
select '2010-07-05'
select * from @tb where convert(varchar(10),date,112) like '%0705%'
date
-----------------------
2008-07-05 00:00:00.000
2009-07-05 00:00:00.000
2010-07-05 00:00:00.000(3 行受影响)