Solved

Date functions - this week and this month

Posted on 2008-10-27
14
838 Views
Last Modified: 2008-11-08
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
Comment
Question by:kbit
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
14 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22815138
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
 

Author Comment

by:kbit
ID: 22815314
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22817463
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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 

Author Comment

by:kbit
ID: 22819919
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22819975
what errors do you get?
0
 

Author Comment

by:kbit
ID: 22820021
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22820078
you must NOT have a space before the (
0
 

Author Comment

by:kbit
ID: 22820188
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22820222
:blush:

it's actually DATE_ADD() and not DATEADD() ... sorry
0
 

Author Comment

by:kbit
ID: 22820290
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22820342
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
 

Author Comment

by:kbit
ID: 22820516
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
 
LVL 11

Expert Comment

by:NoiS
ID: 22823518
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
 

Accepted Solution

by:
kbit earned 0 total points
ID: 22865568
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question