RE: Add hours and minutes to date

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Tue, 31 Jul 2001 10:05:15 -0700
Message-ID: <F001.0035A6F4.20010731100154@fatcity.com>

> -----Original Message-----
> From: John Dunn [mailto:john.dunn_at_sefas.co.uk]
>
> I have 2 varchar2 variables, one  containing a number of
> hours and the other
> containing a number of minutes, which I want to add to a date
> variable.

1. - read manual on to_number, to_char, to_date conversion functions.
2. First example - adding a number of days to a date

SQL> -- when adding a number to a date, the number is treated as days SQL> select

  2     to_char (d, 'YYYY/MM/DD HH24:MI:SS') as stored_date,
  3     to_char (d + 1, 'YYYY/MM/DD HH24:MI:SS') as one_day_later,
  4     to_char (d + 2.5, 'YYYY/MM/DD HH24:MI:SS') as twodays_12hours_later
  5  from t ;

STORED_DATE         ONE_DAY_LATER       TWODAYS_12HOURS_LAT

------------------- ------------------- -------------------

2001/07/31 09:56:32 2001/08/01 09:56:32 2001/08/02 21:56:32

c) Another example - creating a date field out of varchar2 values.

SQL> describe t;

 Name                                      Null?    Type
 ----------------------------------------- -------- -----------------------
 YMD                                                VARCHAR2(8)
 HOURS                                              VARCHAR2(2)
 MINUTES                                            VARCHAR2(2)
 D                                                  DATE

SQL> select ymd, hours, minutes, d from t ;

YMD      HO MI D
-------- -- -- ---------
20010817 23 59

SQL> update t
  2  set d = to_date (ymd || hours || minutes, 'YYYYMMDDHH24MI') ;

1 row updated.

SQL> select ymd, hours, minutes,
  2         to_char (d, 'YYYY/MM/DD HH24:MI:SS') as the_date   3  from t ;

YMD      HO MI THE_DATE

-------- -- -- -------------------

20010817 23 59 2001/08/17 23:59:00

SQL>

---

Jacques R. Kilchoer
(949) 754-8816
Quest Software, Inc.
8001 Irvine Center Drive
Irvine, California 92618
U.S.A.
http://www.quest.com Received on Tue Jul 31 2001 - 12:05:15 CDT