Function for week of the month in mysql

Question

I was looking for a simple function to get the week of the month (rather than the easy week of the year) in a mysql query.

The best I could come up with was:

WEEK(dateField) - WEEK(DATE_SUB(dateField, INTERVAL DAYOFMONTH(dateField)-1 DAY)) + 1

I'd love to know if I'm reinventing the wheel here, and if there is an easier and cleaner solution?

Answer 1

AFAIK, there is no standard on the first week of month.

First week of year is the week containing Jan 4th.

How do you define first week of month?

UPDATE:

You'll need to rewrite your query like this:

SELECT  WEEK(dateField, 5) -
        WEEK(DATE_SUB(dateField, INTERVAL DAYOFMONTH(dateField) - 1 DAY), 5) + 1

so that the year transitions are handled correctly, and the weeks start on Monday.

Otherwise, your query is fine.

Answer 2

There is an alternative that's sometimes used in reporting databases. It's to create a table, let's call it ALMANAC, that has one row per date (the key), and has every needed attribute of the date that might be useful for reporting purposes.

In addition to the week of the month column, there could be a column for whether or not the date is a company holiday, and things like that. If your company had a fiscal year that starts in July or some other month, you could include the fiscal year, fiscal month, fiscal week, etc. that each date belongs to.

Then you write one program to populate this table out of thin air, given a range of dates to populate. You include all the crazy calendar calculations just once in this program.

Then, when you need to know the attribute for a date in some other table, you just do a join, and use the column. Yes, it's one more join. And no, this table isn't normalized. But it's still good design for certain very specific needs.

Answer 3

Hi,

i too strucked with the same issue for days which is something like calculating the numbers of weeks that lies between tow days

eg SElect @Days = (datediff( week, @pdtFromDate, @pdtToDate) - 1) * 5 , in sql

which returns the output as 257 ,

i need to convert the same script in mysql.

Please suggest.