Dates behave different with Sql systax ?

Board index FiveWin for Harbour/xHarbour Dates behave different with Sql systax ?

Posts: 1487

Joined: Tue Jun 14, 2016 07:51 AM

Dates behave different with Sql systax ?
Posted: Sun Jan 08, 2017 10:12 PM

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

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

 $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());

Marc Venken

Using: FWH 23.08 with Harbour

vilian

Posts: 1067

Joined: Wed Nov 09, 2005 02:17 AM

Re: Dates behave different with Sql systax ?
Posted: Mon Jan 09, 2017 01:43 AM

Thy this:

Code (fw): Select all Collapse

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) } )

Sds,
Vilian F. Arraes
vilian@vfatec.com.br
Belém-Pa-Brazil

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Dates behave different with Sql systax ?
Posted: Mon Jan 09, 2017 01:56 PM

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

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

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

Regards

G. N. Rao.

Hyderabad, India

rhlawek

Posts: 195

Joined: Sun Jul 22, 2012 07:01 PM

Re: Dates behave different with Sql systax ?
Posted: Mon Jan 09, 2017 11:55 PM

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

Posts: 1487

Joined: Tue Jun 14, 2016 07:51 AM

Re: Dates behave different with Sql systax ?
Posted: Wed Jan 11, 2017 07:45 PM

Thank you all !

I used the information and have it working.

Marc

Marc Venken

Using: FWH 23.08 with Harbour

FiveTech Support Forums

Continue the discussion