problem with date

[johan]

Hi,

I'm trying to create a report of our activity in a period.

I've added 2 objects (vfrom, vto) text format 13-01-2007.

If I run activity and fill in the 2 dates ex. vfrom = 01-01-2011 and vto = 17-05-2011, the report shows other dates (1-10-2000 and 11-10-2016).

my sql is

Code: Select all

$sql = "


CREATE TABLE #dataTable# SELECT count(r.act_duur) as aantal, g.gev_gevangenis, m.mod_module FROM activiteiten r, gevangenis g, module m where gev_id = act_gevangenis and m.mod_id = r.act_module_id  and r.act_datum between '#vfrom#' and '#vto#' group by  g.gev_gevangenis, mod_module"
;

nuRunQuery($sql);

When I replace #vfrom# and #vto# by the dates the report works fine.

Any idea what's going wrong.

Regards

Johan

[admin]

Johan,

You'll need to convert them from what you see to what mysql wants.
you can make your own function or use msql_date_nq() (qu stands for no quotes)
Its a bit ugly but it works.

Code: Select all

	$todate			=	msql_date_nq(#ToDate#);
	$fromdate		=	msql_date_nq(#FromDate#);

Steven

[johan]

Steven,

Thanks again.

msql_date_nq(#ToDate#); works with date format 1-jan-2007 in the objects. I't doesn't word when i choose 01-01-2007.
I've tried with date_format('#vto#', %Y-%m-%d) instead of msql_date_nq() but that doesn't work.
Do you have a suggestion?


Johan

[admin]

Johan,

Here's the answer I should have given you first time.

http://wiki.nubuilder.com/tiki-index.ph ... rmatField_

(I have only just added it to the wiki today)

Steven

[johan]

Steven,

Thanks again.

It works fine with
$from = reformatField('#vfrom#',11,$quotes = false)

That's what i like about Nubuilder someone helping me while I'm sleeping

Greetings from Belgium

Johan

[admin]

.