SQL & PL/SQL :: Date And Time Calculation
Jun 10, 2011
T1 = 06-Jun-2011 4:00PM
T2 = 10-Jun-2011 11:AM
Calculation is Required.
If at the end of Day Hours of T1 > 6 hours then it should calculate 1 Day.
same with T2. hours of T2(from begining of Day till end Time (11AM), it should again caculate if >6 hours then 1 Day.
remaining will be date calculation. this is how T2 - T1 should give us 5 Days.
Detail
06-Jun-2011 4:00PM to 06-Jun-2011 12:00AM (8 hours > 6 = 1 Day)
06-Jun-2011 12:00AM to 07-Jun-2011 12:00AM = (1 Day)
07-Jun-2011 12:00AM to 08-Jun-2011 12:00AM = (1 Day)
08-Jun-2011 12:00AM to 09-Jun-2011 12:00AM = (1 Day)
09-Jun-2011 12:00AM to 10-Jun-2011 12:00AM = (1 Day)
10-Jun-2011 12:00AM to 10-Jun-2011 11:00AM (11 hrs > 6 = 1 Day)
this is how 5Days calculation is required.
View 1 Replies
ADVERTISEMENT
Jul 5, 2012
I have time fields that have been converted from a 12hr clock to a 24hr clock and these fields hold only the time...no date.
I need to be able to determine the difference between column A and B eg.
Column A: Column B:
11:00 14:00
I can remove the ':' by using the replace command, but as I need to run on a actual 24hr clock I am not sure how to tackle the calculation as it is not the full oracle datetime format.
View 8 Replies
View Related
May 30, 2010
Check the following
SELECT FROM_TZ(CAST(TO_DATE('1999-12-01 11:00:00',
'YYYY-MM-DD HH:MI:SS') AS TIMESTAMP), 'gmt')
AT TIME ZONE '+05:30' "Time at Time zone"
FROM DUAL;
Time at Time zone
01-DEC-99 04.30.00.000000 PM +05:30
My requirement is that
I want to add 2 hours to DateTime i get here i.e. add two hours in result and display the resultant date and tine in time zone '+05:30'. I also want to check that the resultant time falls in business hours (9 am to 6 pm).
View 11 Replies
View Related
Oct 20, 2011
Currently I am working on payroll system where I have to calculate employees working hours/late coming hours/early going hours against its roster which is defined in the beginning of every month/week.
In roster form user define shift of every employee like
Code Name Shift
7 Saad Nafees A - 09:00 17:00
492 Muhammad Nasir Shahzad B - 17:00 01:00
243 Muhammad Tahir C - 01:00 09:00
Roster table structure
code varchar2
name varchar2
Shift date
Remarks varchar2
shift table structure
code varchar2
timein date
timeout date
latetime date
Hdaytime date
Oracle stores both date and time information in date data type, suppose today user change shift timings from 17:00 to 17:30 or user change timein/timeout in attendance form then oracle will store current date with user define timings.
Now this is the main problem which I am facing because whenever you calculate difference between timein and timeout or compare with its roster then output comes wrong because oracle returns total no of hours whenever you minus two dates.
View 11 Replies
View Related
Dec 12, 2011
Till statspack we had
elapsed time = time spent on waits + time CPU was used
Total time during snaps = Elapsed time + (may be) time waited for CPU...In AWR is it possible to draw such equation? I can see that the AWR report has following elements
1) End Snap time - Begin Snap time
2) DB time - as mentioned at the top of AWR report
3) DB CPU - in "Top 5 Timed Foreground Events" (I assume this is 'CPU used by sesson timing' in statspack)
4) Total of time for all Statistics in "Time Model Statistics"
5) BUSY_TIME + IDLE_TIME - "Operating System Statistics"
Time between 2 snapshots? or what else? Also for which seconds to multiply to 'DB Time(s)' per second and 'DB CPU(s)' per second in Load Profile to get the db time and CPU time?
View 2 Replies
View Related
Apr 12, 2011
calculating values of A & B
(both these fields a_std and a_time are coming as varchar from the parent table in a cursor.(basically they are time period and actual arrival time respectively)
i was juggling with the attempt to make varchar to timestamp or date..but caught with Round up /Round down)
Formula ->
A = Round down [A_TIME - A_STD]
B = Round up [A_TIME) - 10 minute + A_STD]
where
A_TIME VARCHAR2(8) N Time (Format" HH:MM AM/PM") eg "3:50 PM"
A_STD VARCHAR2(5) N Standard time (Format" HH:MM") eg "1:00"
Allowed values for A & B after round up/down = multiple of 10 ( 11:00,11:10,11:20 etc.)
View 10 Replies
View Related
Jun 4, 2012
When I try to extract the date tag value from XML data, the time stored in 20120602153021 format i.e., YYYYMMDD24HHMISS format. The following statement extracts only date as 02-JUN-12 however do not extract the time part.
If I try the same in SQLplus with to_date it works however fails in PL/SQL.
XML data:
<?xml version="1.0"?>
<RECORD>
<REGTIMESTAMP>20120601130010</REGTIMESTAMP>
</RECORD>
PL/SQL Extract:
CURSOR c_xml_record
IS
SELECT extract(value(d), '//ACTIVATIONTS/text()').getStringVal() AS REGTIMESTAMP,
FROM t_xml_data x,
[code].......
View 3 Replies
View Related
Jun 6, 2012
I am not able to load complete date along with time in the date column. here is my table desc
DESC STAGE
Name Null Type
----------------------------------
TABLE_NAME NOT NULL VARHCAR(20)
RECORDCOUNT NUMBER
CREATED_DATE NOT NULL DATE
my control file is like this
LOAD DATA
APPEND
INTO TABLE SCOOP.STAGE
FIELDS TERMINATED BY ","
( TABLE_NAME
,RECORDCOUNT
,CREATED_DATE DATE(16) "YYYYMMDDHH:Mi:SS"
)
the data gets loaded, but it appears like this in the table
HIGHSCHOOL3080606-JUN-12
MIDDLESCHOOL8768006-JUN-12
BUT I WANT COMPLETE DATE AND TIME (HH:MI:SS) , HOW CAN I GET IT (THIS IS HOW I WANT 06-JUN-12 11:07:33)
View 10 Replies
View Related
Dec 23, 2012
I want to reset my date to this format: 12/31/2012 11:59:59 PM - see code below:
DECLARE
v_latest_close DATE;
BEGIN
v_latest_close := TO_DATE ('12/31/2012 23:59:59 ','MM/DD/YYYY HH24:MI:SS');
DBMS_OUTPUT.PUT_LINE('The new date format is : '|| v_latest_close);
END;
the code above displays only : 12/31/2012 instead of 12/31/2012 11:59:59 PM
View 4 Replies
View Related
Sep 16, 2011
I need to convert the type of GMT to Local time, during data entry. I have a source table and a target table.
To make the insert get the most value, I have no problems:
insert into destination (SELECT * FROM WHERE source pointnumber = (SELECT MAX (pointnumber) FROM source));
But since times are different between the tables, I want to convert the data obtained to GMT -4:30 Time (Caracas - Venezuela), before inserting it.
I can use a function?
View 1 Replies
View Related
May 21, 2013
I am having problems with the XMLTable function. I cant get it to see the entire date/time value in a date field. This wont work
select x1.* from XMLTABLE('/DOCUMENT' passing xmltype('<DOCUMENT><STR>abc def ghi</STR><NUM>1234</NUM><DT>2013-02-17T04:24:02</DT></DOCUMENT>') columns STR varchar2(25), NUM number, DT date) x1;
However if I change the DT tag to just the date only "2013-02-17" it works. Why wont Oracle see the entire date/time format even if its ISO 8601 compliant?
Oracle DB: 11.2.0.3.0
View 3 Replies
View Related
Feb 21, 2011
I have one table with 10 rows and its frequently updated now my question is that, how can it get the latest date of updation of that rows?
View 4 Replies
View Related
Apr 24, 2005
I only know to retrieve data (date & time) by the following statement : "select sysdate from ..."
but now I want to make the fields current date and current time separately and put in a adult file with these two fields. how to do it.
View 11 Replies
View Related
Sep 28, 2011
I had a database at microsoft access but i had move it to the oracle database.
When i check the datatype at oracle, date column is at DATE datatype. but it only display date. like in oracle. it display:
oracle_date.PNG ( 74.61K )
Number of downloads: 4
But when i link it to microsft access. like i will control the oracle from access. the result is at below:
access_date.PNG ( 136.11K )
Number of downloads: 3
So, my problem is how to display the date and time fully in oracle?
View 1 Replies
View Related
Mar 1, 2010
iam having a table A where column ABC is a varchar field with varchar2(50).
currently data stored is like this.
02-27-2009 11:01:33
02-27-2009 11:01:46
03-06-2009 09:07:18
now i want UTC Date time should be in format 2010-02-24T17:08:09Z. in the above format.
View 9 Replies
View Related
Apr 3, 2012
is there possible to retrieve date with its time in oracle ,if we didnot provide time while insertion? like hiredate column of emp table can we get hiredate of each emp with its time of joining. i am using "11g Enterprise Edition Release 11.2.0.1.0"
View 3 Replies
View Related
Nov 1, 2012
I have a question with Oracle sql developer, i've installed oracle 11g express edition, and i want to insert values 'date' and 'time', but i only got the resultat like this:
Who can tell me how can i do to show just date info in 'date' and same for the 'time'?
View 7 Replies
View Related
Apr 4, 2010
I am converting data from an old paradox table to a new oracle table, one of the problems im having is incompatibility with date and time formats:
some columns contain times in the format : "00:00:00" eg..... "15:00:00"
some columns have date in the format: "dd/mm/yyyy" eg....... "21/08/2000"
some columns have time and date eg.. "05/09/2000 15:49:39"
Currently I have the data held in tables within an access database, and in CSV format.
eg, I have dates like 03/04/2010 which i need to be 03-APR-10....
how I can get the following into Oracle date formats? there is over 1000 records so manual conversion is out of the question
View 21 Replies
View Related
Jul 21, 2011
I have the following piece of
SELECT decode(substr('18900607|000000|20110718112109|23972|002',
17,
14),
'00000000000000',
NULL,
(to_date(substr('18900607|000000|20110718112109|23972|002',
17,
14),
'YYYY/MM/DD HH24:MI:SS')))
FROM dual;
When I run the above code I get an incorrect display ie. "18-JUL-11". when I remove the decode I get the correct display ie."2011/07/18 11:21:09 AM"
View 2 Replies
View Related
Apr 19, 2013
Create table time_in (
Emp_id varchar2(6),
at_date date,
time_in varchar2(6),
status varchar2(2)
)
[Code]....
-------------- required restul----------
emp_id at_date time_in at_date time_out
------ ------- ------- -------- --------
00009 16/03/2013 09:47 17/03/2013 09:45
00009 17/03/2013 09:48 17/03/2013 10:43
00009 18/03/2013 09:57
00009 19/03/2013 09:38 19/03/2013 05:22
00009 20/03/2013 09:48 20/03/2013 05:24
status 01 = time in
status 02 = time out
View 13 Replies
View Related
Aug 17, 2011
SQL> desc guide;
Name Null? Type
----------------------------------------- -------- ----------
GUIDE_ID NOT NULL NUMBER(20)
CHANNEL_ID NOT NULL NUMBER(5)
SHOW_DATETIME DATE
[code]....
when I run this query it only displays the date but no time.how to display it with the date and time?
View 2 Replies
View Related
May 13, 2013
I have a sql id,I want to figure out how many number of times that sql was executed since last 30 days.I can get some details from dba_hist_active_sess_history.
But how to get count(total executions in one day )like wise for 30 days or so.and How can i map SNAP_ID to date&time .
View 3 Replies
View Related
Dec 15, 2012
We have oracle 10g R2 on windows...
I have a table test and it contains date datatype column JDATE;
SQL> desc test
Name Null? Type
----------------------------------------- -------- --------------------
EMPNO NUMBER
EMPTYPE VARCHAR2(20)
SALARY NOT NULL NUMBER
JDATE DATE
DEPTNO NOT NULL NUMBERSELECT TO_CHAR(JDATE,'DD/MM/YYYY HH24:MI:SS') JDATE FROM DUAL;
JDATE
1/11/2010 4:17:29 PM
1/11/2010 4:15:47 PM
1/5/2010 3:50:44 PMIn the above case i want to update test table and increase the minut of each row by 1 minut.
like
for 1/11/2010 4:17:29 PM It would be like 1/11/2010 4:18:29 PM.
for 1/11/2010 4:15:47 PM it would be like 1/11/2010 4:16:47 PM.Can we do this...
View 4 Replies
View Related
Dec 6, 2012
converting text values that I have to date and time format (without the millisecs and AM/PM values).
The following is an example:
04-DEC-12 07.47.58.000 AM
or would i need to change this in excel before importing this type of data.
View 6 Replies
View Related
Jan 31, 2013
I am running following statement
select TO_DATE( TO_CHAR(CAST((B.EOAWDTTM_MODIFIED) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),'YYYY-MM-DD') from PS_SR_EX_ER_AW_VW B where rownum < 5
and It gives me the error
ORA-01830: date format picture ends before converting entire input string 01830. 00000 - "date format picture ends before converting entire input string"
View 5 Replies
View Related
Aug 19, 2010
When I select sysdate from dual it shows me the time in EST. But when I try to fetch the time from a column which is being updated almost every minute with sysdate of the time it is being updated, it shows me the time in UTC that is 4 hours added to the time when it was logged.
SQL> select * from nls_database_parameters;
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET WE8ISO8859P1
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
[code]..
View 3 Replies
View Related
Mar 3, 2011
I am working with Oracle 8i and I have a problem while applying the below query:
select to_char(ENTRYDATE,'MM-DD-YYYY HH24:MI:SS') from TBL_BANDWIDTH_READS
where ENTRYDATE > sysdate-1
and rownum <10
03-03-2011 00:00:00
03-03-2011 00:00:00
03-03-2011 00:00:00
03-03-2011 00:00:00
[code].....
The time appears as 00:00:00. I said it's something weird because if I take the where condition off, then something like this appears:
select to_char(ENTRYDATE,'MM-DD-YYYY HH24:MI:SS') from TBL_BANDWIDTH_READS
12-22-2006 13:13:27
12-22-2006 13:13:27
12-22-2006 13:13:27
12-22-2006 13:13:27
12-22-2006 13:13:27
12-22-2006 13:13:27
12-22-2006 13:13:27
I am using the same query on a different table and there's no problem on it:
select to_char(ENTRYDATE,'MM-DD-YYYY HH24:MI:SS') from TBL_XX_PROGRAM
where ENTRYDATE > sysdate -10 and rownum <10
03-03-2011 17:06:48
03-03-2011 17:06:48
03-03-2011 16:59:08
03-03-2011 16:59:08
03-03-2011 12:04:21
I checked the DDL of both tables and the only difference is that the TBL_XX_PROGRAM have the ENTRYDATE field defined as not null and the ENTRYDATE on the TBL_BANDWIDTH_READS does not have the same constraint, could it affect?
View 3 Replies
View Related
Jul 26, 2011
Is there a way I can find what the last date/time and index was used for a select...
I have a table with several indexes on them, which I beleive are not being accessed.
I use the following the query to find indexes that where not accessed in a while but this I believe is limited my my workload repository retention, which is set to 90 days.
select index_name from dba_indexes where table_name='<table name>'
and index_name not in (select c1 from(
select p.object_name c1, p.operation c2, p.options c3, count(1) c4
from dba_hist_sql_plan p, dba_hist_sqlstat s
where p.object_owner = 'MTAS' and p.operation like '%INDEX%' and p.sql_id = s.sql_id
group by p.object_name, p.operation, p.options
order by 1,2,3))
Without increasing my repository retention is there a way I can get the last date/time, which an index was used instead of just saying it has not been used in 90 days (retention setting). Is this information kept in the SQL plan?
View 2 Replies
View Related
Jan 13, 2013
create table top22 (timed timestamp);
insert into top22(timed)
values (current timestamp),(null);
select timed from top22;
2013-01-14 10:50:22.17
NULL
select 'no is '||coalesce("num",'') from "bifrost"."top22"
getting error in DB2
if i remove this coalesce function
select 'no is '||timed from "bifrost"."top22"
no is 2013-01-14 10:50:22.17
NULL
but i need the second result is no is null
View 9 Replies
View Related
Aug 6, 2013
I have a csv file where a field has values like "Fri Aug 2 19:22:02 EDT 2013". through unix i am able to place the value into an unix variable but the problem is this should be inserted into a date column in my database table. Basically, the relevant data to be extracted is "Aug 2 2013 19:22:02" and then i need to insert the same into my database column. And the date column in the table should displays like '08/02/2013 19:22:02'.
create table tst_var(a varchar2(50))
/
create table test_dt(a date)
/
insert into tst_var values('Fri Aug 2 19:22:02 EDT 2013')
/
commit;
View 4 Replies
View Related
