kbit
asked on
Date functions - this week and this month
Hi, at the moment I have the following code which pulls out records for the current day (using PHP).
Can someone please tell me how to get records for the current week and also the current month?
Please note that this database will be used across different timezones and in different countries.
Many thanks
Can someone please tell me how to get records for the current week and also the current month?
Please note that this database will be used across different timezones and in different countries.
Many thanks
SELECT * FROM leadscustomersdetail INNER JOIN leadscustomers ON leadscustomers.Lead_No = leadscustomersdetail.Lead_No WHERE leadscustomersdetail.ClosureDate >= CURDATE()
AND leadscustomersdetail.ClosureDate < DATE_ADD(CURDATE(), INTERVAL 1 DAY)
ASKER
Havent looked into how to handle the time differences yet, that comes next.
The ClosureDate field is of DATE type.
"Current week" is between Day 1 and Day 7 (inclusive) of the week in which we currently are in (as determined by the CUR_DATE function). Does mySQL consider Sunday or Monday as Day 1 by default?
"Current month" is between the first and last days (inclusive) of the month in which we currently are in (as determined by the CUR_DATE function).
Hope this helps, thanks again
The ClosureDate field is of DATE type.
"Current week" is between Day 1 and Day 7 (inclusive) of the week in which we currently are in (as determined by the CUR_DATE function). Does mySQL consider Sunday or Monday as Day 1 by default?
"Current month" is between the first and last days (inclusive) of the month in which we currently are in (as determined by the CUR_DATE function).
Hope this helps, thanks again
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_dayofweek
day 1 is sunday.
so:
day 1 is sunday.
so:
current week should be this:
AND leadscustomersdetail.ClosureDate >= DATEADD( CURDATE(), INTERVAL 1-dayofweek(CURDATE()))
AND leadscustomersdetail.ClosureDate < DATEADD( CURDATE(), INTERVAL 8-dayofweek(CURDATE()))
current month:
AND leadscustomersdetail.ClosureDate >= DATEADD( CURDATE(), INTERVAL 1-dayofmonth(CURDATE()))
AND leadscustomersdetail.ClosureDate < DATEADD(DATEADD( CURDATE(), INTERVAL 1-dayofmonth(CURDATE())), INTERVAL 1 MONTH)
ASKER
Many thank angelIII, no doubt your solutions are correct so I'll have to investigate why neither are working for me on my database
what errors do you get?
ASKER
Current week error:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AND leadscustomersdetail.Closu reDate < DATEADD ( CURDATE( ) , INTERVAL 8 - da' at line 1
Current month error:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AND leadscustomersdetail.Closu reDate < DATEADD ( DATEADD ( CURDATE( ) , INTER' at line 1
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AND leadscustomersdetail.Closu
Current month error:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AND leadscustomersdetail.Closu
you must NOT have a space before the (
ASKER
Current week error with all the spaces removed:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')
AND leadscustomersdetail.Closu reDate < DATEADD(CURDATE(),INTERVAL 8-dayofweek' at line 3
Current month error with all the spaces removed:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')
AND leadscustomersdetail.Closu reDate < DATEADD(DATEADD(CURDATE(), INTERVAL 1-' at line 3
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')
AND leadscustomersdetail.Closu
Current month error with all the spaces removed:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')
AND leadscustomersdetail.Closu
:blush:
it's actually DATE_ADD() and not DATEADD() ... sorry
it's actually DATE_ADD() and not DATEADD() ... sorry
ASKER
I had tried that as well, error messages are still the exact same. I just dont get it, The exact code (copied from PHPmyAdmin) looks like this:
SELECT *
FROM leadscustomersdetail
WHERE leadscustomersdetail.Closu reDate >= DATEADD(CURDATE(),INTERVAL 1-dayofmonth(CURDATE()))
AND leadscustomersdetail.Closu reDate < DATEADD(DATEADD(CURDATE(), INTERVAL 1-dayofmonth(CURDATE())),I NTERVAL 1 MONTH)
LIMIT 0 , 30
SELECT *
FROM leadscustomersdetail
WHERE leadscustomersdetail.Closu
AND leadscustomersdetail.Closu
LIMIT 0 , 30
as I said:
SELECT *
FROM leadscustomersdetail
WHERE leadscustomersdetail.ClosureDate >= DATE_ADD(CURDATE(),INTERVAL 1-dayofmonth(CURDATE()))
AND leadscustomersdetail.ClosureDate < DATE_ADD(DATE_ADD(CURDATE(),INTERVAL 1-dayofmonth(CURDATE())),INTERVAL 1 MONTH)
LIMIT 0 , 30
ASKER
Same error, I'm tempted to use these instead:
SELECT * FROM `leadscustomersdetail` WHERE WEEK(leadscustomersdetail. ClosureDat e,1) = WEEK(CURRENT_DATE(),1);
and for the month:
SELECT * FROM leadscustomersdetail WHERE MONTH( leadscustomersdetail.Closu reDate ) = MONTH( CURRENT_DATE( ) )
SELECT * FROM `leadscustomersdetail` WHERE WEEK(leadscustomersdetail.
and for the month:
SELECT * FROM leadscustomersdetail WHERE MONTH( leadscustomersdetail.Closu
maybe...
Current Week
SELECT fields FROM table WHERE WEEK(date_field)=WEEK(NOW( )); # or CURDATE() instead NOW()
Current Month
SELECT field FROM table WHERE MONTH(date_field)=MONTH(NO W());
Current Week
SELECT fields FROM table WHERE WEEK(date_field)=WEEK(NOW(
Current Month
SELECT field FROM table WHERE MONTH(date_field)=MONTH(NO
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>used across different timezones and in different countries
do they use the server date/time, and just display the date/time with the utc offset as needed?
also, what function do you need that you cannot find in the docs:
http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html