> -----Original Message-----
> From: John Dunn [mailto:[EMAIL PROTECTED]]
>
> 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.
a) - read manual on to_number, to_char, to_date conversion functions.
b) 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
