Page 1 of 1
Date and Time problems
Posted: Thu Dec 29, 2005 11:02 am
by Vapor
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?
Posted: Thu Dec 29, 2005 8:10 pm
by yum-jelly
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
Code: Select all
SELECT column_one, column_two FROM some_table WHERE shift_date >= starting_date AND shift_date <= ending_date
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)
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
Posted: Fri Dec 30, 2005 1:14 pm
by Vapor
Thanks! I'm using your first suggestion; problems occuring on the <= statement but I should be able to get this. Thanks again!
Posted: Sat Dec 31, 2005 10:01 am
by Bill H
You can also use the MONTH() function:
Code: Select all
SELECT column_one, column_two FROM some_table WHERE MONTH(shift_date)=curr_month