query only specific date only. example: '06-MAY-2010'.
1.from the statement below, it will display out more than 06-mAY-2010.
2.if i want more the date from 03-MAY-2010, 04-MAY-2010 and 05-MAY-2010.
select * from PNG_ORA_SERVER_PERF where SERVER_NAME = 'MLYDESPINTF1' and DATE_TIME >= TO_DATE('06-MAY-2010','DD-MM-YYYY');
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
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 ?
i am using one stored procedure where in one variable which is declare as date value is coming like that '10-OCT-12 11.30.54 AM' and i am inserting this value in one table which has one column vdate with date datatype but it is not inserting there.
View 16 Replies View RelatedI have a date column, where the date values are not stored in a specific pattern. following are the sample value from the column.
8/10/10 12:00 AM
9/22/2010 1:00AM
01/01/2001
9/1/10 6:00 PM
9/22/2009 1:00AM
i want to convert this to a standard format, 'dd/mm'yyyy'.
I want to take AWR report at specific date ex(25-05-2010).
View 2 Replies View RelatedI 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 RelatedAs we know that date datatype can store both date part and time part. If I specify the Date format for my database as 'DD-MM-YYYY HH@$:MI:SS' can i ensure i anyways for a particular columns in the database containing date values the format is 'DD-MM-YYYY' i.e without the time part.Can we specify seperate date formats for specific columsn in database during table creation?
View 1 Replies View RelatedMy transaction of 1 day Jan7,2009 cannot be manipulated by any DML..What shud i do to unlock those records...??
View 10 Replies View RelatedSQL Plus version Oracle8 Enterprise Edition Release 8.0.5.0.0 - Production
PL/SQL Release 8.0.5.1.0 Production
Forms Version : 6i
Reports Version: 6i
O/S : Microsoft Windows Xp professional Version 2002 Service Pack 3
With regards to the above version description here is my query. I have a form which calls report it accepts various parameters like date between, appeal and so on . I want the report to be restricted to the date parameter as passed by the user.Here is my coding which runs report
Declare
Pl_id ParamList;
where_cond varchar2(2500);
Begin
------------Appeal------------------
if
upper(ltrim(rtrim(:appeal)))<> 'ALL' then
where_cond:= where_cond ||'and tbl_donation.appeal_code='||ltrim(rtrim(:blk_ihelp.appeal_code));
else
where_cond:= where_cond||' and tbl_donation.appeal_code is not null';
end if;
-------------Date Option----------------
if
:date_option is not null then
if
:date_option = 'BETWEEN'then
where_cond:=' and tbl_donation.donation_date between '''||ltrim(rtrim(:fdate))||''' and ''' ||ltrim(rtrim(:tdate))||'''';
else
where_cond:=' and tbl_donation.donation_date '||:date_option||''''||ltrim(rtrim(:fdate))||'''';
end if;
end if;
--------------Country-------------------
if
upper (ltrim(rtrim(:country))) <> 'ALL'then
where_cond:= where_cond||'and tbl_donation.country_code='||ltrim(rtrim(:blk_ihelp.country_code));
else
where_cond:= where_cond||'and tbl_donation.country_code is not null';
end if;
-------------Contact Code---------------
if
:contact_code is not null then
if
:contact_code = 'BETWEEN'then
[Code]....
I'm using Oracle 9i Enterprise edition, Is there a select statement to view transaction log for specific date?
View 1 Replies View RelatedI am currently doing a project where i need to write a stored procedure which will be doing the following-
i)it will retrieve multiple columns from multiple tables in a single database(through join) based on certain conditions
II)then it will store the entire data in a certain field(File_data) of staging table
inside file_data a header and a trailer will be present with the records.also the field values will be pipe separated and a new record will start in a new line.
So,the data inside the file_data of staging table will look like this-
H|v1000
transdate|ordnmb|deposite_amt|order_status....
12-nov-09|123456|23.8|C...
4-dec-07|234567|67.7|R...
..........
7-jan-04|567890|54.7|x.....
T|234(record count)
i did this formatting using java, but my project leader wants me to do the formatting using SP,and wants me to use staging table.
I have a table that has hierarchical data within it. I need to select this data in a specific way, showing the hierarchy.
E.g. Data in table (Key is unique)
Lvl KeyParKey HasChild
1k101
1k200
1k301
1k401
2k34k10
2k22k11
2k24k10
2k13k30
2k52k30
2k35k30
2k13k30
2k11k40
3k56k220
3k109k221
3k67k220
4k61 k1090
Etc etc....
That�s generally the format the values would appear in the table if I just did a standard select. I want it displayed in a more hierarchical Parent � child way.
The format I need to get out is as follows:
Lvl KeyParKey hasCh
1k101
2k34k10
2k22k11
3k56k220
3k109k221
4k61 k1090
3k67k220
2k24k10
1k200
1k301
2k13k30
2k52k30
2k35k30
2k13k30
1k401
2k11k40
I want to convert a number to this format using below query but i'm getting error. how to correct the below query.
SELECT TO_CHAR(12345678, '99G9999D99') FROM dual;
error:-###########
When I run a query form the the Query Window in Visuial Studios 2012 all the date fields truncated to 'mm/dd/yyyy', but i need the full date returned. I am able to get full date from TO_char(MyDateField, 'yyyy-mm-dd hh24:mi:ss'), but if I do TO_DATE(MyDateField, 'yyyy-mm-dd hh24:mi:ss') it only returns 'mm/dd/yyyy'. I'm sure this is a simple setting in Visual studios but I cant find it to save my life. Is there there a way to have the full date returned by default?
View 0 Replies View RelatedI 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 RelatedLets say the contents of the table are like this -
key dte_start(number) dte_end(number)
1 20010101 20011231
1 20020101 20081231
1 20030601 20071231
1 20100101 20101231
2 20090101 20091231
2 20090401 20101231
2 20101231 20111231
2 20140101 20141231
3 20080101 20091231
3 20070101 20081231
3 20100101 20101231
3 20120101 20120630
I want the query to resolve overlapping dates as well as merge contiguous segment and leave non-contiguous segments as is. The final result of the query should be like this.
key dte_start(number) dte_end(number)
1 20010101 20081231
1 20100101 20101231
2 20090101 20111231
2 20140101 20141231
3 20070101 20101231
3 20120101 20120630
I tried using the lead over partition along with the standard overlap query and case logic to get the results but couldn't get it to work.
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.
How to get the payment date less than the sch date?
example:
1) for 07/24/2012 sch date the nearest payment date is 07/27/2012.
sch date should be less than the payment date.
Sch dates 07/24/2012 and 07/26/2012 are less than payment date 07/27/2012 but we take the least one.
2) sch date 07/27/2012 is less than the payment date 08/08/2012 so 07/27/2012
Below is the scripts and output needed.
create table cust_sch_tbl
(
customer_NUMBER number,
c_CODE char(5),
sch_DATE date
);
[Code]...
Output needed:
cust cust code payment date sch date
4611 c1 7/27/2012 7/24/2012
4611 c1 8/8/2012 16:57 7/27/2012
4611 c1 10/19/2012 16:37 10/11/2012
4611 c1 11/15/2012 10/30/
We have one oracle table, which has one column "Creation_Date" of the datatype date.
This column has data like
"14-Aug-2010 01:30:30"
"14-Aug-2010 03:30:30"
"15-Aug-2010 01:30:30"
"16-Aug-2010 01:30:30"
"16-Aug-2010 03:30:30"
We want to write SQL query which will group rows by date wise without considering time..I mean for above rows the date wise output should be
Count Date
------- -------
2 14-Aug-2010
1 15-Aug-2010
2 16-Aug-2010
i have a view in a schema of my database 'vw_get_noti_calendar_data' and i want to select some field but it shows an error 'invalid number' and i have observed it but no effect,so where is the error.
my code is---------
SELECT *
FROM aepuser.vw_get_noti_calendar_data
WHERE TO_DATE (TO_CHAR (event_start_date, 'mm/dd/yyyy'), 'dd/mm/yyyy')
BETWEEN TO_DATE (TO_CHAR ('2/28/2012', 'mm/dd/yyyy'), 'dd/mm/yyyy')
AND TO_DATE (TO_CHAR ('2/28/2012', 'mm/dd/yyyy'), 'dd/mm/yyyy')
AND srnum BETWEEN 5 * 1 + 1 AND 5 * 1 + 5;
building SQL query to get the result as shown below.
Create Table Temp
CREATE TABLE TEMP
(
CASEID NUMBER,
SATUS VARCHAR2(1 BYTE),
TRANS_DATE DATE
)
Insert data
[Code]...
I want to build a query which should give output as shown below. Basically i want to select those rows which having minimum trans_date for a given CASEID & Status.
OUTPUT:
CASEIDSATUSTRANS_DATE
100A11/02/2010
100B11/07/2010
100A11/12/2010
200A11/02/2010
200B11/07/2010
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
I have a query with a few tables in join, and I filter data with something like
where mytable.initial_date > sysdate-30
If I use any date, it takes about 5 seconds to run, but if I use
to_date('01010001','ddmmrrrr')
instead of any other dates, it takes just a few milliseconds. Now, it's just a curiosity, but why that date makes the query VERY fast? Does Oracle treat it in some special way? Maybe it knows every date is greatest than that date and doesn't consider the filter?
I want a monthly report where the month wise sum of qty should be displayed in a row for last 12 months. I need to specify the month start date and end date in the query to pick the sum for the particular month. How can i do it in a SQL query?
SUM(decode(to_char(t1.trx_date,'mm/rrrr'),
to_char(add_months(SYSDATE,
-1),'mm/rrrr'),
nvl(t2.quantity_invoiced,
0))) AS qty_01
from t1,t2
where t1.col1=t2.col1
instead of sysdate i have to use start and end of month. Also i am using group by clause on some columns.
I want to write a query to get the time stamp from only one date column,
I tried using a group by clause but getting error "not a group by exp."
Below is the query
SELECT ProdID,ProdRequestID, SUBSTR((max(EVENTTIMESTAMP) - min(EVENTTIMESTAMP)), 18,2)Execution_Time
FROM LOG_TIMESTAMPS where ProdID = 1680988889
group by ProdRequestID
ProdID||ProdRequestID ||EVENTTIMESTAMP
[code]....
In the above i am looking for a diference on ProdRequestId,
Output
ProdIDProdRequestIDEVENTTIMESTAMP
16809888892013-04-09T02-56-34.5025kqcxy03
TABLE - 1
CIDDOB(DATE)
12312-MAR-63
58918-JAN-78
658927-DEC-43
46515-FEB-80
TABLE - 2
DIDDOB_INFO(VARCHAR2)
34425 Mar 1967
123 12 MAR 1963;25 FEB 1974;25 AUG 1978
46515 FEB 1980
I want to join the columns DOB from table -1 and DOB_INFO from table 2 but the datatype of DOB is DATE and DOB_INFO is VARCHAR. TO_DATE function is not working here.
My table has two date columns EFF_DT which is the start date and TERM_DT is the end date. The EFF_DT of the next record should be the next date of the TERM_DT record.
My table looks like this.
Input Table:
-----------
CK_IDPI_IDEFF_DT TERM_DT
Mem1ABC1-Jan-1331-Mar-13
Mem1ABC1-Apr-1331-May-13
Mem1ABC1-Jun-1330-Sep-13
Mem1ABC15-Oct-1331-Dec-13
Mem1ABC1-Jan-1431-Mar-14
Mem1XYZ1-Apr-1430-Jun-14
Mem1XYZ1-Jul-1431-Dec-14
Expected Output:
----------------
CK_IDPI_IDEFF_DT TERM_DT
Mem1ABC1-Jan-1330-Sep-13
Mem1ABC15-Oct-1331-Mar-14
Mem1XYZ1-Apr-1431-Dec-14
In the fourth record, the effective date should be 1-Oct-13 which is the next date to the last TERM_DT 30-Sep-13.As the is the break in the date, the output should show 15-Oct-13 sa the second start date.
Note: Refer to the PI_ID columns, there is a break in the date for the sale PI_ID 'ABC'.
Here I am trying to generate a pseudo column, so that the table with the pseudo column looks like as shown below. and I can use first_value and LAST_value by partitioning on the pseudo column to get the desired output.
1) CNT_VAL is the pseudo column:
-----------------------------
CK_IDPI_IDEFF_DT TERM_DT CNT_VAL
Mem1ABC1-Jan-1331-Mar-131
Mem1ABC1-Apr-1331-May-131
Mem1ABC1-Jun-1330-Sep-131
[code].....
My Query :
----------
I not getting the desired output here as the value in pseudo column is 3.
select CK_ID, PI_ID,EFF_DT,TERM_DT,
(case
when case_CONT - LAG(case_CONT,1) over (ORDER BY EFF_DT) = 0 then to_char(case_CONT)
when case_CONT - LAG(case_CONT,1) over (ORDER BY EFF_DT) <> 0 then to_char(LAG(case_CONT,1) over (ORDER BY EFF_DT) + 1)
else to_char(nvl(case_CONT,0))
[code].....
Scripts:
--------------------
Create table lead_test(
CK_ID varchar2(10),
PI_IDvarchar2(10),
EFF_DTDate,
TERM_DT date);
[code].....
I just want to calculate difference between two dates in YY MM DD HH:MI:SS format through a SQL Query (not function).
Sample data is as follows-
to_timestamp('11-Feb-2008 12:23:00','DD-MON-YYYY HH24:MI:SS')
to_timestamp('2-Dec-2010 04:23:22','DD-MON-YYYY HH24:MI:SS')
or
how to calculate age in YY MM Day HH:MI:SS format. Suppose my DOB is '2-Feb-1988 11:10:15 AM'
I am trying to replace a date range in a query. Don't understand why it's not working, below is the dummy query for the same:
/* v_Employee:= 'select emp_name from employee_table where Join_Year=~Replace1~ and Release_Month <=~Replace2~';
v_Replace1:=2013;
v_Replace2:=7;
pos := 1;
WHILE (INSTR(v_Employee, '~', 1, pos) <> 0) LOOP
IF (pos = 1) THEN
[code]....
Since actual code is like CLOB type, so I have not provided the same. Here after executing this query i found that v_Employee query is getting replaced by v_Replace1 at both of his position i.e "select emp_name from employee_table where Join_Year=2013 and Release_Month <=2013"
But my actual result should: "select emp_name from employee_table where Join_Year=2013 and Release_Month <=7"