Date and Time problems
Moderator: General Moderators
Date and Time problems
Hello; this is my first post on here; so hell to you all =D. My current project is to create a site that will keep track of work hours; expected payouts; shifts, etc..etc... My current problem lies in when I attempt to do a 'montly summary' where I need to select all the shifts of that month only. In my MySQL database I have a 'Shift_Date' datatype 'DATE". I need to somehow parse the date out of this and select only the results in the current month. I'd prefer this to be built intp the SQL query instead of returning all the rows then filtering through them, but I cannot see any way in which to do this. If a filter is needed what would be the most dynamic way to filter the results to reduce stress on the server?
You have many different options, it really depends on your database setup. Most databases a really smart, so you can do simple stuff like....
starting_date = 2005-10-01
ending_date = 2005-10-31
Like I said you have other options to...
You can use INTERVAL and select from the cuurent date and move backward or forward...
(show the next 30 day)
If your using MySQL have a look at the time and date functions. There is many good examples that cover a lot!
pif
starting_date = 2005-10-01
ending_date = 2005-10-31
Code: Select all
SELECT column_one, column_two FROM some_table WHERE shift_date >= starting_date AND shift_date <= ending_dateYou can use INTERVAL and select from the cuurent date and move backward or forward...
(show the next 30 day)
Code: Select all
SELECT column_one, column_two FROM some_table WHERE shift_date >= CURDATE() AND shift_date <= ADDDATE(CURDATE(), INTERVAL 30 DAY);If your using MySQL have a look at the time and date functions. There is many good examples that cover a lot!
pif
- Bill H
- DevNet Resident
- Posts: 1136
- Joined: Sat Jun 01, 2002 10:16 am
- Location: San Diego CA
- Contact:
You can also use the MONTH() function:
Code: Select all
SELECT column_one, column_two FROM some_table WHERE MONTH(shift_date)=curr_month