Becca Becca - 7 months ago 8
SQL Question

Is it possible for me to create a view that displays all records from a table within a month?

I'm writing queries and views for a database. I want to create a view that the user can easily use to view records weekly or monthly. I know i can use DATEDIFF() but I just want a view that will display information within a particular month or week. so I won't need to edit anything. Any ideas or is it possible?

Answer

Try using date and time functions (MONTH(), YEAR(), DAY())

This week: SELECT * FROM jokes WHERE WEEKOFYEAR(date)=WEEKOFYEAR(NOW());

Last week: SELECT * FROM jokes WHERE WEEKOFYEAR(date)=WEEKOFYEAR(NOW())-1;

There are many SQL date/time functions that allow you to do this. Go through SQL functions to know more.

You can design separate pages for Daily/Weekly/Monthly entries and show respective data on those pages.