From: | Netzach <psql-novice(at)netzach(dot)co(dot)il> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: current_date / datetime stuff |
Date: | 2007-06-04 16:23:00 |
Message-ID: | Pine.LNX.4.58.0706041913170.30078@perpetual.homelinux.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
> Is there anything similiar to: SELECT current_date;
> that will return the date of the first Monday of the month?
In the following examples, replace <month> and <year> with the values
that you want.
Try this:
SELECT ( 1 -
extract(dow from ('1/<month>/<year>')::date)::integer
+ 8
) % 7
This will give you a number from 1 to 7 indicating the day of the month
which is the first Monday from the month of the specified date. The '1'
represents Monday. Replace accordingly if you want a different day.
If you want the full date, you will need to concatenate the day and
month:
SELECT ( 1 -
extract(dow from ('1/<month>/<year>')::date)::integer
+ 8
) % 7
|| '/' || '<month>' || '/' || '<year>';
And finally, if you want an answer for the 'current' month, then try
this monster:
SELECT ( 1 - firstday::integer + 8 ) % 7 || '/' || month || '/' || year
FROM (
SELECT extract(dow from ('1/'||month||'/'||year)::date) AS firstday,
month,year
FROM (
SELECT extract(month from current_date) AS month,
extract(year from current_date) AS year
) AS date_parts
) AS date_parts;
NOTE: Americans may wish to swap the order of day/month. Or not :)
Netzach
From | Date | Subject | |
---|---|---|---|
Next Message | David Gardner | 2007-06-04 16:32:33 | Re: Retreving count of rows returned by a join query |
Previous Message | Jon Sime | 2007-06-04 16:09:04 | Re: current_date / datetime stuff |