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

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 - 09:55:05 CDT