SQL & PL/SQL :: Time Range Include A Specific Hour?
Aug 11, 2010
I'm looking for a way to say if a time range includes a specific hour.
If I was looking to work out if 01:00 to 04:00 includes 02:00, then this works:
CASE WHEN TO_DATE('02:00','HH24:MI') BETWEEN TO_DATE('01:00','HH24:MI') AND TO_DATE('04:00','HH24:MI') THEN 1 ELSE 0 END
But, if the time go over two days (with midnight in between), it doesn't work:
CASE WHEN TO_DATE('02:00','HH24:MI') BETWEEN TO_DATE('18:00','HH24:MI') AND TO_DATE('04:00','HH24:MI') THEN 1 ELSE 0 END
View 3 Replies
ADVERTISEMENT
May 8, 2013
I want to select a specific date/time range in a query. I want to select from 6 AM yesterday through 6 AM today. I know that CURRENT_DATE - 1 will give me yesterday, and I can search between that and the current_date. However, how do I incorporate the specific time in the query?
View 4 Replies
View Related
Aug 8, 2013
select to_date('28-FEB-2013') - TO_DATE('01-FEB-2013') FROM DUAL
gives me 27 days, what should I do to get 28 ? meaning include both the start and end dates in the range ?
View 3 Replies
View Related
Jun 14, 2012
our DB machines time is one hour ahead of Active Directory, as we sync our time with AD, is there any contribution of Database in the wrong time of solaris machines ? and for the resolution of problem, what can be checked at DB side.
View 1 Replies
View Related
May 19, 2013
I am trying to break down the balance_date to display the following groupings:
7:00-17:30 CDT
18:00-4:30 CDT
I currently have the query setup to display by day instead of these time ranges. I would like the output to read
19 May Day
19 May Night
20 May Day
20 May Night
I am fairly new to this, but how would I go about making this change?
SELECT
TO_CHAR(TRUNC(balance_date,'D') + 4,'YYYY') || '-' ||
TO_CHAR(TRUNC(balance_date,'D') + 4,'IW') as year_wk,
TO_CHAR(TRUNC(balance_date,'D') + 4,'IW')as wk,
[Code] ........
View 1 Replies
View Related
Nov 28, 2012
I'm trying to compare a date and time within a certain range.
If the sysdate date/time range falls within the range of the values within the database tables then show a "Yes", otherwise, show a "No". The date works but the time doesn't seem to work. Maybe you can't use a "between" operator for time?
CREATE TABLE REGISTRATION
(
EARLY_REGISTRATION_START_DATE DATE,
EARLY_REGISTRATION_END_DATE DATE,
EARLY_REGISTRATION_START_TIME DATE,
EARLY_REGISTRATION_END_TIME DATE
[code]....
View 5 Replies
View Related
May 29, 2012
I'd like to know if it is possible to track DML actions issued on a specific table by a specific user, for example , i tried :
AUDIT SELECT on SCOTT.DEPT by HR by ACCESS;
I get an error, where is my syntax error ?
i want to know if it's possible to do it without trigger ?
View 7 Replies
View Related
Feb 3, 2011
We have a table with timestamp column and having millions of records.We want to create a materialized view or query, which can give count based
-on some group by columns from table and
-group by on condition (if count > 1000) and
-group by on condition (if timestamp range for that group is > 1hr)
View 4 Replies
View Related
Mar 28, 2013
formatting the data.I want to group the below table data based on the Grade column for a header_data with start_time and end_time displayed in range. I was trying with group by, partitions etc but no luck. I use version 10gr2.
create table rel_data_mf (header_data varchar2(10),start_time varchar2(100),end_time varchar2(100),grade varchar2(1));
--table rel_data_mf data as comma separated values
header_data,start_time,end_time,Grade
ENG,2013-03-29 00:00:00-05:00,2013-03-29 01:00:00-05:00,U
ENG,2013-03-29 01:00:00-05:00,2013-03-29 02:00:00-05:00,U
[Code]...
--Required output
header_data,start_time,end_time,Grade
ENG,2013-03-29 00:00:00-05:00,2013-03-29 03:00:00-05:00,U
ENG,2013-03-29 03:00:00-05:00,2013-03-29 07:00:00-05:00,A
ENG,2013-03-29 07:00:00-05:00,2013-03-29 10:00:00-05:00,U
MATH,2013-03-29 00:00:00-05:00,2013-03-29 03:00:00-05:00,U
MATH,2013-03-29 03:00:00-05:00,2013-03-29 07:00:00-05:00,B
MATH,2013-03-29 07:00:00-05:00,2013-03-29 13:00:00-05:00,U
View 6 Replies
View Related
Apr 12, 2010
I need to schedule a procedure on SUN, MON,TUE, WED and THU at 6:30 AM. make sure the folloiwng code is o.k.
begin
dbms_scheduler.create_job
(job_name => 'Load-Data_Calc',
job_type => 'STORED_PROCEDURE',
[code]...
View 5 Replies
View Related
Nov 9, 2012
I'd like to stop Recovery in specific time. For instance ; My db works Day-1 and Every day Recovery starts 20:00 and finish 05:00. If it is possible that when DB time reach to sync 03:00, I'd like to stop recovery.
DB version Oracle 10.2.0.5 .....
View 5 Replies
View Related
Sep 9, 2011
here we have an scenario where we want to find out all the sql statements that are executed in a particular time. The sql statements are executed via our application. I tried in awr report but it shows only the sql query which has taken long time to execute. and i even tried in V$session and V$sqlarea. how to view the executed sql statements in a particular session/current session
View 3 Replies
View Related
Jan 22, 2013
Is there a way where I can check users/IPs who connect to the database in a specific time 2 days ago?
View 5 Replies
View Related
May 29, 2013
Can we make Data Guard to take clone Primary Database at Specific Time?
e.g. Clone Primary Database at 10 AM and at 6 PM?
View 10 Replies
View Related
Jul 22, 2013
I have an application connected to Oracle 11g that sends its own querys to the db based on what the user is clickng on. The applicaiton is connected via one user id and I was wondering, is there a way that I can capture the tiem each query starts, the sql itself, and the amount of time it took to fetch the data?
View 7 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
Apr 11, 2011
I have a database which consists of various orders and various field.
I have a variable called createddatetime . I want that whenever i should run the database it should display records from
Yesterday 06:00:00 am to Current Date 05:59:59 am
Now to implement this i tried to put this syntax
and to_char(Createddatetime,'dd/mm/yyyy HH24:mi:ss') between 'sysdate-1 06:00:00' and 'sysdate 05:59:59'
But nothing comes up
where as definitely there are records between times because when i do and Createddatetime between sysdate-1 and sysdate I see valid records coming up.
View 3 Replies
View Related
Jun 4, 2010
How can we include a progress bar in forms?
View 1 Replies
View Related
Feb 21, 2011
I have to do a number of database exercises and it's my very first time using SQL. I did all of them, except for one query.It's fairly simple, I need to find for every combination of warehouses (there are 5) and items (25) how many of each items I can stock in said warehouse until until it goes over the capacity. So for example warehouse 1 has a max of 300 pounds and item 1 weighs 1 pound, so I can stock 300 in it. Warehouse 1 and product 2...etc.
Every warehouse has a capacity in pounds and every item has a weight also in pounds. There are 3 tables between warehouse and items (warehouse---shipment---order----order details----items).
I managed to find every instance of warehouse_ID/item_ID/(capacity/weight)...but thats for the existing warehouses/item. If they added a new warehouse or product, I'd be screwed, as they wouldnt show up in my query...here's my query:
select Warehouse_ID, item_ID, (select round(capacity/weight,1) as maximum_capacity
from order_detail
join item using (item_ID)
join order using (Order_ID)
join shipment using (shipment_number)
join warehouse using (warehouse_ID)
group by warehouse_id, item_id
order by warehouse_ID
I keep trying to find a way to include possible new warehouses or products in my query, but I'm at a complete loss. I tried to right and left outer joins with no luck
View 5 Replies
View Related
Aug 31, 2011
how to write this query using all three tables in one query statement.
Result:
Input value of "GM" would return "GM@EMAIL.COM"
Input value of "GM" and "GMC" would return "GMC@EMAIL.COM"
If "GM" is supplied, then only return the "GM@EMAIL.COM" email.
If "GM" and "GMC" is supplied, then only return the "GMC@EMAIL.COM" email
Test Case : GM would return "GM@EMAIL.COM" email
Test Case : GM and GMC would return "GMC@EMAIL.COM" email
select email
from email a,
company b,
sub_company c
where (company = "GM" but no match found in sub_company)
(company = "GM" and sub_company_name = "GMC" match is found in sub_company)
[code]....
View 11 Replies
View Related
Mar 3, 2010
Here is my problem : I have 7 applications to deploy on Forms server, each with there own applicationsX.env file. In formsweb.cfg, I specify an envFile directive in each applications specific section.The problem is that I would prefer not to copy default.env 7 times just to add 2-3 specific directives. I would like to always include default.env + applicationsX.env for example.
Is there a way to specify multiple env file in the envFile directive?
View 7 Replies
View Related
Jan 2, 2008
follow SQL query would lead to an ORA 01850: hour must be between 0 and 23 error? I'm getting this error don't not sure if this query is causing it.
select
mlh_date_begin,
mat_desc,
mlh_alert_text,
mat_code
from s_mlh_mem_alert_history,
s_mat_member_alert_type
where mlh_mem_uid=f_mem_uid
and mlh_date_begin <= trunc(sysdate)
[Code]...
View 3 Replies
View Related
Oct 5, 2011
I want to creat a table using Execute Immediate that includes tablespace name in the create table command. But I don't know the tablespace name that the client is using. Can I incorporate in a script? If yes, then how?
I have written the following proc. While executing , it gives me the error:
ORA-00959: tablespace 'V_TABLESPACE_NAME' does not exist.
create or replace procedure pr_create_table as
v_tablespace_name varchar2(4000);
begin
select distinct tablespace_name into v_tablespace_name
from user_tables
where table_name like 'ABC%';
[code]....
View 20 Replies
View Related
Jan 2, 2013
I am doing an export using the following parfile information:
userid=/
directory=datapump_nightly_export
dumpfile=test_expdp.dmp
logfile=test_expdp.log
full=y
content=all
However when I run this I do not see the sys.aud$ in the log file. I know I can do a seperate export to specifically get the sys.aud$ table but is there any way to include it in with my full export?
View 8 Replies
View Related
Oct 4, 2007
I am receiving the following Oracle ERROR:
HIPDAO -> getXAlerts():SQLExceptionORA-01850: hour must be between 0 and 23
HERE is the code where it's occuring:
public List xMethod(
int memUID)
throws DAOException {
[Code].....
The error doesn't make sense because I don't think I'm setting a date anywhere in the code.
View 3 Replies
View Related
May 9, 2010
I wish to query the pro_vitual_bytes by hourly average by server name from 04/01/2010 to 04/02/2010. how i going to perform the query.
SERVER_NAME DATE_TIME PRO_VITUAL_BYTES
MLYORABA04/01/2010 00:00:402,238,312,448.00
MLYORABA04/01/2010 00:01:402,241,458,176.00
MLYORABA04/01/2010 00:02:402,238,312,448.00
MLYORABA04/01/2010 00:03:402,241,458,176.00
[Code]....
View 4 Replies
View Related
Apr 15, 2013
After prepared this xml . I make some edit(like add two record) in my emp and dept table i need every one hour update of my xml . How i will do this one ?
select xmlelement("AllDepartments",
xmlagg(
xmlelement("Department", xmlattributes(d.deptno as deptno, d.dname as dname),
xmlagg(
[code].........
View 3 Replies
View Related
May 16, 2013
I am using oracle 11.2.0.3.0, i have taken database backup using rman , after backup i have added a new datafile . My database has been crash , can i restore the database including the new data file which does not include in backup . I have all the archive logs.
View 6 Replies
View Related
Nov 14, 2012
I'm trying to modify my dbca template to include two control files. One on ASM +RECO and we we're thinking of adding the second one on the database filesystem $ORACLE_BASE/something/something Within the template there are references to controlfiles two places:
Initparameter:
<initParam name="control_files" value="("{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/control01.ctl", "{ORACLE_BASE}/fast_recovery_area/{DB_UNIQUE_NAME}/control02.ctl")"/>
and
<StorageAttributes>
<ControlfileAttributes id="Controlfile">
<maxDatafiles>32767</maxDatafiles>
<maxLogfiles>1024</maxLogfiles>
<maxLogMembers>3</maxLogMembers>
<maxLogHistory>32767</maxLogHistory>
<maxInstances>8</maxInstances>
<image name="control01.ctl" filepath="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/"/>
<image name="control02.ctl" filepath="+RECO"/>
</ControlfileAttributes>
With this configuration there is only one controlfile created and that is on +RECO. What am I doing wrong and also should I add the second controlfile to +DATA instead of fielsystem $ORACLE_BASE?
View 6 Replies
View Related
Mar 29, 2004
how to extract hour part from a date?
suppose my date is like
29-mar-2004 09:20:34
i wanna get only hour from the above date-
View 3 Replies
View Related
