Re: First day of month, last day of month

On Apr 24, 2008, at 9:15 AM, Colin Wetherbee wrote:

> Frank Bax wrote:
>> Frank Bax wrote:
>>> Nacef LABIDI wrote:
>>>> is there a better method to retrieve all the rows with dates in
>>>> the current month.
>>>
>>> select * from mytable where extract(month from mydate) =
>>> extract(month from now()) and extract(year from mydate) =
>>> extract(year from now());
>> Sorry; I was not thinking clearly - date_trunc is better for this:
>> select * from mytable where date_trunc('month',mydate) =
>> date_trunc('month',now());
>
> I have some code that uses extract() for this sort of thing. Would
> you mind explaining how date_trunc() is better for this?
>
> Most of my extract() results end up in <select> drop-down boxes in
> HTML.

extract will pull specific date unit value out of a given date/
timestamp/interval. date_trunc will "round" a given date/timestamp
down to the given unit.

extract(month from now()) -> 4

date_trunc('month', now()) -> 2008-04-01 00:00:00-05

I typically find date_trunc much more useful but I may just think that
because I've been writing partitioning code a lot lately.

Erik Jones

DBA | Emma®
erik(at)myemma(dot)com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com