[Koha] [koha] SQL Report for withdrawn books.

[Alvaro Cornejo]

Hi James

You need to define a date format /tell mysql that you are comparing dates
with DATE_FORMAT(YOURDATE, '%yyyy-%mm-%dd') so your query shall be
something like:

I´m not sure how will it work with the dates picklers


SELECT biblio.author AS Author, biblio.title AS Title,
biblioitems.publishercode AS Publisher,biblioitems.publicationyear AS
Date_of_Publication, biblioitems.isbn AS ISBN, items.itemcallnumber AS
Call_Number,items.barcode AS Barcode_Number, items.price AS Price,
items.itemlost_on AS 'Date Lost'
FROM items
LEFT JOIN biblioitems ON
(items.biblioitemnumber=biblioitems.biblioitemnumber)
LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber)
WHERE items.itype= <<Section|itemtypes>>
AND items.itemlost=<<Status|lost>>
AND items.itemlost_on BETWEEN DATE_FORMAT( <<Between (yyyy-mm-dd)|date>> ,
'%yyyy-%mm-%dd') AND DATE_FORMAT(<<and (yyyy-mm-dd)|date>> ,
'%yyy-%mm-%dd')
ORDER BY items.itemcallnumber ASC





|----------------------------------------------------------------------------------------|
 Stay safe / Cuídate/  Reste sécurisé
*7* Switch off as you go / Apaga lo que no usas /  Débranchez au fur et à
mesure.
 *q *Recycle always / Recicla siempre / Recyclez toujours
 P Print only if absolutely necessary / Imprime solo si es necesario /
Imprimez seulement si nécessaire


Le ven. 27 nov. 2020 à 04:25, muiru james <muirunyeri at gmail.com> a écrit :

> I have the below query which runs well without the date filter. On
> including the date placeholder, it gives me zero results. What am I
> missing? I need to be able to select a date range. Please help
>
> SELECT biblio.author AS Author, biblio.title AS Title,
> biblioitems.publishercode AS Publisher,biblioitems.publicationyear AS
> Date_of_Publication, biblioitems.isbn AS ISBN, items.itemcallnumber AS
> Call_Number,items.barcode AS Barcode_Number, items.price AS Price,
> items.itemlost_on AS 'Date Lost'
> FROM items
> LEFT JOIN biblioitems ON
> (items.biblioitemnumber=biblioitems.biblioitemnumber)
> LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber)
> WHERE items.itype= <<Section|itemtypes>>
> AND items.itemlost=<<Status|lost>>
> AND items.itemlost_on BETWEEN <<Between (yyyy-mm-dd)|date>> AND <<and
> (yyyy-mm-dd)|date>>
> ORDER BY items.itemcallnumber ASC
> _______________________________________________
>
> Koha mailing list  http://koha-community.org
> Koha at lists.katipo.co.nz
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>