Start and end date for catalog item

[thyon]

you could have also created your events using the TL event calendar and used this as a reference link to your DJs, etc. by adding fields for them to the events calendar. Creating your own catalog is really a toss-up between making your own app for TL or using catalog, because if it gets complex, you were better off making your own app, than trying to pigeon-hole the catalog to work for all occasions.

[valentin]

I´m having a similar problem right now and tried a few things - but without success.

I have a List of Catalog - Items (Simple Table) - Every Item has a Date Field.
Now I want to create a Condition which tells my Modul in the Front End to show only the Items of the present month.

How do I do that?

I tried things like that in the Condition Field:
FROM_UNIXTIME(event_date) = CURMONTH()
(event_date) = CURMONTH()
(event_date) = MONTH()

nothing worked for me. Probably a small thing - maybe somebody knows the right piece of code.

[scare]

Try something like

Code: Select all

MONTH(FROM_UNIXTIME(event_date)) = MONTH(NOW()) AND YEAR(FROM_UNIXTIME(event_date)) = YEAR(NOW())

Although I think that calculating timestamp of the first day in month and first day of the following month and using BETWEEN would be much more efficient

[valentin]

thanks scare, works like a charme.
Just one question. Does this also show passed event´s of a month?

e.g.: today is the 5th april and there was an event yesterday. will it be displayed? the problem is i´d have to wait untill tomorrow to see what happens - since it´s the frist of march now!

[scare]

It should display all events from the current month, it ignores the day completely, so only month and year matter. Of course you need to test it You may try to substitute NOW() with tomorrow's date to see if today's events are displayed.

[valentin]

okay, this works great.
have a look here:
http://grazjazz.at.dd14304.kasserver.co ... sicht.html

can i bother you with another question?
how would i make a list fot the next month? I´m thinking about presenting the present and the coming month...

best regards,
valentin

[scare]

OK, I'm writing this from my head so don't complain if it doesn't work Test every step below in some phpmyadmin or similar program to see if it gives correct results.

We need to find the first day of the next month (should return something like 2010-04-01):

Code: Select all

select DATE_ADD(LAST_DAY(NOW()), INTERVAL 1 DAY)

And the first day of the month following the next month (2010-05-01):

Code: Select all

select DATE_ADD(DATE_ADD(LAST_DAY(NOW()), INTERVAL 1 DAY), INTERVAL 1 MONTH)

Now we just need to select dates between those two dates (+ convert it to unix timestamp), so the result should be something like this:

Code: Select all

event_date >= UNIX_TIMESTAMP(DATE_ADD(LAST_DAY(NOW()), INTERVAL 1 DAY)) AND event_date < DATE_ADD(DATE_ADD(LAST_DAY(NOW()), INTERVAL 1 DAY), INTERVAL 1 MONTH))

[valentin]

thanks, I´ll give it a try - but first I have to solve the problem here:
viewtopic.php?f=27&t=1325