pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feeddow question
3+ messages / 2 participants
[nested] [flat]
* dow question
@ 2005-12-08 02:03 Keith Worthington <KeithW@NarrowPathInc.com>
0 siblings, 1 reply; 3+ messages in thread
From: Keith Worthington @ 2005-12-08 02:03 UTC (permalink / raw)
To: pgsql-sql
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(dow from tbl_detail.ship_by_date) = 1
THEN 'Monday ' || tbl_detail.ship_by_date::text
WHEN extract(dow from tbl_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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: dow question
@ 2005-12-08 03:17 Michael Glaesemann <grzm@myrealbox.com>
parent: Keith Worthington <KeithW@NarrowPathInc.com>
0 siblings, 1 reply; 3+ messages in thread
From: Michael Glaesemann @ 2005-12-08 03:17 UTC (permalink / raw)
To: KeithW@NarrowPathInc.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: dow question
@ 2005-12-08 03:33 Keith Worthington <KeithW@NarrowPathInc.com>
parent: Michael Glaesemann <grzm@myrealbox.com>
0 siblings, 0 replies; 3+ messages in thread
From: Keith Worthington @ 2005-12-08 03:33 UTC (permalink / raw)
To: pgsql-sql; +Cc: Michael Glaesemann <grzm@myrealbox.com>
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-12-08 03:33 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-08 02:03 dow question Keith Worthington <KeithW@NarrowPathInc.com>
2005-12-08 03:17 ` Michael Glaesemann <grzm@myrealbox.com>
2005-12-08 03:33 ` Keith Worthington <KeithW@NarrowPathInc.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox