SELECT datediff('m',date(),cdate(left(byny,4) & "-" & right(byny,2) & "-" & "01"))
FROM adddata 上面这个能差到,但是
加条件后就不行了,说“标准表达式中数据类型不匹配”
SELECT datediff('m',date(),cdate(left(byny,4) & "-" & right(byny,2) & "-" & "01"))
FROM adddata
where datediff('m',date(),cdate(left(byny,4) & "-" & right(byny,2) & "-" & "01")) >1
我该怎么办啊。兄弟们。
FROM adddata 上面这个能差到,但是
加条件后就不行了,说“标准表达式中数据类型不匹配”
SELECT datediff('m',date(),cdate(left(byny,4) & "-" & right(byny,2) & "-" & "01"))
FROM adddata
where datediff('m',date(),cdate(left(byny,4) & "-" & right(byny,2) & "-" & "01")) >1
我该怎么办啊。兄弟们。
dim strsql as string
tmpDate=cdate(left(byny,4) & "-" & right(byny,2) & "-" & "01")strsql=" SELECT datediff('m',date(),cdate(left(byny,4) & "-" & right(byny,2) & "-" & "01"))
FROM adddata
where datediff('m',date(),"& tmpDate &") >1"
FROM adddata
where (datediff('m',date(),"'" & cdate(left(byny,4) & "'-'" & right(byny,2) & "'-" & "01")) >1)
试一下上面。
FROM adddata
where datediff('m',date(),"#" & left(byny,4) & "-" & right(byny,2) & "-" & "01#") >1 上面的试试.
SELECT datediff('m',date(),cdate(left(byny,4) & "-" & right(byny,2) & "-" & "01"))
FROM adddata
where datediff('m',date(),cdate(left(byny,4) & "-" & right(byny,2) & "-01")) >1
of123兄弟,子查询也试过了。datediff('m',date(),cdate(left(byny,4) & "-" & right(byny,2) & "-01")) as mm 然后where mm>1 也报同样的错误。
我也觉得是类型不同作大小运算不行,但是在access自带的查询里怎么转换啊,不是在程序里啊。
上面的我试过了,可以的。(CommitTime是我在数据库中的日期型字段)
liuxu2559兄弟,谢谢你的测试,
我和你的区别是byny字段是字符串,而且是6位数,比如200912,我必须加上代表日的两位数,然后再转换成时间字段进行比较。
现在的问题是,我用format转换byny字段时,用SELECT datediff('m',date(),cdate(left(byny,4) & "-" & right(byny,2) & "-" & "01"))
FROM adddata 查询,语法没有错,结果是查询的记录中都是“错误”。而用cdate转时,查询就是对的,能正确计算出月差。
对比您的数据,我感觉唯一的不同是,我没有在where条件中日期型加“#”,下面加了,测试一下还是出错,
SELECT datediff('m',"#"&format(now(),'yyyy-mm-dd')&"#","#"&cdate(left(byny,4) & "-" & right(byny,2) & "-" & "01"))&"#"
FROM adddata where datediff('m',"#"&format(now(),'yyyy-mm-dd')&"#","#"&cdate(left(byny,4) & "-" & right(byny,2) & "-" & "01"))&"#";
我怀疑是cdate问题?但是我不加条件单单查询时没有问题啊?
谢谢。。
只要字符串没有非法日期,试试:select datediff('m',cdate(format(byny,'####-##')),date())
from [adddata]
where datediff('m',cdate(format(byny,'####-##')),date())>1
楼主的语句没有问题,可能是 byny 列有空值楼主用下面的语句试试看
SELECT datediff('m',date(),cdate(left(iif(isnull(byny),'200910',byny),4) & "-" & right(iif(isnull(byny),'200910',byny),2) & "-" & "01"))
FROM adddata
where datediff('m',date(),cdate(left(iif(isnull(byny),'200910',byny),4) & "-" & right(iif(isnull(byny),'200910',byny),2) & "-" & "01")) >1