ACCESS转SQL后的时间计算问题。急急急···

来源：百度知道 编辑：UC知道 时间：2024/09/21 14:54:33
代码如下：
-------------------------------------------------------------------
今日： <font color="#0099FF"> <%=Conn.Execute("Select count([id]) From [FY_title] Where [time] like '%"& date() &"%' And [title_del] = 0 And [title_show] = 0 ")(0)%> </font> 昨日： <font color="#0099FF"> <%=Conn.Execute("Select count([id]) From [FY_title] Where [time] like '%"& date() - 1 &"%' And [title_del] = 0 And [title_show] = 0 ")(0)%> </font>
--------------------------------------------------------------------
为什么这段语句在ACCESS数据库下好使。在SQL就不行呢？
连接字符串没问题。其他功能正常。
[time]改成
cast([time] as varchar(30)) 或 convert(varchar(10),[time],120)='"& date() &"' 虽然可以正常显示，但是问题没解决。都显示成0
这是个论坛的发帖统计。今日多少贴和昨日多少贴
那位大侠帮忙改下

SQL 不支持datetime字段用 like 通配符的!

改为:
今日:<%=Conn.Execute("Select count([id]) From [FY_title] Where convert( int, [time])=convert( int, getdate() ) And [title_del] = 0 And [title_show] = 0 ")(0)%>

昨日:<%=Conn.Execute("Select count([id]) From [FY_title] Where convert( int, [time])=convert( int, getdate() )-1 And [title_del] = 0 And [title_show] = 0 ")(0)%>

cast([time] as varchar(30)) 或 convert(varchar(10),[time],120)
是出 "yyyy-mm-dd" 格式的, asp 的 date() 是出 "yyyy/m/d"格式的，
当然有机会查不出数据！你再转换下格式还是可以的。

不过对于SQL数据库，整数处理是最快的！

Select count([id]) From [FY_title] Where datediff(D,[time],getdate()=0 And [title_del] = 0 And [title_show] = 0 ")(0
<%=Conn.Execute("Select count([id]) From [FY_title] Where [time] like datediff(D,[time],getdate()=1 And [title_del] = 0 And [title_show] = 0 ")(0)%>

数据类型改为datatime