select *
from list
where isdate((CONVERT(varchar(8),year(member_birth)) + '-' + CONVERT(varchar(8), day(member_birth)) + '-' + CONVERT(varchar(8),month(member_birth))))=0
from list
where isdate((CONVERT(varchar(8),year(member_birth)) + '-' + CONVERT(varchar(8), day(member_birth)) + '-' + CONVERT(varchar(8),month(member_birth))))=0
--or try
select *
from list
where isdate(member_birth)=0
以下示例将创建 test_dates 表并插入两个值。示例中将使用 ISDATE 来确定列中的值是否为日期。USE tempdb
CREATE TABLE test_dates (Col_1 varchar(15), Col_2 datetime)
GO
INSERT INTO test_dates VALUES ('abc', 'July 13, 1998')
GO
SELECT ISDATE(Col_1) AS Col_1, ISDATE(Col_2) AS Col_2
FROM test_dates
下面是结果集:
Col_1 Col_2
----------------- --------------------
0 1
(DAY(member_birth) <= 12) 去掉那些 日期大于12 的啊?
SELECT *
FROM list
WHERE (isdate(CONVERT(varchar(8), YEAR(member_birth)) + '- ' + CONVERT(varchar(8),
DAY(member_birth)) + ' - ' + CONVERT(varchar(8), MONTH(member_birth))) = 0) AND
(member_id IN
(SELECT member_id
FROM list
WHERE (DAY(member_birth) <= 12) AND ((created_by = 'pull_script ') AND
(DATEDIFF([day], '2008/12/31', created_date_time) > 1)) OR
((DATEDIFF([day], '2008/12/31', updated_date_time) > 1) AND
(updated_by = 'pull_script'))))
请指正啊!
FROM list
WHERE (DAY(member_birth) <= 12) AND (isdate(CONVERT(varchar(8), YEAR(member_birth))
+ '- ' + CONVERT(varchar(8), DAY(member_birth)) + ' - ' + CONVERT(varchar(8),
MONTH(member_birth))) = 0) AND (created_by = 'pull_script ') AND (DATEDIFF([day],
'2008/12/31', created_date_time) > 1) OR
(DATEDIFF([day], '2008/12/31', updated_date_time) > 1) AND
(updated_by = 'pull_script')
结果没有数据显示。说明所有的数据都是对的。
但是update 的时候为什么会说out_of _range啊?
(DAY(member_birth) <= 12)
and ((created_by = 'pull_script ')
AND (DATEDIFF([day], '2008/12/31',created_date_time) > 1)
)
OR (
(DATEDIFF([day], '2008/12/31', updated_date_time) > 1)
AND (updated_by = 'pull_script')
)
)
楼主的括号有些多了,呵呵。
UPDATE list SET member_birth = CONVERT(datetime,(CONVERT(varchar(8),year(member_birth)) + '-' + CONVERT(varchar(8), day(member_birth)) + '-' + CONVERT(varchar(8),month(member_birth))))
WHERE(
(DAY(member_birth) <= 12)
and (
(created_by = 'pull_script ') AND (DATEDIFF([day], '2008/12/31',
created_date_time) > 1)
)
OR (
(DATEDIFF([day], '2008/12/31', updated_date_time) > 1) AND
(updated_by = 'pull_script')
)
)
)
)