Operaciones con fechas en ADO

[Rick Lipkin]

Pedro

Your connection string is for MySql and not MS Access

Code: Select all

Local cStr     := "Driver={MySQL ODBC 3.51 Driver};Server=localhost;Port=3306;Database=" + "BASE_DE_DATOS" + ";User=" + "USUARIO" + ";Password=" + "12345"+ ";Option=3;"
   Local oReturn  := tOleAuto():New("ADODB.connection")
 

Are you trying to open this recordset from MySql or Ms Access or perhaps you need to open both ?

Rick

[Pedro]

Rick
The MySQL connection is Simon, not mine, he gave as an example of how to connect to the database and how to make the connection to the recordset, it has nothing to do with the issue of dates.
A question Rick, have you tried with Acces to make a filter, or search for two dates in a date field whose format is short date?
I'll try to do a self-contained module and I'll send it if you show me an e-mail

[Rick Lipkin]

Pedro

I looked up the definition of a 'short date' in Ms Access and I found this link..

http://www.techonthenet.com/access/func ... format.php

I am not familiar with using 'short dates' and according the the above link .. there are 'formatting' options..

I would like to see your Ms Access Database if you do not mind sending it to me via e-mail. If the Access ( .mdb ) is over a few mg .. I would suggest you send it via a file hosting web site like YouSendIt.com.

Also, if the database is password protected .. I would need to know that information as well. Hopefully the Access database is a standard .mdb and not part of a 'workgroup'.

My e-mail address is R1.1955@Live.com .. I will do my best to see what I can do to help you.

Rick Lipkin

[Pedro]

Bueno, como ya le he comentado a Rick por e mail las modificaciones que ha hecho en el ejemplo autocontenido han funcionado a excepción de que el recordset siempre trae el primer registro sin atender a la fecha inicial que se pide.
A ver si algún gurú puede indicarme como hacer para que pueda tomar las fechas correctamente.

Aqui os pongo el ejemplo y el rd, podeis crearos una tabla de ACCESS con _ que aparecen en la función REPLISVTO, en concreto los de nOption = 2 que se introducen en el arreglo aVtos

Code: Select all

#Include "FiveWin.ch"
#Include "Xbrowse.ch"
*#Include "Adodef.ch"
#Include "Dtpicker.ch"
#Include "Report.ch"
*-------------------------------------------------------------------------------
FUNCTION Main()
*-------------------------------------------------------------------------------
Local oWMain

PUBLIC oConex, cArea

SET DATE TO ITALIAN
SET CENTURY ON
SET 3DLOOK ON
REQUEST HB_LANG_ES
SetDialogEsc(.f.)
HB_LangSelect('ES')
HB_SetCodePage("ESMWIN")

SetBalloon( .T. )

DEFINE WINDOW oWMain MDI FROM 02, 02 TO ( MaxRow() - 2 ), ( MaxCol() - 10 );
          TITLE  "ADO with Test Dates"  ;
          COLOR RGB(0,0,51),RGB(255,255,255);
          MENU MainMenu()

ACTIVATE WINDOW oWMain MAXIMIZED ;
               ON INIT EscogeArea();
                VALID MsgYesNo( "Finalizar sesion?","Elija" )


return( nil )
*===============================================================================
Function MainMenu()
   MENU oMenu 2007
          MENUITEM "Vencimientos"  ACTION VENCTOS()
   ENDMENU

return( oMenu )
*===============================================================================
function EscogeArea()
*===============================================================================
Local cDir
Local lConecta,nStart

cDir := GetModuleFileName(GetInstance())
  

