Page 1 of 1

how to find the week of the month?

Posted: Tue Mar 13, 2007 1:10 am
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!!

Posted: Tue Mar 13, 2007 1:45 am
by dibyendrah
Easy using mysql builtin function.

Code: Select all

mysql> SELECT YEAR('2007-03-12');
        -> 1998

Posted: Tue Mar 13, 2007 3:32 am
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

Posted: Tue Mar 13, 2007 3:45 am
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!!

Posted: Tue Mar 13, 2007 4:20 am
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".

Posted: Tue Mar 13, 2007 4:26 am
by aaronhall
Except for weeks starting on the first. If DAY('1-1-2007') == DAY('3-1-2007'), don't add a week.

Posted: Tue Mar 13, 2007 4:41 am
by PHPycho
Thanks Mr. VOlka
It just rocked !!!