Srini,
If I understand correctly, you want all rows that are less than sysdate - 90
minutes. There are a lot of ways to approach this. I guess you might be
getting stuck on a couple of points. You can convert the tm_date and tm_time
columns into a real date with a time component -- e.g.
"to_date(tm_date||tm_time,'YYYYMMDDHH24:MI')". The other key for you is to
get the date minus 90 minutes. 24 hours time 60 minutes in an hour gives you
the total number of minutes in a day. You can divide 90 by this number to
give you the amount to subtract and the correct date/time:
1 select to_char(sysdate,'MM/DD/YYYY HH24:MI:SS') now,
2 to_char(sysdate - 90/(24*60),'MM/DD/YYYY HH24:MI:SS')
3* from dual
SQL> /
NOW TO_CHAR(SYSDATE-90/
------------------- -------------------
05/04/2002 09:29:12 05/04/2002 07:59:12
So, with those two key pieces, you could write the query as follows:
1 select *
2 from tbl_tmstmp
3 where to_date(tm_date||tm_time,'YYYYMMDDHH24:MI') <
4* to_date('20020504 03:00','YYYYMMDD HH24:MI') - 90/(24*60)
SQL> /
TMSTMP_ID TM_DATE TM_TIM
---------- ---------- ------
1 20020502 12:30
2 20020503 19:30
3 20020503 23:30
4 20020504 00:30
For your real query, simply replace "to_date('20020504 03:00','YYYYMMDD
HH24:MI')" with sysdate.
Regards,
Larry G. Elkins
[EMAIL PROTECTED]
214.954.1781
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of sarath
> kumar
> Sent: Saturday, May 04, 2002 9:38 AM
> To: Multiple recipients of list ORACLE-L
> Subject: sysdate - 90 mins query
>
>
> i hava a table tbl_tmstmp with date and time as 2
> different columns (tm_date number)and tm_time
> varchar2(6).
> select * from tbl_tmstmp;
> tmstmp_id tm_date tm_time
> -------- -------- -------
> 1 20020502 12:30
> 2 20020503 19:30
> 3 20020503 23:30
> 4 20020504 00:30
> 5 20020504 02:30
> 6 20020504 08:30
>
> i have requuirement to pickup the records which are
> sysdate - 90 minutes. Assuming the sysdate is 20020504
> and time is 03:00 AM i need to pick up the first 4
> records.
>
> is it possible without using the UNION as DBAS are
> picky about using UNIONS.
>
> thanks
> Srini.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Larry Elkins
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
