Dates behave different with Sql systax ?

[Marc Venken]

Hello,

This code works, but if I changes the dates, or even enter true the dates inside the editvars, the result is empty.

cStartDatum := {^ 2016/12/15 } is a strange why of configuring a date for me. What does it do ?

Dates where always a little bit hard, also in php, but how to tread them in FWH with SQL.

Code: Select all  Expand view  RUN

cPoule := "3B"
nMinAge := 41
nMaxAge := 50
// YYYY/M/D
cStartDatum := {^ 2016/12/15 }
cEndDatum := {^ 2017/01/08 }

msginfo(cStartDatum)

EDITVARS cPoule,cStartDatum,cEndDatum

//cStartdatum = substr(dtoc(cStartdatum),4,2)+"/"+substr(dtoc(cStartdatum),1,2)+"/20"+substr(dtoc(cStartdatum),7,8)
//cStartdatum = "20"+substr(dtoc(cStartdatum),7,8)+"/"+substr(dtoc(cStartdatum),4,2)+"/"+substr(dtoc(cStartdatum),1,2)

//msginfo(cStartDatum)

cFields:= "home, away, poule,dag,uitslag_thuis,uitslag_uit,date_format(dag,'%d %M %Y') AS Datedag"
oRs := oCn:RowSet( "select "+ cFields + " from `tbl_matchen` where `poule` = ? and dag >= ? and dag <= ? ",{ cPoule,cStartDatum,cEnddatum } )


 

ofthen used sql's in my soccer php program :

Code: Select all  Expand view  RUN

 $query2 = mysql_query("SELECT *, voor - tegen as saldo from tbl_ploegen where poule = '$poule' and periode = '0'
 ORDER BY punten DESC , gespeeld , winst DESC , gelijk DESC, verlies DESC, saldo DESC , voor DESC, landnaam") or die(mysql_error());

 $query3 = mysql_query("SELECT *, date_format(dag,'%a %d %M %Y') as entry_date from tbl_matchen
 where poule = '$poule' and (thuisploeg_id = '$ploeg' or uitploeg_id = '$ploeg') ORDER BY dag ") or die(mysql_error());



 

[vilian]

Thy this:

Code: Select all  Expand view  RUN

cPoule := "3B"
nMinAge := 41
nMaxAge := 50
// YYYY/M/D
cStartDatum := Ctod("12/15/2016")
cEndDatum  :=  Ctod("01/08/2017")

msginfo(cStartDatum)

//EDITVARS cPoule,cStartDatum,cEndDatum

cFields:= "home, away, poule,dag,uitslag_thuis,uitslag_uit,date_format(dag,'%d %M %Y') AS Datedag"
oRs := oCn:RowSet( "select "+ cFields + " from `tbl_matchen` where `poule` = ? and dag >= ? and dag <= ? ",{ cPoule, oCn:ValToSql(cStartDatum), oCn:ValToSql(cEnddatum) } )


 

[nageswaragunupudi]

{^ YYYY/MM/DD } is the way we define a date constant.
Syntax:
{ˆ [<YYYY/MM/DD>] [<hh:mm:ss[.ccc>]] [AM|PM] }

This works with both xHarbour and Harbour and has nothing to do with any SQL.

d1 := {^ 2000/11/29 } // Assignment is done at compile time
is same as
d1 := STOD( "20001129" ) // Assignment is done at runtime and so, slower

Please never use CTOD(..) because this depends on date format set, which can be different for different countries.


About the above SQL statement:

Dates where always a little bit hard, also in php, but how to tread them in FWH with SQL.

But not at all difficult with FWHMYSQL. Please use them just as you use in Harbour with DBFs.
No need to do any conversions.

I presume field type of "dag" is Date. There is no need to covert "dag" in to a character value using date_format.
Also there is no need to use ValToSql.

Please straight away use simple Harbour variables. It is important to stop thinking that it is complex to use Dates in SQL

Code: Select all  Expand view  RUN

cPoule      := "3B"
nMinAge     := 41
nMaxAge     := 50

dStartDatum := {^ 2016/12/15 } // Date Type variable
dEndDatum   := {^ 2017/01/08 } // Date type variable

// Alternatively

dStartDatum := STOD( "20161215" ) // Date Type variable
dEndDatum   := STOD( "20170108" ) // Date type variable


EDITVARS cPoule,dStartDatum,dEndDatum  // optional


cFields:= "home, away, poule,dag,uitslag_thuis,uitslag_uit,dag"
oRs := oCn:RowSet( "select "+ cFields + " from `tbl_matchen` where `poule` = ? and dag >= ? and dag <= ?", { cPoule, dStartDatum, dEnddatum } )

XBROWSER oRs
 

Much safer version of the same SQL, in case "dag" is a DateTime field is:

Code: Select all  Expand view  RUN

oRs := oCn:RowSet( "select "+ cFields + " from `tbl_matchen` where `poule` = ? and dag >= ? and dag < ?", { cPoule, dStartDatum, dEnddatum + 1 } )
 

[rhlawek]

If the syntax is more comfortable you can use the harbour hb_DateTime() function to create a datetime variable.

hb_DateTime( nYear, nMonth, nDay, nHour, nMin, nSec, nMill ) // I believe the last argument is milliseconds.

The source file rtl\dateshb.c has a fair number of datetime related functions, it is a worthwhile read.

Robb

[Marc Venken]

Thank you all !

I used the information and have it working.

Marc