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

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'))

HTH. Finn

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.
Received on Wed Apr 05 2000 - 12:52:46 CDT