Re: System Function that will give you current Saturday Date

Curtis Crowson wrote on 2004-01-16: > "N" <N@N.COM> wrote on 2003-12-23: >> Is there a system function in Informix that will >>give you a current specific date of a week (specifically >>current Saturday). > > select > current, > current + ( 6 - weekday( current ) ) units day next_saturday, > current - ( weekday(current) + 1 ) units day last_saturday > from > systables > where > tabid = 1 > ; > > This is simple enough you can change it for other days. > 0 Sunday, > 1 Monday, > 2 Tuesday, > 3 Wednesday, > 4 Thursday, > 5 Friday, > 6 Saturday > > You can derive "simple" formulas for all of the days of the week. > > Next Monday is > current + ( 6 - weekday( current - 2 ) ) units day next_monday, > > I think. > > And you are welcome. Perpetuating the long gaps between postings - here's a solution I hacked together based on Curtis's hint. I'm not convinced that the formulae for the other days of the week are completely obvious. The first few lines in the SQL are for SQLCMD; DB-Access will give you '-201 syntax error' messages. Sorry about the odd line wraps. { # Stored procedures NEXT_WEEKDAY() and LAST_WEEKDAY() written by # Jonathan Leffler <jleffler@us.ibm.com> based on a hint from Curtis # Crowson <curtis@crowson1.com> posted to comp.databases.informix on # 2004-01-16. # # Given a day of week (dow) value in the range 0 {Sunday} to 6 # {Saturday} and a reference date (the default is TODAY), these # procedures return the next Dow-day after or before the reference date. # On a Monday (2004-01-19, for example), NEXT_WEEKDAY() returns # 2004-01-26 and LAST_WEEKDAY() returns 2004-01-12. If this is not the # desired behaviour - you want 2004-01-19 returned - then remove the # second IF block from the procedures. Set-up for use with SQLCMD. } continue push; continue on; DROP PROCEDURE next_weekday; DROP PROCEDURE last_weekday;continue pop; trace on; { Weekday DOW following given reference date - default today } CREATE PROCEDURE next_weekday(dow INTEGER, refdate DATE DEFAULT TODAY) RETURNING DATE; DEFINE dt DATE; IF refdate IS NULL OR dow IS NULL OR dow NOT BETWEEN 0 {Sunday} AND 6 {Saturday} THEN RETURN NULL; END IF; LET dt = refdate + (6 - WEEKDAY(refdate + 6 - dow)); IF dt = refdate THEN LET dt = dt + 7; END IF; RETURN dt; END PROCEDURE; { Weekday DOW preceding given reference date - default today } CREATE PROCEDURE last_weekday(dow INTEGER, refdate DATE DEFAULT TODAY) RETURNING DATE; DEFINE dt DATE; IF refdate IS NULL OR dow IS NULL OR dow NOT BETWEEN 0 {Sunday} AND 6 {Saturday} THEN RETURN NULL; END IF; LET dt = refdate + (6 - WEEKDAY(refdate + 6 - dow)) - 7; IF dt = refdate THEN LET dt = dt - 7; END IF; RETURN dt; END PROCEDURE; echo "Testing next_weekday - reference date TODAY"; EXECUTE PROCEDURE next_weekday(-1); -- Null (DOW invalid) EXECUTE PROCEDURE next_weekday(0); -- Answer depends on today's date! EXECUTE PROCEDURE next_weekday(1); -- Answer depends on today's date! EXECUTE PROCEDURE next_weekday(2); -- Answer depends on today's date! EXECUTE PROCEDURE next_weekday(3); -- Answer depends on today's date! EXECUTE PROCEDURE next_weekday(4); -- Answer depends on today's date! EXECUTE PROCEDURE next_weekday(5); -- Answer depends on today's date! EXECUTE PROCEDURE next_weekday(6); -- Answer depends on today's date! EXECUTE PROCEDURE next_weekday(7); -- Null (DOW invalid) EXECUTE PROCEDURE next_weekday(NULL); -- Null (DOW invalid) echo "Testing next_weekday - reference date 2004-02-25 is a Wednesday (day 3)"; EXECUTE PROCEDURE next_weekday(-1, MDY(2,25,2004)); -- Null (DOW -- invalid) EXECUTE PROCEDURE next_weekday(0, MDY(2,25,2004)); -- 2004-02-29 EXECUTE PROCEDURE next_weekday(1, MDY(2,25,2004)); -- 2004-03-01 EXECUTE PROCEDURE next_weekday(2, MDY(2,25,2004)); -- 2004-03-02 EXECUTE PROCEDURE next_weekday(3, MDY(2,25,2004)); -- 2004-03-03 EXECUTE PROCEDURE next_weekday(4, MDY(2,25,2004)); -- 2004-02-26 EXECUTE PROCEDURE next_weekday(5, MDY(2,25,2004)); -- 2004-02-27 EXECUTE PROCEDURE next_weekday(6, MDY(2,25,2004)); -- 2004-02-28 EXECUTE PROCEDURE next_weekday(7, MDY(2,25,2004)); -- Null (DOW -- invalid) EXECUTE PROCEDURE next_weekday(NULL, MDY(2,25,2004)); -- Null (DOW -- invalid) EXECUTE PROCEDURE next_weekday(1, NULL); -- Null (refdate invalid) echo "Testing last_weekday - reference date TODAY"; EXECUTE PROCEDURE last_weekday(-1); -- Null (DOW invalid) EXECUTE PROCEDURE last_weekday(0); -- Answer depends on today's date! EXECUTE PROCEDURE last_weekday(1); -- Answer depends on today's date! EXECUTE PROCEDURE last_weekday(2); -- Answer depends on today's date! EXECUTE PROCEDURE last_weekday(3); -- Answer depends on today's date! EXECUTE PROCEDURE last_weekday(4); -- Answer depends on today's date! EXECUTE PROCEDURE last_weekday(5); -- Answer depends on today's date! EXECUTE PROCEDURE last_weekday(6); -- Answer depends on today's date! EXECUTE PROCEDURE last_weekday(7); -- Null (DOW invalid) echo "Testing last_weekday - reference date 2004-02-25 is a Wednesday (day 3)"; EXECUTE PROCEDURE last_weekday(-1, MDY(2,25,2004)); -- Null (DOW -- invalid) EXECUTE PROCEDURE last_weekday(0, MDY(2,25,2004)); -- 2004-02-22 EXECUTE PROCEDURE last_weekday(1, MDY(2,25,2004)); -- 2004-02-23 EXECUTE PROCEDURE last_weekday(2, MDY(2,25,2004)); -- 2004-02-24 EXECUTE PROCEDURE last_weekday(3, MDY(2,25,2004)); -- 2004-02-18 EXECUTE PROCEDURE last_weekday(4, MDY(2,25,2004)); -- 2004-02-19 EXECUTE PROCEDURE last_weekday(5, MDY(2,25,2004)); -- 2004-02-20 EXECUTE PROCEDURE last_weekday(6, MDY(2,25,2004)); -- 2004-02-21 EXECUTE PROCEDURE last_weekday(7, MDY(2,25,2004)); -- Null (DOW -- invalid) EXECUTE PROCEDURE last_weekday(NULL, MDY(2,25,2004)); -- Null (DOW -- invalid) EXECUTE PROCEDURE last_weekday(1, NULL); -- Null (refdate invalid) -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/