need help in getting the equivalent month of week

[newphpcoder]

Hi..

I have 2 tables for week and calendar days.

first I have so_week
fields:
from_week
to_week

second is calendar_days
fields:
month_name
working_days

sample data:
from_week : 33
to_week: 36

month_name:
Jan
Feb
Mar
Apr
May
and so on

working_days:
23
22
25
25
23

now I need to get the working days based on from_week to week

for example from 33 to 36 the month is aug, sep, sep, sep i need to get the working days for that month.

any help is highly appreciated..

Thank you so much

[eval(BadCode)]

You also need a year to get anything to be perfect, otherwise you would essentially just be guessing. The reason is leap year.

CREATE  TABLE `test`.`so_week` (
  `so_week_ID` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `from_week` INT UNSIGNED NOT NULL ,
  `to_week` INT UNSIGNED NOT NULL ,
  PRIMARY KEY (`so_week_ID`) );

INSERT INTO 
  so_week(`from_week`, `to_week`) 
VALUES 
  (33, 36);


# as far as I went

SELECT MONTHNAME(MAKEDATE(1980,7*from_week)) FROM so_week;

Trying to recreate datetime/month names and all of that is insane. You're fighting the current, just work with the normal date/datetime data-types. It will save you a massive headache at how incredibly complicated/intricate time is. If you want someone to blame it on, blame it on the Sumerians and Babylonians, they were the ones who came up with this sexagesimal numbering (base 60) system. it's worse than the American measuring system (inches, feet, cups, fail).

Edit: the simple answer is to store from_date and to_date instead of from_week and to_week. Also, making a table with the month names and then joining on it, is pure insanity. There are built in function, MONTHNAME(date) ^^ DATEFORMAT(date,format-string)) which are deterministic and will get the answer incredibly faster and lqtm, i remember when I did something like what you're doing.