Bob-Villa
asked on
MYSQL - How to get 1st, 2nd, 3rd etc... Monday, Tuesday, Wednesda, etc ... date in a month
Having trouble finding the 1st, 2nd, whatever day in a month.
I know the month and year. Say... Sept. 2008
I want to know what is the Date of First Monday in that month. or Second Friday, etc.
I have seen some other functions in pSQL and Oracle but not MySQL. I have MySQL version 5.x
I know the month and year. Say... Sept. 2008
I want to know what is the Date of First Monday in that month. or Second Friday, etc.
I have seen some other functions in pSQL and Oracle but not MySQL. I have MySQL version 5.x
Do you want to find a particular record for the first day ?
ASKER
i want to know the date of the first monday in september of 2008 returned in MYSQL format. not the date of an actual record.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
(No points please. This is just a translation)
Here is a rough translation of pssandhu's function into MySQL. Watch out for line wrapping
Here is a rough translation of pssandhu's function into MySQL. Watch out for line wrapping
delimiter //
CREATE FUNCTION fn_GetWeekdayDate (
TheDate Datetime,
DayName varchar(10),
DayNum Tinyint
)
RETURNS Datetime
BEGIN
DECLARE InitialDate Datetime;
DECLARE ReturnDate Datetime;
DECLARE Dow int;
SET InitialDate =
CASE WHEN Date_Format(TheDate, '%W') = DayName THEN TheDate
WHEN Date_Format(Date_Add(TheDate, INTERVAL 1 DAY), '%W') = DayName THEN Date_Add(TheDate, INTERVAL 1 DAY)
WHEN Date_Format(Date_Add(TheDate, INTERVAL 2 DAY), '%W') = DayName THEN Date_Add(TheDate, INTERVAL 2 DAY)
WHEN Date_Format(Date_Add(TheDate, INTERVAL 3 DAY), '%W') = DayName THEN Date_Add(TheDate, INTERVAL 3 DAY)
WHEN Date_Format(Date_Add(TheDate, INTERVAL 4 DAY), '%W') = DayName THEN Date_Add(TheDate, INTERVAL 4 DAY)
WHEN Date_Format(Date_Add(TheDate, INTERVAL 5 DAY), '%W') = DayName THEN Date_Add(TheDate, INTERVAL 5 DAY)
WHEN Date_Format(Date_Add(TheDate, INTERVAL 6 DAY), '%W') = DayName THEN Date_Add(TheDate, INTERVAL 6 DAY)
ELSE TheDate
END;
SET ReturnDate = CASE WHEN DayNum IS NULL or DayNum IN (0,1) Then InitialDate
ELSE Date_Add(InitialDate, INTERVAL (7*(DayNum-1)) DAY)
END;
/* uncomment this if you want to return null instead
of allowing crossover months
SET ReturnDate = CASE WHEN Month(TheDate) = Month(ReturnDate) THEN ReturnDate
ELSE NULL
END;
*/
RETURN ReturnDate;
END
//
delimiter ;
SELECT fn_GetWeekdayDate('2008-09-01', 'MONDAY', 1);
What was inadequate about pssandhu's suggestion? IMO it did exactly what the asker requested.
I don't want any points for it, but did they try the translated version?
I don't want any points for it, but did they try the translated version?
ASKER
pssandhu provided sql code that did not work with mysql. agx provided a rough translation that needed some work. I was looking for some built-in function, or an advanced query that I was unfamiliar with, perhaps I didn't ask clearly. I was able to accomplish the same thing (in the 43 lines above) in 2 lines of perl code. If I were another user searching for the same solution I don't feel the answers given here would have been a great solution. Didn't mean to offend anyone, I was just trying to clean up my 2 open questions. Please don't respond with any additional comments I don't have time to debate this issue. I pay my $10 a month to find quick answers in a pinch, not to debate the quality of the answers given by other members.
No need for debate. No further suggestions were offered because no other comments were made until now. For future reference if a suggestion doesn't meet your needs, for whatever the reason, feel free to ask for alternatives :) In addition, be sure to provide complete details about your environment so responders will know what options are available to you.