Get the week of the month in MYSQL

Question

I am developing a Java application using MySQL. I need to know which is the week of each month, of the stored dates. Is there any MySQL function for that ? Basically , if i was to use this for the current date (13.09) it would show me its in week number 2 and tomorrow it will be week number 3.

Answer 1

You can play with the WEEK() function, and see if it suits your needs. Here I'm using WEEK(date, 3) that will return the week of the year from 1 to 53, starting from Mondays:

set @my_date = '2015-09-13';

SELECT
  WEEK(@my_date, 3) -
  WEEK(@my_date - INTERVAL DAY(@my_date)-1 DAY, 3) + 1
  AS week_number;

• WEEK(date, 3) will return the week of the year of the selected date
• WEEK(date - INTERVAL DAY(@my_date)-1 DAY, 3) will return the week of the year of the first day of the month of the selected date

It will return 1 for 01-March-2015 (because it's the first day of the month so it's week 1) and 2 for 02-March-2015 (because weeks starts from Mondays, so it's a new week). If this is not the desidered behaviour you should specify your requirements more precisely.

Please see a fiddle here.