Postgres Pro
Downloads
Home   >   mailing lists

Thread: dow question

dow question

From
Keith Worthington
Date:
Hi All,

I am working on a query which in part is

CASE   WHEN extract(dow from tbl_detail.ship_by_date) = 0      THEN 'Sunday ' || tbl_detail.ship_by_date::text   WHEN
extract(dowfrom tbl_detail.ship_by_date) = 1      THEN 'Monday ' || tbl_detail.ship_by_date::text   WHEN extract(dow
fromtbl_detail.ship_by_date) = 2      THEN 'Tuesday ' || tbl_detail.ship_by_date::text   WHEN extract(dow from
tbl_detail.ship_by_date)= 3      THEN 'Wednesday ' || tbl_detail.ship_by_date::text   WHEN extract(dow from
tbl_detail.ship_by_date)= 4      THEN 'Thursday ' || tbl_detail.ship_by_date::text   WHEN extract(dow from
tbl_detail.ship_by_date)= 5      THEN 'Friday ' || tbl_detail.ship_by_date::text   WHEN extract(dow from
tbl_detail.ship_by_date)= 6      THEN 'Saturday ' || tbl_detail.ship_by_date::text
 
END AS sort_by_string

Is there a better way to do this?  The CASE seems inefficient and wordy 
but perhaps not.

I was hoping for

day_name(tbl_detail.ship_by_date) || tbl_detail.ship_by_date::text AS 
sort_by_string

but AFAIK day_name(date) or something like it is merely my fantasy. ;-)

-- 
Kind Regards,
Keith

Re: dow question

From
Michael Glaesemann
Date:
On Dec 8, 2005, at 11:03 , Keith Worthington wrote:

> Is there a better way to do this?  The CASE seems inefficient and  
> wordy but perhaps not.
>
> I was hoping for
>
> day_name(tbl_detail.ship_by_date) || tbl_detail.ship_by_date::text  
> AS sort_by_string
>
> but AFAIK day_name(date) or something like it is merely my  
> fantasy. ;-)

Will to_char() do what you want? Something like

test=# select to_char(current_date, 'Day YYYY-MM-DD');       to_char
----------------------
Thursday  2005-12-08
(1 row)

http://www.postgresql.org/docs/current/interactive/functions- 
formatting.html

Michael Glaesemann
grzm myrealbox com

Re: dow question

From
Keith Worthington
Date:
Michael Glaesemann wrote:
> 
> On Dec 8, 2005, at 11:03 , Keith Worthington wrote:
> 
>> Is there a better way to do this?  The CASE seems inefficient and  
>> wordy but perhaps not.
>>
>> I was hoping for
>>
>> day_name(tbl_detail.ship_by_date) || tbl_detail.ship_by_date::text  AS 
>> sort_by_string
>>
>> but AFAIK day_name(date) or something like it is merely my  fantasy. ;-)
> 
> 
> Will to_char() do what you want? Something like
> 
> test=# select to_char(current_date, 'Day YYYY-MM-DD');
>        to_char
> ----------------------
> Thursday  2005-12-08
> (1 row)
> 
> http://www.postgresql.org/docs/current/interactive/functions- 
> formatting.html
> 
> Michael Glaesemann
> grzm myrealbox com

Michael,

That will do nicely.  I didn't realize until now after your tip and 
reading the to_char documentation how much I could do with to_char.

to_char(tbl_detail.ship_by_date, 'FMDay, YYYY/MM/DD') AS ship_by_string,

is exactly what the doctor ordered.  Thanks for the tip.

-- 
Kind Regards,
Keith