Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Date functions - this week and this month

Posted on 2008-10-27
14
Medium Priority
?
842 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 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
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 create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

824 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