[Zope-DB] ZSQL - Year, Month, Date issue HELP!

[Ben Avery]

hi Rod,

thanks for re-posting here.

to fix this, I would convert your year, month and days from the form 
into one date field, and use a between using your database's date operators.
I use Oracle, and what I would do is something like the following.
p.s. if you can let us know what version of Zope and Python, and what 
DB, what OS etc you are using, that also helps.

---

select tblproblemtype.name as Problem_Type, count(*)as total from 
tblservicerequest, tblproblemtype
where timelogged between TO_DATE(<dtml-sqlvar start_date type="string">, 
'YYYY-MM-DD') and TO_DATE(<dtml-sqlvar end_date type="string">, 
'YYYY-MM-DD')
and
tblservicerequest.problemtype = tblproblemtype.id
group by tblproblemtype.name
order by tblproblemtype.name;

---

but you would have to have a single form element where the user would 
type the date in, in the same format you state in the date conversion.

You should only really use <dtml-sqlvar> rather than <dtml-var> in sql 
queries for safety, otherwise you could do the date conversion like
TO_DATE('<dtml-var start_year>-<dtml-var start month>-<dmtl-var 
start-day>', 'YYYY-MM-DD')
read the help files for why not.

or if you absolutely have to use separate form elements from your page, 
you should look into a python script to go between the two. something like:

---

#parameters: year_from, year_to, month_from, month_to, day_from, day_to

date_from = '%s-%s-%s' % (year_from, month_from, day_from)
date_from = '%s-%s-%s' % (year_to, month_to, day_to)

results=context.name_of_zsql_method(date_from=date_from, 
date_to=date_to).dictionaries()

print str(results) # or whatever you want to do with them
return printed

---

don't make the mistake of being afraid of Python, despite the name - it 
was taken from Monty Python, not the snake! there are plenty of 
tutorials to get you started - have a google around.

hope this gets you going, good luck

Ben

Roderick Silva wrote:
> The attached code will work correctly as long as I don't cross over the 
> year. Once I do that the numbers get all messed up. In other words if I 
> choose 02/31/02 to 03/31/02 I am ok. Once I choose 12/31/02 to 1/31/03 
> the numbers get messed up.
> 
> I was told that I should concat. Can someone point the way? Can someone 
> write one line of it for me?
> 
> Once I can start helping out on the list I will. Right now I am not much 
> help unless you need Flash work in return. That I can do well.
> 
> Thanks,
>  
> -Roderick-
> 
> 
> 
> ----------------------------------------------------------------------
> 
> select tblproblemtype.name as Problem_Type, count(*)as total from 
> tblservicerequest, tblproblemtype
> 
> where
>     year(timelogged) = <dtml-var 
> "REQUEST.form['subfield_startdate_year']">  and <dtml-var 
> "REQUEST.form['subfield_enddate_year']">
> and
>     month(timelogged) = <dtml-var 
> "REQUEST.form['subfield_startdate_month']">  and <dtml-var 
> "REQUEST.form['subfield_enddate_month']">
> and
>     day(timelogged) between <dtml-var 
> "REQUEST.form['subfield_startdate_day']">  and <dtml-var 
> "REQUEST.form['subfield_enddate_day']">
> and
>     tblservicerequest.problemtype = tblproblemtype.id
> 
> group by tblproblemtype.name
> order by tblproblemtype.name;
> 
> 
> ------------------------------------------------------------------------
> STOP MORE SPAM with the new MSN 8 <http://g.msn.com/8HMPENUS/2728> and 
> get 2 months FREE*_______________________________________________ 
> Zope-DB mailing list Zope-DB@zope.org 
> http://mail.zope.org/mailman/listinfo/zope-db