subtract minute from date/time
I want to subtract a minute from a date/time How can I do this? John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Dunn 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).
Re: subtract minute from date/time
date-1/(24*60*60) Jack John Dunn john.dunn@sefas.To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] co.uk cc: (bcc: Jack van Zanen/nlzanen1/External/MEY/NL) Sent by: Subject: subtract minute from date/time [EMAIL PROTECTED] 11-04-2002 13:08 Please respond to ORACLE-L I want to subtract a minute from a date/time How can I do this? John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Dunn 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). == De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst Young. Ernst Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst Young applies general terms and conditions, which contain a clause that limits its liability. A copy of these terms and conditions is available on request free of charge. === -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack van Zanen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists
RE: subtract minute from date/time
I know that if you have a date field then you can get the hour using the formula date_field/60/60/24 thus to get the minutes will be date_field/60/60 -Original Message- Sent: Thursday, April 11, 2002 14:08 To: Multiple recipients of list ORACLE-L I want to subtract a minute from a date/time How can I do this? John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Dunn 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hatzistavrou Giannis 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).
Re: subtract minute from date/time
select to_number(to_char(sysdate, 'MI')) from dual; JP On Thu 11. April 2002 13:08, you wrote: I want to subtract a minute from a date/time How can I do this? John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jan Pruner 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).
RE: subtract minute from date/time
date_fld - 1/1440 Iain Nicoll Test and Release De-Regulated Services Internal : 700 2331 External : 0141 568 2331 -Original Message- Sent: Thursday, April 11, 2002 12:08 PM To: Multiple recipients of list ORACLE-L I want to subtract a minute from a date/time How can I do this? John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Dunn 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nicoll, Iain (Calanais) 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).
Re: subtract minute from date/time
To subtract a minute you can do it this way select to_char(sysdate,'DD-MON- HH24:mi:ss') , to_char((sysdate - 1/(24*60)),'DD-MON- HH24:mi:ss') from dual; Hope this answers your query John Dunn [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/11/02 04:38 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:subtract minute from date/time I want to subtract a minute from a date/time How can I do this? John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Dunn 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).
Re: subtract minute from date/time
Try this query: select to_char (sysdate - (1/(24*60) * 5),'HH24:MI:SS') from dual; ^ this subtracts 5 mins from current date time. On Thu, 11 Apr 2002, John Dunn wrote: I want to subtract a minute from a date/time How can I do this? John -- Volkan YILDIRIM Ankara Patent Bureau Ltd. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Volkan YILDIRIM 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).
Re: subtract minute from date/time
Sorry this is one second :-) Jack van Zanen [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: (bcc: Jack van Zanen/nlzanen1/External/MEY/NL) [EMAIL PROTECTED] Subject: Re: subtract minute from date/time 11-04-2002 13:23 Please respond to ORACLE-L date-1/(24*60*60) Jack John Dunn john.dunn@sefas.To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] co.uk cc: (bcc: Jack van Zanen/nlzanen1/External/MEY/NL) Sent by: Subject: subtract minute from date/time [EMAIL PROTECTED] 11-04-2002 13:08 Please respond to ORACLE-L I want to subtract a minute from a date/time How can I do this? John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Dunn 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). == De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst Young. Ernst Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst Young applies general terms and conditions, which contain a clause that limits its liability. A copy
RE: subtract minute from date/time
I'm glad its not just me thats having a bad morning making the numbers work... My understanding of it goes something like this: When working with date/times, oracle considers 1 to mean One day. There are 24 hours in every day, so to get the figure for one hour you use 1/24 eg sysdate - 1/24 would give you the time one hour ago. There are 60 minutes in each hour, so one minute is 1/(24*60) Which is 24 hours times 60 minutes Similarly, if you wanted seconds there are 60 in each minute, so 1 second is 1/(24*60*60) Thats one day divided by (number of hours in a day * number of minutes in an hour * number of seconds in a minute) You can probably do the same thing with decimals, but the numbers are messy. If Oracle handled anything smaller than a second, we'd probably start seeing issues with rounding errors... Hope thats clear and I haven't made any obvious mistakes. Simon Anderson -Original Message- Sent: Thursday, April 11, 2002 14:08 To: Multiple recipients of list ORACLE-L I want to subtract a minute from a date/time How can I do this? John -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).
Append date/time stamp to logfile name (NT 4.0)
OS: NT 4.0 I have scheduled a query against a database that pipes results to a log file. Ex. Select * from any_table; logfile.txt Since I need a trend on this query I need to keep the logfile names unique. Best case scenario is to append a date/time stamp to the logfile name as in 230820011200.txt (daymonthyeartime). Does anyone know how to do this with NT scripting? Any help would be greatly appreciated. Thanks, Chuck Speaks Database Administrator Lithonia Lighting 770-860-3450 http://www.lithonia.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Speaks, Chuck W. 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).
Re: Append date/time stamp to logfile name (NT 4.0)
Chuck, It's doable, and I've seen scripts on the net that do it, but it's a bit cumbersome. However, you could also do it in sqlplus, using spool, where the name of the spool file is taken from a new_value of a date query, i.e., col x new_value fn select to_char (sysdate, 'DDMMHHMI') || '.txt' x from dual; spool fn hth, yosi Speaks, Chuck W. wrote: OS: NT 4.0 I have scheduled a query against a database that pipes results to a log file. Ex. Select * from any_table; logfile.txt Since I need a trend on this query I need to keep the logfile names unique. Best case scenario is to append a date/time stamp to the logfile name as in 230820011200.txt (daymonthyeartime). Does anyone know how to do this with NT scripting? Any help would be greatly appreciated. Thanks, Chuck Speaks Database Administrator Lithonia Lighting 770-860-3450 http://www.lithonia.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Speaks, Chuck W. 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). -- Thanks, Yosi - Yosi Greenfield Oracle Certified DBA [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Yosi Greenfield 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).
RE: Append date/time stamp to logfile name (NT 4.0)
Hello,
Not sure if this is what you're looking for but you can use the following in
a batch file:
for /f tokens=2-4 delims=-/ %%i in ('date/t') do set filedate=%%k%%j%%i
for /f tokens=1-2 delims=: %%i in ('time/t') do set filetime=%%i%%j
ren logfile.log %filedate%%filetime%.txt
This assumes your NT date format is -MM-dd or /MM/ddand
your time format is HH:mm:ss or H:mm:ss both of which are configurable
in CONTROL PANEL -- REGIONAL SETTINGS.
HTH
--
Chris J. Guidry P.Eng.
ATCO Electric, Metering Services
Phone: (780) 420-4142
Fax: (780) 420-3854
Email: [EMAIL PROTECTED]
-Original Message-
From: Speaks, Chuck W. [SMTP:[EMAIL PROTECTED]]
Sent: Thursday, August 23, 2001 12:06 PM
To: Multiple recipients of list ORACLE-L
Subject: Append date/time stamp to logfile name (NT 4.0)
OS: NT 4.0
I have scheduled a query against a database that pipes results to a log
file.
Ex. Select * from any_table; logfile.txt
Since I need a trend on this query I need to keep the logfile names
unique.
Best case scenario is to append a date/time stamp to the logfile name as
in
230820011200.txt (daymonthyeartime). Does anyone know how to do this with
NT scripting? Any help would be greatly appreciated.
Thanks,
Chuck Speaks
Database Administrator
Lithonia Lighting
770-860-3450
http://www.lithonia.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Speaks, Chuck W.
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Guidry, Chris
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).
RE: Date / Time
Title: RE: Date / Time Here is my attempt to display the difference between two dates in the format days:Hours:Minutes:seconds The two dates are dt1 and dt2 If dt1 is later than dt2 then result returned as negative. It should work for differences of +/- 99 days - to increase the range, change '09' at the end of the first line to the size desired. to_char(trunc(greatest(dt2,dt1) - least(dt2,dt1)) * sign(dt2 - dt1),'09') || ':' || to_char(to_date(1,'J') + (greatest(dt2,dt1) - least(dt2,dt1)) ,'HH24:MI:SS') Regards Garry -Original Message- From: Scott Canaan [mailto:[EMAIL PROTECTED]] Sent: 10 July 2001 15:12 To: Multiple recipients of list ORACLE-L Subject: Re: Date / Time Sajid, Unfortunately, I ran into the same problem. I didn't find anything to do it for me, either, so I had to write the pl/sql code. It is very long and messy, but can be done. When I did it, I didn't even attempt the days notation. Here is the code that I wrote: date_diff := trans_cur.modified_date - last_date; date_diff_tot := trans_cur.modified_date - first_date; SELECT decode( trunc( date_diff * 24),0, to_char( trunc( date_diff * 1440), 'FM90') || ':' || to_char( round( date_diff * 86400) - trunc( date_diff * 1440) * 60,'FM00'), to_char( trunc( date_diff * 24),'FM90') || ':' || to_char( trunc( date_diff * 1440 - trunc( date_diff * 24) * 60),'FM00') || ':' || to_char( round( date_diff * 86400 - trunc( date_diff * 1440) * 60), 'FM00')), decode( trunc( date_diff_tot * 24), 0, to_char( trunc( date_diff_tot * 1440),'FM90') || ':' || to_char( round( date_diff_tot * 86400) - trunc( date_diff_tot * 1440) * 60,'FM00'), to_char( trunc( date_diff_tot * 24),'FM90') || ':' || to_char( trunc( date_diff_tot * 1440 - trunc( date_diff_tot * 24) * 60),'FM00') || ':' || to_char( round( date_diff_tot * 86400 - trunc( date_diff_tot * 1440) * 60), 'FM00')) INTO elapsed_1, elapsed_2 FROM dual; I hope this helps. Sajid Iqbal wrote: Hello All I want to display the time elapsed between two dates - in days, hours, minutes and seconds. If I do select date1 - date2, the result is : 12.0194907 Is there a function that will turn the number of days into something more legible? Ideally i'd like to do ; to_char(12.0194907,'DD:HH:MI:SS') but obviously that won't work. Is there a solution other than writing a complex function myself which will have to * by 24, / by 60 and substr etc to get the different bits of the number? Please CC any replies directly to me at [EMAIL PROTECTED] Thanks in advance, Saj. -- Sajid Iqbal Database Team Leader -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sajid Iqbal 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). -- Scott Canaan ([EMAIL PROTECTED]) (716) 475-7886 Life is like a sewer, what you get out of it depends on what you put into it - Tom Lehrer -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Scott Canaan 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). All internet traffic to this site is automatically scanned for viruses and vandals.
Date / Time
Hello All I want to display the time elapsed between two dates - in days, hours, minutes and seconds. If I do select date1 - date2, the result is : 12.0194907 Is there a function that will turn the number of days into something more legible? Ideally i'd like to do ; to_char(12.0194907,'DD:HH:MI:SS') but obviously that won't work. Is there a solution other than writing a complex function myself which will have to * by 24, / by 60 and substr etc to get the different bits of the number? Please CC any replies directly to me at [EMAIL PROTECTED] Thanks in advance, Saj. -- Sajid Iqbal Database Team Leader -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sajid Iqbal 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).
Re: Date / Time
Sajid, Unfortunately, I ran into the same problem. I didn't find anything to do it for me, either, so I had to write the pl/sql code. It is very long and messy, but can be done. When I did it, I didn't even attempt the days notation. Here is the code that I wrote: date_diff := trans_cur.modified_date - last_date; date_diff_tot := trans_cur.modified_date - first_date; SELECT decode( trunc( date_diff * 24),0, to_char( trunc( date_diff * 1440), 'FM90') || ':' || to_char( round( date_diff * 86400) - trunc( date_diff * 1440) * 60,'FM00'), to_char( trunc( date_diff * 24),'FM90') || ':' || to_char( trunc( date_diff * 1440 - trunc( date_diff * 24) * 60),'FM00') || ':' || to_char( round( date_diff * 86400 - trunc( date_diff * 1440) * 60), 'FM00')), decode( trunc( date_diff_tot * 24), 0, to_char( trunc( date_diff_tot * 1440),'FM90') || ':' || to_char( round( date_diff_tot * 86400) - trunc( date_diff_tot * 1440) * 60,'FM00'), to_char( trunc( date_diff_tot * 24),'FM90') || ':' || to_char( trunc( date_diff_tot * 1440 - trunc( date_diff_tot * 24) * 60),'FM00') || ':' || to_char( round( date_diff_tot * 86400 - trunc( date_diff_tot * 1440) * 60), 'FM00')) INTO elapsed_1, elapsed_2 FROM dual; I hope this helps. Sajid Iqbal wrote: Hello All I want to display the time elapsed between two dates - in days, hours, minutes and seconds. If I do select date1 - date2, the result is : 12.0194907 Is there a function that will turn the number of days into something more legible? Ideally i'd like to do ; to_char(12.0194907,'DD:HH:MI:SS') but obviously that won't work. Is there a solution other than writing a complex function myself which will have to * by 24, / by 60 and substr etc to get the different bits of the number? Please CC any replies directly to me at [EMAIL PROTECTED] Thanks in advance, Saj. -- Sajid Iqbal Database Team Leader -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sajid Iqbal 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). -- Scott Canaan ([EMAIL PROTECTED]) (716) 475-7886 Life is like a sewer, what you get out of it depends on what you put into it - Tom Lehrer -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Scott Canaan 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).
Re: Date / Time
Sajid Iqbal wrote:
Hello All
I want to display the time elapsed between two dates - in days, hours,
minutes and seconds.
If I do select date1 - date2, the result is : 12.0194907
Is there a function that will turn the number of days into something more
legible? Ideally i'd like to do ;
to_char(12.0194907,'DD:HH:MI:SS') but obviously that won't work. Is
there a solution other than writing a complex function myself which will
have to * by 24, / by 60 and substr etc to get the different bits of the
number?
Please CC any replies directly to me at [EMAIL PROTECTED]
Thanks in advance,
Saj.
--
Sajid Iqbal
Database Team Leader
Sajid,
The difference of two dates is a number of days. If you want to use a
function similar to the dates function, you have to treat the day and
fractional parts separately, eg
select trunc(date2 - date1) days,
to_char(trunc(sysdate) + (date2 - date1) - trunc(date2 - date1),
'HH24:MI')
This uses a trick, the fact that date + number is a date ('number' is
assumed to be a number of days) - so we can use 0:00 today as 'base' and
just display hours and minutes (and seconds if you need them).
--
Regards,
Stephane Faroult
Oriole Corporation
--
http://www.oriolecorp.com, designed by Oracle DBAs for Oracle DBAs
--
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Stephane Faroult
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).
RE: Date / Time
SELECT 12.0194907 ,FLOOR((12.0194907*86400)/86400) || ':' || TO_CHAR(TO_DATE(ROUND(SUBSTR(12.0194907,INSTR(12.0194907,'.'))*86400),'s '),'HH24:MI:SS') FROM dual HTH Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art ! *1 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. *1 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jamadagni, Rajendra 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).
Re: Date / Time
On Tue, Jul 10, 2001 at 05:26:09AM -0800, Sajid Iqbal wrote:
I want to display the time elapsed between two dates - in days, hours,
minutes and seconds.
If I do select date1 - date2, the result is : 12.0194907
Is there a function that will turn the number of days into something more
legible? Ideally i'd like to do ;
to_char(12.0194907,'DD:HH:MI:SS') but obviously that won't work. Is
there a solution other than writing a complex function myself which will
have to * by 24, / by 60 and substr etc to get the different bits of the
number?
using a as your value (cos it's shorter to write)
trunc(a)||':'||to_char(trunc(sysdate)+a-trunc(a), 'HH24:MI:SS')
You could get away with using
to_char(a+to_date('31-Dec-00'), 'DD:HH24:MI:SS')
if you can ensure that 1 = a 32
--
T.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Tommy Wareing
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).
Re: Date / Time
Hi
The script below may help you - it will display different components in separate
columns, modify it with your two dates and proper table name and remove what you
don't need
select sysdate,
sysdate - to_date('0307200100', 'DDMMHH24MISS') diffr,
trunc(sysdate - to_date('0307200100', 'DDMMHH24MISS')) days,
trunc(mod(sysdate - to_date('0307200100', 'DDMMHH24MISS'), 1) *
24) hrs,
trunc(mod(mod(sysdate - to_date('0307200100', 'DDMMHH24MISS'),
1) * 24, 1) * 60) min,
round(mod(mod(mod(sysdate - to_date('0307200100',
'DDMMHH24MISS'), 1) * 24, 1) * 60, 1) * 60) sec,
to_char(trunc(sysdate - to_date('0307200100', 'DDMMHH24MISS')))
|| ' ' ||
lpad(trunc(mod(sysdate - to_date('0307200100', 'DDMMHH24MISS'),
1) * 24), 2, '0') || ':' ||
lpad(trunc(mod(mod(sysdate - to_date('0307200100',
'DDMMHH24MISS'), 1) * 24, 1) * 60), 2, '0') || ':' ||
lpad(round(mod(mod(mod(sysdate - to_date('0307200100',
'DDMMHH24MISS'), 1) * 24, 1) * 60, 1) * 60), 2, '0') Difference
from dual
HTH
Witold
Sajid Iqbal wrote:
Hello All
I want to display the time elapsed between two dates - in days, hours,
minutes and seconds.
If I do select date1 - date2, the result is : 12.0194907
Is there a function that will turn the number of days into something more
legible? Ideally i'd like to do ;
to_char(12.0194907,'DD:HH:MI:SS') but obviously that won't work. Is
there a solution other than writing a complex function myself which will
have to * by 24, / by 60 and substr etc to get the different bits of the
number?
Please CC any replies directly to me at [EMAIL PROTECTED]
Thanks in advance,
Saj.
--
Sajid Iqbal
Database Team Leader
--
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
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).
RE: Date / Time
Sajid: Try this piece. I use a version of it for my uptime.sql script: TO_CHAR(TRUNC(date1 - date2)) || ' day(s), ' || TO_CHAR(TRUNC(MOD(date1 - date2 - 1, 1) * 24)) || ' hour(s), ' || TO_CHAR(TRUNC(((MOD(date1 - date2 - 1, 1) * 24) - (TRUNC(MOD(date1 - date2 - 1, 1) * 24))) * 60)) || ' minute(s) and ' || TO_CHAR(ROUND(MOD(((MOD(date1 - date2 - 1, 1) * 24) - (TRUNC(MOD(date1 - date2 - 1, 1) * 24))) * 60, 1) * 60, 1)) || ' seconds.' -- Jon Walthour, OCDBA Oracle DBA Computer Horizons Cincinnati, Ohio --- Original Message --- From: Sajid Iqbal [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 7/10/01 9:26:09 AM Hello All I want to display the time elapsed between two dates - in days, hours, minutes and seconds. If I do select date1 - date2, the result is : 12.0194907 Is there a function that will turn the number of days into something more legible? Ideally i'd like to do ; to_char(12.0194907,'DD:HH:MI:SS') but obviously that won't work. Is there a solution other than writing a complex function myself which will have to * by 24, / by 60 and substr etc to get the different bits of the number? Please CC any replies directly to me at [EMAIL PROTECTED] Thanks in advance, Saj. -- Sajid Iqbal Database Team Leader -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sajid Iqbal 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jon Walthour 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).
RE: date+time in milliseconds
Hi Raj, I never heard such time Date can handle century + year + month + day + hour + minute + second but not milliseconds Sinardy -Original Message- Sent: Tuesday, 8 May 2001 1:14 PM To: LazyDBA mailing list can anyone help me with date + time in milliseconds. i want to inpurt the data from a user in this format. rgds raj _ Chat with your friends as soon as they come online. Get Rediff Bol at http://bol.rediff.com Think you know someone who can answer the above question? Forward it to them! To unsubscribe: send a blank email to [EMAIL PROTECTED] To subscribe: send a blank email to [EMAIL PROTECTED] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl Tell yer mates about http://www.farAwayJobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sinardy Xing 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).
RE: date+time in milliseconds
You can store the whole thing as a string in one column or store as two columns: date + milliseconds. The Oracle DATE type does not support time granularity below one second. We have used both methods here - just depends on how you want to use the data later. Ron Morton Database Architect / Administrator Union Switch Signal Inc [EMAIL PROTECTED] -Original Message- From: Sinardy Xing [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, May 08, 2001 2:50 AM To: Multiple recipients of list ORACLE-L Subject: RE: date+time in milliseconds Hi Raj, I never heard such time Date can handle century + year + month + day + hour + minute + second but not milliseconds Sinardy -Original Message- Sent: Tuesday, 8 May 2001 1:14 PM To: LazyDBA mailing list can anyone help me with date + time in milliseconds. i want to inpurt the data from a user in this format. rgds raj _ Chat with your friends as soon as they come online. Get Rediff Bol at http://bol.rediff.com Think you know someone who can answer the above question? Forward it to them! To unsubscribe: send a blank email to [EMAIL PROTECTED] To subscribe: send a blank email to [EMAIL PROTECTED] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl Tell yer mates about http://www.farAwayJobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sinardy Xing 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Morton, Ronald D 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).
RE: date+time in milliseconds
If you get milliseconds in the data and want to keep it, you have to store it in a field different than date, If you don't need to keep the milliseconds, you can use different functions to convert the data. If you need to get the time in milliseconds, you can write a Java stored procedure. You should in fact find the code in the list archives. In either case more details would help someone to help you Witold -Original Message- can anyone help me with date + time in milliseconds. i want to inpurt the data from a user in this format. rgds raj -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).
Re: date time
I figure it out. It's based on Geogrian date. Just add it to "Jan-1-1970'
then use TO_CHAR to convert it
From: [EMAIL PROTECTED]
To: mai huynh [EMAIL PROTECTED]
CC: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: Re: date time
Date: Sun, 11 Mar 2001 12:35:35 -0800 (PST)
On Sat, 10 Mar 2001, mai huynh wrote:
does the date time data in Oracle store in long number? From the
application
interface, we enter data in format like '01/02/2001 10:30:00 PM' , when
I
run the query, I get the result in long number as '915134070' . When I
compare it to SYSDATE ('SELECT * FROM Mytable WHERE entry_date
SYSDATE-60'), I get the error inconsistent data type. How can I compare
the
field 'entry_date' to the last 60 days? Thanks
You will need to include some code to reproduce this.
Personally, I don't know how to make this happen.
Jared
_
Get your FREE download of MSN Explorer at http://explorer.msn.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: mai huynh
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).
