Re: HH:MM if today, else Mon, DD in SQL?

In article <8cfuju$rm3$1_at_news.inet.tele.dk>,   "Finn Ellebaek Nielsen" <fen_at_changegroup.dk> wrote:
> That's exactly the reason why you need TRUNC as I pointed out. And the
> original date format was wrong as well, so it should be:
>
> decode(trunc(time_due),
> trunc(sysdate), to_char(time_due, 'hh24:mi'), -- mi, not mm,
hh24 for
> 24-hour
> to_char(time_due, 'Mon dd'))

Karamba! That is it :)

> HTH.
It does! Thank you,

Otis

> Otis Gospodnetic <otis_at_my-deja.com> wrote in message
> news:8cfk40$usf$1_at_nnrp1.deja.com...
> > In article <954912231.12213.2.pluto.d4ee154e_at_news.demon.nl>,
> > "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote:
> > > decode(time_due, sysdate, to_char(time_due,'HH-MM'), to_char
(time_due,
> > > 'DD'))
> > > should be all.
> >
> > Hm, I must have described my problem poorly :)
> > I would like to make a select that is going to return only HH-MM in
> > time_due happens to be the same date as today's date, and return
Mon,
> > DD otherwise.
> >
> > So if I have 4 rows with time_due dates:
> > 2000-04-05 12:13:14
> > 2000-04-05 11:43:29
> > 2000-03-02 23:15:33
> > 2000-02-01 17:15:42
> >
> > I would like to be able to get this from the select:
> >
> > 12:13
> > 11:43
> > May 02
> > Feb 01
> >
> > The first 2 have only HH-MM because their dates are the same as
today's
> > date, but the second 2 have Mon, DD because they are not the same as
> > today's date.
> >
> > I'm not sure if this is possible with just SQL (no stored
procedures).
> >
> > When I tried this:
> >
> > select decode(receive_date, sysdate, to_char(receive_date,'HH-MM'),
> > to_char(receive_date,'Mon, DD')) FROM my_table;
> >
> > I got this:
> >
> > DECODE(
> > -------
> > Apr, 04
> > Apr, 05
> > Apr, 04
> > Apr, 04
> > Apr, 04
> > Apr, 04
> > Apr, 05
> > Apr, 04
> > Apr, 05
> > Apr, 04
> > Apr, 05
> >
> > So this gives me 'Apr, 05' for dates that are today, and I'd love
to be
> > able to get just HH-MM for all dates that match today's date and
Mon,
> > DD for all others.
> >
> > Possibile?
> >
> > Thank you,
> >
> > Otis
> >
> >
> > > Sybrand Bakker, Oracle DBA
> > >
> > > Otis Gospodnetic <otis_at_my-deja.com> wrote in message
> > > news:8ce9lr$gkb$1_at_nnrp1.deja.com...
> > > > Hi,
> > > >
> > > > I have a table that has a column with dates.
> > > > When I get rows from that table I would like to be able to show
> > hours
> > > > and minutes if the date happens to be the current date(i.e.
today);
> > > > otherwise I would like to display month and day.
> > > >
> > > > For example, say that I'm displaying something like a to-do
list:
> > > >
> > > > task time due
> > > > ----------------------
> > > > meet Jackie 01:37
> > > > wake up 09:56
> > > > buy shoes April 10
> > > > go to Jelsa April 01
> > > > ...
> > > >
> > > > Is this doable in SQL without stored procedures and without post
> > > > processing of result set (comparing retrieved dates with current
> > date)?
> > > >
> > > > Thank you,
> > > >
> > > > Otis
> > > > P.S.
> > > > Hmm...looks like an SQL question....but I am running Oracle
> > 8.1.5.0.2
> > > > on Linux actually :)
> > > >
> > > >
> > > > Sent via Deja.com http://www.deja.com/
> > > > Before you buy.
> > > >
> > >
> > >
> >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Apr 05 2000 - 13:35:50 CDT