nSTART := RAT( "\", cDir )
cArea  := SUBSTR(cDir,1,nSTART-1)

SET DEFA to ( cArea )
Set Path to ( cArea )

lConecta :=   Abretablas()
If !lConecta
    oWMain:End()
EndIf

Return nil
*-------------------------------------------------------------------------------
FUNCTION AbreTABLAS()
*-------------------------------------------------------------------------------
//  oConex is just the connection STRING
//  to be passed to the Recordset :Open()
//  See REPLISVTO()


Local lConecta := .T.

Local xPROVIDER := "Microsoft.Jet.OLEDB.4.0"
Local cSource  := cArea+"\Datos.mdb"

oConex  := 'Provider='+xPROVIDER+';Data Source='+cSOURCE 

Return(lConecta)
/*
*===============================================================================
Function ShowError(oError)
       MsgInfo( "Descripción : "  + oError:Description      + CRLF + ;
                "Error Nativo : " + Str(oError:NativeError) + CRLF + ;
                "Número Error : " + Str(oError:Number)      + CRLF + ;
                "Origen : "       + oError:Source           + CRLF + ;
                "Estado SQL : "   + oError:SQLState  )
Return nil

*-------------------------------------------------------------------------------
Function FW_OpenRecordSet( oCn, cSql, nLockType )
*-------------------------------------------------------------------------------
Local oRs,oError

   DEFAULT nLockType       := 3 // adLockOptimistic

   oRs   := TOleAuto():new( "ADODB.RecordSet" )

   WITH OBJECT oRs
      :ActiveConnection    := oCn
      :Source              := cSql
      :LockType            := nLockType
      :CursorLocation      := 3            // adUseClient
      :CacheSize           := 100
   END

   TRY
      oRs:Open()
   CATCH
      FOR EACH oError IN oConex:Errors
          ShowError(oError)
      NEXT
      oRs                  := nil
   END

return oRs
*/

*-------------------------------------------------------------------------------
 Function Venctos()
*-------------------------------------------------------------------------------
Local oDlgVtos
Local dInicio := Ctod("01/02/2012"), dFinal := Date()
Local oRadVto,nOpcion := 1

DEFINE DIALOG oDlgVtos RESOURCE "LISVTOS"

      REDEFINE RADIO oRadVto VAR nOpcion ID 4005,4006 OF oDlgVtos UPDATE

      REDEFINE DTPicker dInicio   ID 4007 PICTURE "@D" OF oDlgVtos UPDATE
      REDEFINE DTPicker dFinal    ID 4008 PICTURE "@D" OF oDlgVtos UPDATE

      REDEFINE BUTTONBMP ID 221 OF oDlgVtos BITMAP "ACEPTAR" TEXTRIGHT  ;
               ACTION REPLISVTO(nOpcion,dInicio,dFinal)

      REDEFINE BUTTONBMP ID 223 OF oDlgVtos BITMAP "SALIR1" TEXTRIGHT CANCEL ACTION oDlgVtos:End()

ACTIVATE DIALOG oDlgVtos CENTER

SysRefresh()
Return nil

*-------------------------------------------------------------------------------
Function REPLISVTO(nOption,dInicio,dFinal)
*-------------------------------------------------------------------------------
Local aVtos := {}
Local oRsLisVto
Local oError
Local cSource
Local dDini 
Local dDfin 

dDini := dInicio
dDfin := dFinal

If Dtos(dInicio)> Dtos(dFinal)
   MsgStop("Parámetros de fechas incorrectos","ATENCION")
   Return nil
EndIf

oRsLisVto := TOleAuto():New( "ADODB.Recordset" )
oRsLisVto:CursorType     := 1        // opendkeyset
oRsLisVto:CursorLocation := 3        // local cache
oRsLisVto:LockType       := 3        // lockoportunistic

cSource := "SELECT * from [Venctos] where [Vencto] "
cSource += "between #"+dtoc(dDini)+"# and #"+dtoc(dDfin)+"# Order by [Vencto]"



TRY
   oRsLisVto:Open( cSource,oConex )
CATCH oErr
   MsgInfo( "Error in Opening Venctos table" )
   RETURN(.f.)
END TRY

If oRsLisVto:eof
   MsgInfo( "Sorry .. no Rows Found" )
   oRsLisVto:CLose()
   Return(nil)
Endif
   

If nOption == 1

   oRsLisVto:MoveFirst()

   While !oRsLisVto:Eof()

      If oRsLisVto:Fields("Pagado"):Value == .F.
         AADD(aVtos,{oRsLisVto:Fields("FECHA"):Value,;
                     oRsLisVto:Fields("FACTURA"):Value,;
                     oRsLisVto:Fields("CODIGO"):Value,;
                     oRsLisVto:Fields("NOMBRE"):Value,;
                     oRsLisVto:Fields("VENCTO"):Value,;
                     oRsLisVto:Fields("IMPORTE"):Value  })
      EndIf
      oRsLisVto:MoveNext()
   EndDo

Else

   oRsLisVto:MoveFirst()
   While !oRsLisVto:Eof()

      If oRsLisVto:Fields("Pagado"):Value == .T.
         AADD(aVtos,{oRsLisVto:Fields("FECHA"):Value,;
                     oRsLisVto:Fields("FACTURA"):Value,;
                     oRsLisVto:Fields("CODIGO"):Value,;
                     oRsLisVto:Fields("NOMBRE"):Value,;
                     oRsLisVto:Fields("VENCTO"):Value,;
                     oRsLisVto:Fields("IMPORTE"):Value,;
                     oRsLisVto:Fields("FPAGO"):Value,;
                     oRsLisVto:Fields("TIPOPAGO"):Value })
      EndIf
      oRsLisVto:MoveNext()
   EndDo

EndIf

If nOption == 1
   REPORTNOPAGADAS(aVtos,dInicio,dFinal)
Else
   REPORTPAGADAS(aVtos,dInicio,dFinal)
EndIf

oRsLisVto:Close()

Return nil
*-------------------------------------------------------------------------------
FUNCTION REPORTNOPAGADAS(aVtos,dInicio,dFinal)
*-------------------------------------------------------------------------------
LOCAL oFont1, oFont2, oPen1
Local oReport
Local n := 1

     DEFINE FONT oFont1 NAME "TAHOMA" SIZE 0,-10
     DEFINE FONT oFont2 NAME "TAHOMA" SIZE 0,-8

     DEFINE PEN oPen1 WIDTH 1 COLOR CLR_BLACK

     PrinterSetup()

     REPORT oReport TITLE  "LISTADO DE VENCIMIENTOS DE FACTURAS","",;
                           "DESDE EL "+DTOC(dInicio)+"       "+"HASTA EL "+DTOC(dFinal);
          FONT   oFont1, oFont2 ;
          PEN    oPen1 ;
          HEADER "Fecha: "+dtoc(date()),"","Página:"+Str(oReport:nPage,3) RIGHT ;
          PREVIEW

          COLUMN TITLE "FECHA" ;
          DATA DTOC(aVtos[n,1]) ;
          FONT 2  ;
          GRID 1

          COLUMN TITLE "FACTURA" ;
          DATA aVtos[n,2] ;
          FONT 2  ;
          GRID 1

          COLUMN TITLE "CODIGO" ;
          DATA aVtos[n,3];
          FONT 2  ;
          GRID 1

          COLUMN TITLE "NOMBRE" ;
          DATA aVtos[n,4] ;
          FONT 2  ;
          GRID 1

          COLUMN TITLE "VENCIMIENTO" ;
          DATA DTOC(aVtos[n,5]) ;
          FONT 2  ;
          GRID 1

          COLUMN TITLE "IMPORTE" ;
          DATA aVtos[n,6] ;
          PICTURE "99,999.99" ;
          TOTAL ;
          FONT 2  ;
          GRID 1

          oReport:bSkip := {|| n++}
          oReport:nTitleUpLine := RPT_SINGLELINE
          oReport:nTitleDnLine := RPT_SINGLELINE
  END REPORT
  oReport:CellView()


  ACTIVATE REPORT oReport  WHILE (n <= Len(aVtos) )
     oFont1:End()
     oFont2:End()

     oPen1:End()
     Set Default to (cArea)
 RETURN NIL
*-------------------------------------------------------------------------------
FUNCTION REPORTPAGADAS(aVtos,dInicio,dFinal)
*-------------------------------------------------------------------------------
LOCAL oFont1, oFont2, oPen1
Local oReport
Local n := 1

     DEFINE FONT oFont1 NAME "TAHOMA" SIZE 0,-10
     DEFINE FONT oFont2 NAME "TAHOMA" SIZE 0,-8

     DEFINE PEN oPen1 WIDTH 1 COLOR CLR_BLACK

     PrinterSetup()

     REPORT oReport TITLE  "LISTADO DE VENCIMIENTOS PAGADOS","",;
                           "DESDE EL "+DTOC(dInicio)+"       "+"HASTA EL "+DTOC(dFinal);
          FONT   oFont1, oFont2 ;
          PEN    oPen1 ;
          HEADER "Fecha: "+dtoc(date()),"","Página:"+Str(oReport:nPage,3) RIGHT ;
          PREVIEW

          COLUMN TITLE "FECHA" ;
          DATA DTOC(aVtos[n,1]) ;
          FONT 2  ;
          GRID 1

          COLUMN TITLE "FACTURA" ;
          DATA aVtos[n,2] ;
          FONT 2  ;
          GRID 1

          COLUMN TITLE "CODIGO" ;
          DATA aVtos[n,3];
          FONT 2  ;
          GRID 1

          COLUMN TITLE "NOMBRE" ;
          DATA aVtos[n,4] ;
          FONT 2  ;
          GRID 1

          COLUMN TITLE "VENCIMIENTO" ;
          DATA DTOC(aVtos[n,5]) ;
          FONT 2  ;
          GRID 1

          COLUMN TITLE "IMPORTE" ;
          DATA aVtos[n,6] ;
          PICTURE "99,999.99" ;
          TOTAL ;
          FONT 2  ;
          GRID 1

          COLUMN TITLE "FECHA PAGO" ;
          DATA DTOC(aVtos[n,7]) ;
          FONT 2  ;
          GRID 1

          COLUMN TITLE "TIPO PAGO" ;
          DATA aVtos[n,8] ;
          FONT 2  ;
          GRID 1


          oReport:bSkip := {|| n++}
          oReport:nTitleUpLine := RPT_SINGLELINE
          oReport:nTitleDnLine := RPT_SINGLELINE
  END REPORT
  oReport:CellView()


  ACTIVATE REPORT oReport  WHILE (n <= Len(aVtos) )
     oFont1:End()
     oFont2:End()

     oPen1:End()

 RETURN NIL

// end

RC

Code: Select all

LISVTOS DIALOG DISCARDABLE 92, 23, 316, 161
STYLE WS_POPUP|DS_MODALFRAME|WS_CAPTION|WS_SYSMENU|WS_VISIBLE
CAPTION "LISTADO DE VENCIMIENTOS DE FACTURAS"
FONT 8, "MS Sans Serif"
{
  CONTROL "Desde Vencimiento", 4002, "Static", SS_CENTERIMAGE|WS_GROUP, 28, 36, 72, 12
  CONTROL "Hasta Vencimiento", 4003, "Static", SS_CENTERIMAGE|WS_GROUP, 28, 56, 72, 12
  CONTROL "FRAS. NO PAGADAS", 4005, "Button", BS_AUTORADIOBUTTON, 204, 36, 95, 10
  CONTROL "FACTURAS PAGADAS", 4006, "Button", BS_AUTORADIOBUTTON, 204, 56, 95, 10
  CONTROL "", 4007, "SysDateTimePick32", WS_TABSTOP, 104, 36, 60, 14
  CONTROL "", 4008, "SysDateTimePick32", WS_TABSTOP, 104, 56, 60, 14
  CONTROL "ACEPTAR", 221, "Button", BS_RIGHT|WS_TABSTOP, 60, 124, 55, 16
  CONTROL "SALIR", 223, "Button", BS_RIGHT|WS_TABSTOP, 176, 124, 55, 16
}

Los bitmaps de los botones podéis quitarlos

[nageswaragunupudi]

For MS Access and also for ADO the date literals should be formatted as #YYYY-MM-DD#.

I use this function to convert dates to ADO/MSAcess date formats in my own applications.

Code: Select all

function D2ADO( dDate )

   local cDate

   IF empty( dDate )
      cDate  := "NULL"
   else
      cDate    := "#" + TRANSFORM( DTOS( dDate ), "@R XXXX-XX-XX" ) + "#"
   endif

return cDate
 

D2SQL( Date() ) returns #2013-03-18#

We can use
" HIREDATE BETWEEN " + D2ADO( date1 ) + " AND " + D2ADO( date2 )

Notes:
1. Never use BETWEEN for DateTime field-types. For example, if HIREDATE is a DateTime type, then
" HIREDATE >= " + D2ADO( date1 ) + " AND HIREDATE < " + D2ADO( date2 + 1 )

2. For mysql, mssql dates should be formatted as 'yyyy-mm-dd'
3. Oracle : DATE 'yyyy-mm-dd'

[Pedro]

Thanks nao works
Could you tell me if there is any way in Access 2003 DateTime date not?
When I create a date field always puts as date / time and does not give more chances but then you tell him is short date format, ie as in dbf.

[nageswaragunupudi]

There is nothing like short date.
Whether we create a table with a field as DATE or DATETIME, it means the same.
The field can contain time information also.

Some RDMSs like MySql allow pure Date fields ( like our DBF ) but many RDMSs allow only datetime fields.

From my experience with different RDMS like Access, MSSql, Oracle, MySql I advise that it is better to allow dates to be stored as DateTime values inside the Tables and use *SAFE* SQL keeping in mind that the field may contain time values also.

So, better to avoid BETWEEN for dates.
Use " field >= d1 and field < ( d2 + 1 )

When we move on to RDMSs, we better make it a habit to think that the date-fields can contain time part also.