> 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
