Link to home
Start Free TrialLog in
Avatar of kbit
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
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)

Open in new window

Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

you will have to define more clearly what you consider "current week" and "current month", and how the date/time is stored in regards to
>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 
Avatar of kbit
kbit

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
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_dayofweek
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)

Open in new window

Avatar of kbit

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?
Avatar of kbit

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.ClosureDate < 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.ClosureDate < DATEADD ( DATEADD ( CURDATE( ) , INTER' at line 1
you must NOT have a space before the (
Avatar of kbit

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.ClosureDate < 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.ClosureDate < DATEADD(DATEADD(CURDATE(),INTERVAL 1-' at line 3
:blush:

it's actually DATE_ADD() and not DATEADD() ... sorry
Avatar of kbit

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.ClosureDate >= DATEADD(CURDATE(),INTERVAL 1-dayofmonth(CURDATE()))
 AND leadscustomersdetail.ClosureDate < DATEADD(DATEADD(CURDATE(),INTERVAL 1-dayofmonth(CURDATE())),INTERVAL 1 MONTH)
 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

Open in new window

Avatar of kbit

ASKER

Same error, I'm tempted to use these instead:

SELECT * FROM `leadscustomersdetail` WHERE WEEK(leadscustomersdetail.ClosureDate,1) = WEEK(CURRENT_DATE(),1);

and for the month:

SELECT * FROM leadscustomersdetail WHERE MONTH( leadscustomersdetail.ClosureDate ) = MONTH( CURRENT_DATE( ) )
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(NOW());



ASKER CERTIFIED SOLUTION
Avatar of kbit
kbit

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial