[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 845
  • Last Modified:

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

0
kbit
Asked:
kbit
  • 7
  • 6
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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 
0
 
kbitAuthor Commented:
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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
kbitAuthor Commented:
Many thank angelIII, no doubt your solutions are correct so I'll have to investigate why neither are working for me on my database
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what errors do you get?
0
 
kbitAuthor Commented:
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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you must NOT have a space before the (
0
 
kbitAuthor Commented:
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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
:blush:

it's actually DATE_ADD() and not DATEADD() ... sorry
0
 
kbitAuthor Commented:
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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

0
 
kbitAuthor Commented:
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( ) )
0
 
NoiSCommented:
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());



0
 
kbitAuthor Commented:
Here are the solutions I've gone with and they seem to be working pretty nicely:

Current week:
SELECT * FROM leadscustomersdetail INNER JOIN leadscustomers ON leadscustomers.Lead_No = leadscustomersdetail.Lead_No WHERE  WEEK(leadscustomersdetail.ClosureDate,3) = WEEK(CURDATE(),3) AND  YEAR(leadscustomersdetail.ClosureDate) = YEAR(CURDATE())

The "3" determines that its the first week of the year, beginning with a Monday, that has more than 3 days in it

See http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_week

Current month:
SELECT * FROM leadscustomersdetail INNER JOIN leadscustomers ON leadscustomers.Lead_No = leadscustomersdetail.Lead_No WHERE MONTH(leadscustomersdetail.ClosureDate) = MONTH(CURDATE()) AND YEAR(leadscustomersdetail.ClosureDate) = YEAR(CURDATE())

Thanks to all who tride to help
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now