以文本方式查看主题

-  Foxtable(狐表)  (http://foxtable.net/bbs/index.asp)
--  专家坐堂  (http://foxtable.net/bbs/list.asp?boardid=2)
----  [求助]日期范围选择  (http://foxtable.net/bbs/dispbbs.asp?boardid=2&id=136464)
--  作者：lgj716330
--  发布时间：2019/6/13 19:47:00
--  [求助]日期范围选择
selec t acctdate as [日期],period as [会计期间],debit as [借方金额],credit as [贷方金额],projectid as [核算项目] from {cn_acctbook} where isdelete = 0 and acctdate > (表中最近一天日期所在年份上一年1月1号)

括号中的日期应该如何表达，求助
--  作者：有点甜
--  发布时间：2019/6/13 21:42:00
--  

1、你说的最近的日期，指什么？和哪个值比较？

 

2、上传具体实例测试。
--  作者：lgj716330
--  发布时间：2019/6/14 8:37:00
--  
暂时先按这样解决了
selec t acctdate as [日期],period as [会计期间],debit as [借方金额],credit as [贷方金额],projectid as [核算项目] from {cn_acctbook} where isdelete = 0 and acctdate > dateadd(yy,-1,getdate())
[此贴子已经被作者于2019/6/14 8:47:52编辑过]
--  作者：lgj716330
--  发布时间：2019/6/14 9:25:00
--  
已解决
selec t acctdate as [日期],period as [会计期间],debit as [借方金额],credit as [贷方金额],projectid as [核算项目] from {cn_acctbook} where isdelete = 0 and datepart(yy,acctdate) >= datepart(yy,getdate())-1
--  作者：lgj716330
--  发布时间：2019/6/14 12:26:00
--  
Dim Filter As String = "1=1"
With e.Form.Controls("RQ1") 
    If .Value IsNot Nothing Then
        If Filter > "" Then
            Filter = Filter & " And "
        End If
        Filter = Filter & "acctdate >= \'" & .Value & "\'"
    End If
End With
With e.Form.Controls("RQ2") 
    If .Value IsNot Nothing Then
        If Filter > "" Then
            Filter = Filter & " And "
        End If
        Filter = Filter & "acctdate <= \'" & .Value & "\'"
    End If
End With

cmd.CommandText = "selec t acctdate as [日期],period as [会计期间],debit as [借方金额],credit as [贷方金额],debit-credit as [金额],projectname as [核算项目] from {cn_acctbook} left outer join {cn_project} on {cn_acctbook}.projectid = {cn_project}.id where isdelete = 0 and datepart(yy,acctdate) >= datepart(yy,getdate())-1"
如何将标颜色的日期范围用上面的筛选条件替换
--  作者：有点甜
--  发布时间：2019/6/14 12:42:00
--  

参考

 

cmd.CommandText = "selec t acctdate as [日期],period as [会计期间],debit as [借方金额],credit as [贷方金额],debit-credit as [金额],projectname as [核算项目] from {cn_acctbook} left outer join {cn_project} on {cn_acctbook}.projectid = {cn_project}.id where isdelete = 0 and " & filter