Sql Filter between two dates - ADO

Sql Filter between two dates - ADO

Postby Rick Lipkin » Sat Jun 24, 2023 7:16 pm

To All

I have a Access sql table that has two datetime fields HDATE and LDATE and I have a button where when I click the button I can set a filter between the two dates

Ldate := ctod("01/01/2023")
Hdate := ctod("01/31/2023")

oRs:Filter := ""
oRsFilter := oRs:Fields("Ldate"):Value >= lDate .and. oRs:Fields("Hdate"):Value <= hDate

I have not tried this code but I don't think it will work ?? any ideas ??

Rick Lipkin
User avatar
Rick Lipkin
 
Posts: 2663
Joined: Fri Oct 07, 2005 1:50 pm
Location: Columbia, South Carolina USA

Re: Sql Filter between two dates - ADO

Postby nageswaragunupudi » Sun Jun 25, 2023 3:36 am

If you are working with MSACCESS
Code: Select all  Expand view
oRs:Filter := "LDATE >= #2023-01-01# AND HDATE < #2023-02-01#"


If you are working with Microsoft SQL
Code: Select all  Expand view
oRs:Filter := "LDATE >= '2023-01-01' AND HDATE < '2023-02-01'"


Note: It is safer to use "HDATE < (date + 1 )" than "HDATE <= (date)", because many RDBMS use datetime fields.

How to easily generate these expressions?
We better make it a habit to open an ADO connection using FW_OpenAdoConnection() of FWH instead of opening on our own with ADODB.Connection.
If we open using FWH function FW_OpenAdoConnection, all the other ADO functions know the RDMS used and function in a say appropriate to the RDMBS.

Please try this:
Code: Select all  Expand view
  lDate    := STOD( "20230101" )
   hDate    := STOD( "20230131" )
   oCn   := FW_OpenAdoConnection( "xbrtest.mdb" )
   ? C := FW_AdoApplyParams( "LDATE >= ? AND HDATE < ?", { lDate, hDate + 1 } )
   oCn:Close()

   oCn   := FW_MSSQLDB()
   ? C := FW_AdoApplyParams( "LDATE >= ? AND HDATE < ?", { lDate, hDate + 1 } )
 
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10620
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: Sql Filter between two dates - ADO

Postby Rick Lipkin » Tue Jun 27, 2023 3:54 pm

Rao

Here was my solution .. been so long since I have worked in MS Access ..... I forgot about having to use the the hash tag .. here is my working solution...

Thanks
Rick Lipkin

Code: Select all  Expand view

oRsTrips:Filter := ""
oRsTrips:Filter := "[PICKUPDATE] >= #"+dtoc(lDate)+"# and [PICKUPDATE] <= #"+dtoc(hDate)+"#"

Try
   oRsTrips:MoveFirst()
Catch
End Try

oLbxA:MakeTotals()
oLbxA:ReFresh()

SysReFresh()

If oRsTrips:Eof
   Saying := "Sorry .. there were no records found for dates "
   Saying += dtoc(lDate)+"  thru  "+dtoc(hDate)

   Msginfo( Saying )

   oButt5:CLick()   // view all
Endif

Return(nil)

 
User avatar
Rick Lipkin
 
Posts: 2663
Joined: Fri Oct 07, 2005 1:50 pm
Location: Columbia, South Carolina USA

Re: Sql Filter between two dates - ADO

Postby Armando » Wed Jun 28, 2023 4:29 pm

Tim and friends:

Here is an other way, using the BETWEEN clause.

Code: Select all  Expand view

                oRsPag:Filter := "pro_nom LIKE " + "'%" + cFilNom + "%'" +;
                                            "AND " +;
                                                "det_fdp BETWEEN '" + DToS(dFecIni) + " AND " + DToS(dFecFin) + "' " +;
                                            "AND " +;
                                        "pro_nom LIKE " + "'%" + cFilNom + "%'"
 


Best regards
SOI, s.a. de c.v.
estbucarm@gmail.com
http://www.soisa.mex.tl/
http://sqlcmd.blogspot.com/
Tel. (722) 174 44 45
Carpe diem quam minimum credula postero
User avatar
Armando
 
Posts: 3201
Joined: Fri Oct 07, 2005 8:20 pm
Location: Toluca, México


Return to FiveWin for Harbour/xHarbour

Who is online

Users browsing this forum: Google [Bot] and 39 guests

cron