Solved

Date functions - this week and this month

Posted on 2008-10-27
14
829 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
  • 7
  • 6
14 Comments
 
LVL 142

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 142

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
 

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 142

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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22820078
you must NOT have a space before the (
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

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 142

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 142

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This article discusses how to create an extensible mechanism for linked drop downs.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now