how to find the week of the month?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
PHPycho
Forum Contributor
Posts: 336
Joined: Fri Jan 06, 2006 12:37 pm

how to find the week of the month?

Post by PHPycho »

What should be the query if i would like to find the week of the month from Y-m-d format.?
For Example:
If i would like to know which week is running on month of 2007-03-12 (the answer is 2nd week)
How to perform such query in SQL ?
Thanks in advance to all of YOU!!
User avatar
dibyendrah
Forum Contributor
Posts: 491
Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:

Post by dibyendrah »

Easy using mysql builtin function.

Code: Select all

mysql> SELECT YEAR('2007-03-12');
        -> 1998
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

dibyendrah wrote:Easy using mysql builtin function.

Code: Select all

mysql> SELECT YEAR('2007-03-12');
        -> 1998
actually we have 2007 in 2007 ;)

Code: Select all

SELECT Week('2007-03-12')
returns 10
User avatar
PHPycho
Forum Contributor
Posts: 336
Joined: Fri Jan 06, 2006 12:37 pm

Post by PHPycho »

Pardon me i think you didnt get my question
One more time:
I want ans 2 for the case 2007-03-12 because the March - 12 means 2nd week running..I dont want the ans 10 because its the total week from the start of the year....
I would like to find the week of the month
Hope my question is clear..If not feel free to comment..
Thanks in advance to all of You!!
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

Haven't seen a built-in function for that but try

Code: Select all

SELECT Week('2007-03-12')-Week('2007-03-01')
it returns 2 which i would interpret as "third week".
User avatar
aaronhall
DevNet Resident
Posts: 1040
Joined: Tue Aug 13, 2002 5:10 pm
Location: Back in Phoenix, missing the microbrews
Contact:

Post by aaronhall »

Except for weeks starting on the first. If DAY('1-1-2007') == DAY('3-1-2007'), don't add a week.
User avatar
PHPycho
Forum Contributor
Posts: 336
Joined: Fri Jan 06, 2006 12:37 pm

Post by PHPycho »

Thanks Mr. VOlka
It just rocked !!!
Post Reply