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

[muiru james]

Hi,

Thanks Katrina and Alvaro for your time and effort.

Katrin's suggestion solved my problem.

If you have an idea on my string on batch editing dates for withdrawn/lost
items to set an earlier one I would be more than happy

Thank you all

Regards
James

On Sat, Nov 28, 2020 at 12:39 PM Katrin Fischer <katrin.fischer.83 at web.de>
wrote:

> Hi,
>
> when using the |date, use the datepicker to set the date. Koha will
> automatically convert it to the correct format for the database in your
> query. I think the issue is that items.itemlost_on is a datetime. So use
> DATE() to get better results and use the datepicker to enter the dates!
>
> AND DATE(items.itemlost_on) BETWEEN <<Between (yyyy-mm-dd)|date>> AND
> <<and...
>
> Hope this helps,
>
> Katrin
>
>
> On 27.11.20 10:25, muiru james wrote:
> > 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
> _______________________________________________
>
> Koha mailing list  http://koha-community.org
> Koha at lists.katipo.co.nz
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>