Re: SQL Date Challenge

On Mon, 04 Jun 2001 10:31:52 -0700
"Josh Berkus" <josh@agliodbs.com> wrote:
>     Can anyone come up with a purely declarative (i.e. SQL) way to SELECT
> all of the Wednesdays within a given time period?  Or is there, perhaps,
> some trick of the PGSQL date parser I could use?
> 
>     I can think of a number of ways to do this procedurally, but that's
> very awkward for what I need to use the information (to select all
> wednesdays within the last two months for which each staff emember has
> not turned in a timecard).  I'm considering using a regularly updated
> reference table, but it seems like there *must* be a more elegant
> solution.
> 
>     Basically, what I want is: 
> 
> SELECT ALL Wednesdays BETWEEN 5/1/01 AND 6/1/01;
> 
>     Which results in:
> 
> Wednesdays
> -----------
> 5/2/01
> 5/9/01
> 5/16/01
> 5/23/01
> 5/30/01

Try (under postgres 7.1)select footable.somefield from footable where extract(dow from footable.my_date_field) = 3;

from the docs:
 EXTRACT (field FROM source)
 The extract function retrieves sub-fields from date/time values, such as year or hour. source is a value expression
thatevaluates to type timestamp or interval. (Expressions of type date or time will be cast to timestamp and can
thereforebe used as well.) field is an identifier (not a string!) that selects what field to extract from the source
value.The extract function returns values of type double precision. The following are valid values:  .... The day of
theweek (0 - 6; Sunday is 0) (for timestamp values only) 
 
   SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40');   Result: 5

-- 
Your mouse has moved.
Windows NT must be restarted for the change to take effect.
Reboot now?  [OK]