Обсуждение: current_date / datetime stuff
Hello, I was hoping someone here may be able to help me out with this one: Is there anything similiar to: SELECT current_date; that will return the date of the first Monday of the month? Please let me know. Thanks, Joshua
> 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
Joshua wrote: > Hello, > > I was hoping someone here may be able to help me out with this one: > > Is there anything similiar to: SELECT current_date; > that will return the date of the first Monday of the month? You might try the following query, or a variation thereof (replacing the "now()"s in the subquery with an arbitrary date if you need the first monday of months other than the current one): select cast(case when d.dow > 1 then d.m + (8 - d.dow) * interval '1 day' when d.dow < 1 then d.m + 1 * interval '1 day' else d.m end as date) as first_monday from (select extract(dow from date_trunc('month', now())) as dow, date_trunc('month', now()) as m) d; -Jon -- Senior Systems Developer Media Matters for America http://mediamatters.org/
On Jun 4, 2007, at 11:23 , Netzach wrote: > SELECT ( 1 - > extract(dow from ('1/<month>/<year>')::date)::integer > + 8 > ) % 7 > SELECT ( 1 - > extract(dow from ('1/<month>/<year>')::date)::integer > + 8 > ) % 7 > || '/' || '<month>' || '/' || '<year>'; > 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 :) > PostgreSQL has a large number of useful date manipulation functions so you can avoid doing this kind of error-prone string work. http://www.postgresql.org/docs/8.2/interactive/functions-datetime.html Michael Glaesemann grzm seespotcode net