现有以下 WISH_SMS 表中的数据 birthday 是 smalldatetime 数据类型name sex birthday
丁丽君 2 1967-06-16 00:00:00.0
师占卿 1 1900-01-01 00:00:00.0
杨润成 1 1900-01-01 00:00:00.0
白拉秀 2 1940-06-16 00:00:00.0找出生日: 06-16 的人 即1,4两条记录我用:sql模糊查询:select * from WISH_SMS where W_BIRTHDAY like '%06-16%'查询不到记录...请各位指教?
丁丽君 2 1967-06-16 00:00:00.0
师占卿 1 1900-01-01 00:00:00.0
杨润成 1 1900-01-01 00:00:00.0
白拉秀 2 1940-06-16 00:00:00.0找出生日: 06-16 的人 即1,4两条记录我用:sql模糊查询:select * from WISH_SMS where W_BIRTHDAY like '%06-16%'查询不到记录...请各位指教?
where CONVERT(VARCHAR(10),W_BIRTHDAY,120) like '%06-16%'
and datepart(d,W_BIRTHDAY )=16
DECLARE @t TABLE(ID int,Name varchar(10),Birthday datetime)
INSERT @t SELECT 1,'aa','1999-01-01'
UNION ALL SELECT 2,'bb','1996-02-29'
UNION ALL SELECT 3,'bb','1934-03-01'
UNION ALL SELECT 4,'bb','1966-04-01'
UNION ALL SELECT 5,'bb','1997-05-01'
UNION ALL SELECT 6,'bb','1922-11-21'
UNION ALL SELECT 7,'bb','1989-12-11'DECLARE @dt1 datetime,@dt2 datetime--查询 2003-12-05 至 2004-02-28 生日的记录
SELECT @dt1='2003-12-05',@dt2='2004-02-28'
SELECT * FROM @t
WHERE DATEADD(Year,DATEDIFF(Year,Birthday,@dt1),Birthday)
BETWEEN @dt1 AND @dt2
OR DATEADD(Year,DATEDIFF(Year,Birthday,@dt2),Birthday)
BETWEEN @dt1 AND @dt2
/*--结果
ID Name Birthday
---------------- ---------------- --------------------------
1 aa 1999-01-01 00:00:00.000
7 bb 1989-12-11 00:00:00.000
--*/--查询 2003-12-05 至 2006-02-28 生日的记录
SET @dt2='2006-02-28'
SELECT * FROM @t
WHERE DATEADD(Year,DATEDIFF(Year,Birthday,@dt1),Birthday)
BETWEEN @dt1 AND @dt2
OR DATEADD(Year,DATEDIFF(Year,Birthday,@dt2),Birthday)
BETWEEN @dt1 AND @dt2
/*--查询结果
ID Name Birthday
---------------- ----------------- --------------------------
1 aa 1999-01-01 00:00:00.000
2 bb 1996-02-29 00:00:00.000
7 bb 1989-12-11 00:00:00.000
--*/
1楼sql语句不行呀..报错了..
[Error Code: 281, SQL State: S1000] 120 is not a valid style number when converting from DATETIME to a character string.
-- Author : liangCK 小梁 & angellan 兰儿
-- Comment: 小梁 爱 兰儿
-- Date : 2008-10-16 15:42:39
------------------------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (name VARCHAR(6),sex INT,birthday SMALLDATETIME)
INSERT INTO @T
SELECT '丁丽君',2,'1967-06-16 00:00:00.0' UNION ALL
SELECT '师占卿',1,'1900-01-01 00:00:00.0' UNION ALL
SELECT '杨润成',1,'1900-01-01 00:00:00.0' UNION ALL
SELECT '白拉秀',2,'1940-06-16 00:00:00.0'--SQL查询如下:select * from @T
where CONVERT(VARCHAR(10),birthday,120) like '%06-16%'/*
name sex birthday
------ ----------- -----------------------
丁丽君 2 1967-06-16 00:00:00
白拉秀 2 1940-06-16 00:00:00(2 行受影响)*/
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-06-16 15:47:06
----------------------------------------------------------------
--> 测试数据:[WISH_SMS]
if object_id('[WISH_SMS]') is not null drop table [WISH_SMS]
create table [WISH_SMS]([name] varchar(6),[sex] int,[birthday] datetime)
insert [WISH_SMS]
select '丁丽君',2,'1967-06-16 00:00:00.0' union all
select '师占卿',1,'1900-01-01 00:00:00.0' union all
select '杨润成',1,'1900-01-01 00:00:00.0' union all
select '白拉秀',2,'1940-06-16 00:00:00.0'
------开始查询:select * from [WISH_SMS] where CONVERT(VARCHAR(10),BIRTHDAY,120) like '%06-16%'
------查询结果:
/*-------------------------------------------------------------
name sex birthday
丁丽君 2 1967-06-16 00:00:00.000
白拉秀 2 1940-06-16 00:00:00.000*/-------------------------------------------------------------
where CONVERT(VARCHAR(10),W_BIRTHDAY,120) like '%06-16%'
10 | 110 美国 | mm-dd-yy
------------------------------------------------------------------------------------------------------------
11 | 111 日本 | yy/mm/dd
------------------------------------------------------------------------------------------------------------
12 | 112 ISO | yymmdd
用110这个就可以了 select * from WISH_SMS where CONVERT(VARCHAR(10),W_BIRTHDAY,110) like '%06-16%'
谢谢了.
insert into @tb
select '丁丽君',2,'1967-06-16 00:00:00.0' union all
select '师占卿',1,'1900-01-01 00:00:00.0' union all
select '杨润成',1,'1900-01-01 00:00:00.0' union all
select '白拉秀',2,'1940-06-16 00:00:00.0'select * from @tb where datepart(mm,birthday )=6 and datepart(d,birthday )=16name sex birthday
------ ----------- -----------------------
丁丽君 2 1967-06-16 00:00:00
白拉秀 2 1940-06-16 00:00:00(2 行受影响)
where CONVERT(VARCHAR(10),BIRTHDAY,126) like '%06-16%'
where substring(CONVERT(VARCHAR(10),W_BIRTHDAY,120),6,5)='0616'