Re: Date Question

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Tue, 03 Nov 1998 15:11:24 GMT
Message-ID: <363f1548.5613291@dcsun4.us.oracle.com>

On Tue, 03 Nov 1998 14:13:24 GMT, slakkundi_at_my-dejanews.com wrote:

>OK you Oracle gurus,
>
>This is probably an easy one for all of you but I am stumped. I need to round
>April 12, 1971 to the nearest month. (I guess dates after the 15th of the
>month are rounded forward).
>

Try this:

select sysdate the_date,

       decode( greatest( to_char( sysdate, 'dd' ), 14 ), 14,
               to_char(last_day(add_months(sysdate,-1))+1, 'Month' ),
               to_char(last_day(sysdate)+1), 'Month' ) round_month

  from dual
/

eg.

SQL> select to_date( '12-APR-71' ) the_date,   2 decode( greatest( to_char( to_date('12-APR-71'), 'dd' ), 14 ), 14,

  3                 to_char(last_day(add_months('12-APR-71',-1))+1,
'Month' ),
  4                 to_char(last_day('12-APR-71')+1, 'Month') )

round_month
  5 from dual
  6 /

THE_DATE ROUND_MON
--------- ---------
12-APR-71 April

SQL> select to_date( '16-APR-71' ) the_date,   2 decode( greatest( to_char( to_date('16-APR-71'), 'dd' ), 14 ), 14,

  3                 to_char(last_day(add_months('16-APR-71',-1))+1,
'Month' ),
  4                 to_char(last_day('16-APR-71')+1, 'Month' ) )

round_month
  5 from dual
  6 /

THE_DATE ROUND_MON
--------- ---------
16-APR-71 May

>Also how can I sum dates i.e.
>
>Items were sold on 1/1/97, 1/4/97
>
>So I should get a count of 2

Not really sure what you mean? Do you want a count of all the records in which items were sold?

select count(*)
  from T1
 where items were sold

Hope this helps.

chris.

>
>
>Help. Thanks a bunch in advance.
>
>Sam Lakkundi&#137;
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Tue Nov 03 1998 - 09:11:24 CST