Thread: Last day of month
Dear friends,
Postgres 7.3.4
How to find the last sunday/mon..../sat of any given month.
Thanks
Kumar
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 > How to find the last sunday/mon..../sat of any given month. There is probably a smoother way to do it, but here is a quick little function to do what you ask. Feed it a date and a number, where 0 is Sunday, 1 is Monday, etc. CREATE OR REPLACE FUNCTION lastday(date,int) RETURNS DATE AS ' DECLARE match date; tomorrow date; BEGIN SELECT TO_DATE((SELECT EXTRACT(\'year\' FROM $1) || \' \' || EXTRACT(\'month\' FROM $1) || \' 01\'), \'YYYY MM DD\')INTO tomorrow; LOOP tomorrow := tomorrow + \'24 hours\'::interval; IF (1 = EXTRACT(\'day\' FROM tomorrow) ) THEN RETURN match; END IF;IF ($2 = EXTRACT(\'dow\' FROM tomorrow)) THEN match := tomorrow; END IF; END LOOP; END; ' LANGUAGE plpgsql; - -- Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200402252206 -----BEGIN PGP SIGNATURE----- iD8DBQFAPWL1vJuQZxSWSsgRAu0tAKDO7oKbxOmfDpCUYpeDSwCwyALs7QCgvKT3 x+aqhBqzm9F87ESbsMe6HdQ= =AriI -----END PGP SIGNATURE-----
Greg Sabino Mullane wrote: >>How to find the last sunday/mon..../sat of any given month. > > There is probably a smoother way to do it, but here is a > quick little function to do what you ask. Feed it a date > and a number, where 0 is Sunday, 1 is Monday, etc. How about this: regression=# select date_trunc('month', current_date + '1 month'::interval); date_trunc --------------------- 2004-03-01 00:00:00 (1 row) Joe
Greg Sabino Mullane wrote: >>How to find the last sunday/mon..../sat of any given month. > > There is probably a smoother way to do it, but here is a > quick little function to do what you ask. Feed it a date > and a number, where 0 is Sunday, 1 is Monday, etc. oops...forget my last reply...I was a bit too quick on the draw. Try this instead: regression=# select date_trunc('month', current_date + '1 month'::interval) - '1 day'::interval; ?column? --------------------- 2004-02-29 00:00:00 (1 row) Joe
At 11:30 PM 2/25/04, Joe Conway wrote: >Greg Sabino Mullane wrote: >>>How to find the last sunday/mon..../sat of any given month. >> >>There is probably a smoother way to do it, but here is a >>quick little function to do what you ask. Feed it a date >>and a number, where 0 is Sunday, 1 is Monday, etc. > >oops...forget my last reply...I was a bit too quick on the draw. Try this >instead: > >regression=# select date_trunc('month', current_date + '1 >month'::interval) - '1 day'::interval; > ?column? >--------------------- > 2004-02-29 00:00:00 >(1 row) > >Joe But the original request was for a specific day-of-week. So use Joe's answer above to get last day of month, and use 'dow' to determine the day-of-week of that day. Let's call that dow1. If the day-of-week being asked for is dow2 then: if dow1 < dow2 return (last-day-of-month - dow1 - 7 + dow2) else return (last-day-of-month - dow1 + dow2) I'm no good at coding pgsql functions, so I'm not going to attempt proper syntax. Frank
On Thu, Feb 26, 2004 at 03:07:52AM -0000, Greg Sabino Mullane wrote: > > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > > > How to find the last sunday/mon..../sat of any given month. > > There is probably a smoother way to do it, but here is a > quick little function to do what you ask. Feed it a date > and a number, where 0 is Sunday, 1 is Monday, etc. select date_trunc('month', current_date + '1 month'::interval) - '1 day'::interval + (((3 - 7 - to_char(date_trunc('month',current_date + '1 month'::interval) - '1 day'::interval,'D')::int) %7)||' days')::interval; The "3" is the day of week (1 = Sunday, 7 = Saturday). This equation will return the date of the last "x" of the current month. Change "current_date" to be whatever date you wish to find the last "x" of. Michael -- Michael Darrin Chaney mdchaney@michaelchaney.com http://www.michaelchaney.com/