topic-43369

Board index FiveWin for Harbour/xHarbour Sql Filter between two dates - ADO

Posts: 2706

Joined: Fri Oct 07, 2005 01:50 PM

Sql Filter between two dates - ADO
Posted: Sat Jun 24, 2023 07: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

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Sql Filter between two dates - ADO
Posted: Sun Jun 25, 2023 03:36 AM

If you are working with MSACCESS

Code (fw): Select all Collapse

oRs:Filter := "LDATE >= #2023-01-01# AND HDATE < #2023-02-01#"

If you are working with Microsoft SQL

Code (fw): Select all Collapse

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 (fw): Select all Collapse

   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

Rick Lipkin

Posts: 2706

Joined: Fri Oct 07, 2005 01:50 PM

Re: Sql Filter between two dates - ADO
Posted: Tue Jun 27, 2023 03: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 (fw): Select all Collapse

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)

Armando

Posts: 3358

Joined: Fri Oct 07, 2005 08:20 PM

Re: Sql Filter between two dates - ADO
Posted: Wed Jun 28, 2023 04:29 PM

Tim and friends:

Here is an other way, using the BETWEEN clause.

Code (fw): Select all Collapse

                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

FiveTech Support Forums

Continue the discussion