CREATE TABLE TEST(BIRTHDAY VARCHAR(10))
INSERT TEST VALUES ('2006-1-09')
INSERT TEST VALUES ('2006-8-24')
INSERT TEST VALUES ('')
SELECT * FROM TESTSELECT BIRTHDAY
FROM TEST
WHERE
(BIRTHDAY IS NOT NULL) AND
(BIRTHDAY <> '') AND
(ISDATE(BIRTHDAY)=1 AND MONTH(CONVERT(datetime,BIRTHDAY)) = 1)DROP TABLE TEST
INSERT TEST VALUES ('2006-1-09')
INSERT TEST VALUES ('2006-8-24')
INSERT TEST VALUES ('')
SELECT * FROM TESTSELECT BIRTHDAY
FROM TEST
WHERE
(BIRTHDAY IS NOT NULL) AND
(BIRTHDAY <> '') AND
(ISDATE(BIRTHDAY)=1 AND MONTH(CONVERT(datetime,BIRTHDAY)) = 1)DROP TABLE TEST
所以 (Birthday IS NOT NULL) AND (Birthday <> '') 这个条件可以去除
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
指定style 试试
我想是不是因为字符串的格式有问题。
但是我通过isdate(Birthday) = 1应该已经过滤掉了呀。
郁闷中............
(SELECT Birthday
FROM Customer
WHERE (Birthday IS NOT NULL) AND (Birthday <> '') AND (isdate(Birthday) = 1) ) a
where MONTH(CONVERT(datetime, Birthday)) = 1
(MONTH(CONVERT(datetime, Birthday)) = 1)
sql server好像在(isdate(Birthday) = 1)不满足的情况下依旧会执行下一个判断,也就是
MONTH(CONVERT(datetime, Birthday)) = 1,楼上的可以解决这个问题。