SQL & PL/SQL :: Difference Between Sysdate / Current Date / Current And Local Timestamp

May 30, 2012

What is the difference between the following . In my schema all are giving the same results with some different format

SQL> SELECT sysdate , current_date , current_timestamp , localtimestamp from dual;

----------- ------------ ------------------------------------------------- -------------------------------------------------
5/30/2012 8 5/30/2012 8: 30-MAY-12 AM -04:00 30-MAY-12 AM

View 1 Replies


Adding Time Part To Current Date To Form Timestamp

May 17, 2011

I have a column where I have time stamp with date and time(8-May-10 10:20:55 Am) from that i will get only time(10:20:55 Am)[query for this mentioned below] Now i need to append the time to current date and insert into a Timestamps column only .

so my result should be 17-May-11 10:20:55 Am(Assuming current Date is 17-May-11) . I tried search it by i found a way in sql server by using DataAdd and DataDiff .

query to get the time is:select to_char(to_date('11-MAY-11 05.00.00 PM', 'DD-Mon-YY HH:MI:SSAM'),'HH24:MI:SS') "Time Now" from dual

View 1 Replies View Related

SQL & PL/SQL :: Verify Current Date Is Greater Than 15th Of Current Month

Sep 22, 2010

I need to verify if the current date is grater than the 15th of the current month. If its grater than the 15th of the current month i need to do an action or if else its lesser than 15th of the current month i need to do an other operation.

View 5 Replies View Related

PL/SQL :: Attribute In DB - Return Difference Between Start_date And Current Date

Feb 15, 2013

I have an attribute in my DB called start_date of type date.I want a query that return the difference between the start_date and the current date.for example if start_date = 2/14/2013 *2*:35:00 PM and the current date = 2/14/2013 *1*:35:00 PM the query returns 1.

I want the result in hours.

View 13 Replies View Related

SQL & PL/SQL :: Getting Query For Current Year Data Up To Sysdate?

Jun 19, 2012

I have a table for exampl,

emp sal date
111 200 03-mar-2011
100 200 03-mar-2012
15 200 06-mar-2012
17 200 03-mar-2003
178 200 03-mar-2004
11 200 11-jun-2012
101 200 19-jun-2012

i need sql querry to get current year records upto sysdate.i.e for example this year 2012 so i need all the 2012 year records upto sysdate.

my output like this.....

emp sal date
100 200 03-mar-2012
15 200 06-mar-2012
11 200 11-jun-2012
101 200 19-jun-2012(upto sysdate)

View 2 Replies View Related

Difference Between Switch Logfile And Archive Log Current

Feb 21, 2009

What are the differences of;


I read Oracle's document but still didn't understand the difference in real life-use.

If you were writing a hotbackup script, which would you use at the end of datafile copy?

View 7 Replies View Related

SQL & PL/SQL :: Declare Date Variable And Assign It To Current System Date

Apr 19, 2011

Write a program to declare a date variable and assign it to the current system date. Depending on the day of the month the program should print the following:

If day is 1-10 then print "It is day<day number> of <month name>. It is early in the month".
If day is 11-20 then print "It is day<day number> of <month name>. It is the middle of the month".
If day is 21-31 then print "It is day<day number> of <month name>. It is nearly the end of the month".

For example, if the day is November 30, then print "It is day 30 of November. It is nearly the end of the month".

View 9 Replies View Related

Server Administration :: Find Out Difference Of Data For Some Tables Between Current And Previous Day

May 26, 2011

We want to find out difference of data for some tables between current day & previous day. We can use query with minus operation but it will take lot of time since table size is in range from 200 to 500 GB. We have to do this exercise every day.

View 5 Replies View Related

Get Current Date And Time?

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

PL/SQL :: Current Quarter End Date

Jan 25, 2013

My below query is working fine with sysdate but when m passing a date it's throwing an error to get the current qtr end date..

select add_months(trunc('20-dec-2012','q'),3) - 1 from dualERROR

ERROR at line 1:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER

even after adding the to_date function between trunc and date.                             

View 5 Replies View Related

Find Records That Have Most Current Date?

Apr 13, 2004

I have two tables

--00001--|--00005 --|---ABCD--|01-MARCH-2004
--00002--|--00006 --|---BCDE--|02-MARCH-2004
--00003--|--00007 --|---CDEF--|03-MARCH-2004
--00001--|--00008 --|---DEFG--|04-APRIL-2004
--00003--|--00009 --|---EFGH--|05-APRIL-2004


table_1 contain records on employee and the supervisor they are under at a certain date.

As some employee(00001 & 00003) have a different supervisor from different date, I'll like to extract from table_1 the record of each employee in the table that only contain the supervisor info on the most recent date.And from table_2, i'll like to extract the employee's name. These records extracted from both the tables would the be put into a new table,table_3

Example: For employee 00001, only extract record that have the most recent date which is 04-APRIL-2004 and not on 01-MARCH-2004

00001 |--QWER--|00008 |--DEFG---|04-APRIL-2004
00002 |--ASDF-- |00006 |--BCDE---|02-MARCH-2004
00003 |--ZXCV-- |00009 |--EFGH---|05-APRIL-2004

How to write an SQL statement to perform this?

View 6 Replies View Related

SQL & PL/SQL :: Create Table With Current Date

Jul 22, 2010

How can we create a table with current date in oracle this is to be done daily a job as following today's table name is MYTABLE-2010-07-23

so every day a job is to create a table with tablename-current date

is this possible? and how to

View 11 Replies View Related

Display Data From Current Date To Next 15 Days?

Jan 24, 2007

I have a Employee data as below with EID and DOB

1 03/01/1950
2 14/01/1950
3 27/01/1950
4 02/02/1950
5 04/03/1950
6 12/03/1950
7 28/03/1950
8 28/02/1951
9 08/03/1951
10 28/03/1951

I need a query to display the birth date of all emp in such a way that if i run a query today the dob which lies between today & today+15 days data must be displayed.

View 8 Replies View Related

SQL & PL/SQL :: Date - How To Get Past 12 Months From Current Month

Dec 17, 2011

I need to get the past 12 months from the current month

for example





View 23 Replies View Related

PL/SQL :: Date Is Greater Than Current Time Plus 24 Hours?

May 26, 2013

how to pull data from a table where date is greater than current time (+24 hours)... my date field is in the following format 15-MAR-2013 20:07:00

I want to do something like this

select * from table_A where date_field > (sys_date_time) +24h

as an example, when I run a query @ 4 PM on March 26, I want to pull data that has date > 4 PM March 27

View 6 Replies View Related

Reports & Discoverer :: Current Date Field

Sep 13, 2010

if i make the source for a field is a current date with format dd/mm/rrrr hh:mi:ss and the report published on the application server and the clients from different machines run this report

what the field display on Cline or on DB or On Application Server?

View 3 Replies View Related

SQL & PL/SQL :: Display The Current Date With Post Fix Notation In Oracle?

Jul 2, 2013

Is there any way to display the current date with post fix notation in oracle?

Ex : 2nd ,5th , 3rd , 1st , 8th ,

select to_char(sysdate,'DD') from dual;

View 2 Replies View Related

JDeveloper, Java & XML :: Inserting Current Date From Node?

Mar 22, 2013

How can we insert current date which is present in xml node into a column having date datatype.

View 6 Replies View Related

SQL & PL/SQL :: Difference Between Sysdate And Current_date

Apr 15, 2010

What is the difference between the values of sysdate and current_date ? On querying the database I got the results as sysdate = the date and time for the database server location and current_date = my local system date

If however, i change my local system date - it still shows the correct date.Myunderstanding was that current_date uses the dbtimezone.But..

SQL> select sysdate,current_date from dual;
-------------------- --------------------
15-APR-2010 06:06:14 15-APR-2010 18:36:15
SQL> select dbtimezone from dual;

View 9 Replies View Related

Where Current Of - For Update Of

Oct 5, 2010

I have an employee table that has a paygrp_id that will be used for my subset of employees. For all the employees that have the paygrp_id = 10212 on the employee table I need to update the workbrain_user table to set the flag wbu_cansee_self to 'N'. The join between the employee table and the workbrain_user table is the emp_id.I get the following error when I run this cursor.

Error on line 0
CURSOR wbuFlag_cur
IS SELECT e.emp_fullname, e.paygrp_id,wbu.WBU_CANS

View 3 Replies View Related

SQL & PL/SQL :: How To Get The Name Of Current Procedure

Jul 13, 2011

I have a procedure like below:

create or replace procedure sp_test
vs_proc_name varchar2(40);
--get the name of current procedure ,here is "sp_test"
--[color=red]but ,i do not want the hard code here[/color]
insert into test_Page(proc) values(vs_proc_name);

I don't want to coding like this:


so ,is there any building function to get that ?

View 5 Replies View Related

SQL & PL/SQL :: How To Get Next Not Null Value For Current Row

Dec 14, 2010

following is my input data:



View 4 Replies View Related

Update Where Current Of

Sep 27, 2010

I have a table I need to update called employee_history. It has almost the exact same values as another table called Employee. The difference being that the employee_history has a start date and end date. So it allows you to have overrides in the future. The employee table is static. So it only allows you to see the current data as of today.

I need to write an insert statement that will pull values from employee table and populate the employee_history table but I also have columns in the employee_history table that requires a next value and a start and end date.

From the employee table I want to select e.emp_id, e.emp_firstname, e.emp_lastname, e.shftpat_id, e.client_id from employee e


with the VALUES being (SEQ_EMPHIST_ID.nextval, TRUNC(sysdate), 01/01/3000 and the employee data from the employee table)). I bought the book Easy Oracle PL/SQL Programming but I am not finding what I need. It gives an example of a CURSOR FOR UPDATE and a reference to an UPDATE WHERE CURRENT OF.

The examples are straight forward where you take two columns from table one and update a new table with those values, or where you select a subset of employees that need a commission and update the emp table with that commission.

View 1 Replies View Related

SQL & PL/SQL :: Bulk Collections And Current OF

Sep 2, 2011

I have a driver table from which I need to update another table while, at the same time, record the fact that I have processed each record on the driver table.

The driver table will contain around 3.5 million records, therefore I intended to handle this using some bulk collections, with a LIMIT option so that I don't hit any memory problems.

I would also prefer to commit in batches, or at least handle exceptions using the SAVE EXCEPTION clause. The problem is I seem to be running into an error when trying to make the update to the driver table (the commented out code). With this in, I get the error:

ORA-01410: invalid ROWID
ORA-06512: at "CUST_MAIL_UPDATE", line 217
ORA-06512: at line 38

Can the CURRENT OF not work with the FORALL? What is my best approach here? If I use a FOR LOOP I lose my SAVE EXCEPTIONS exception handling.

The Procedure is as follows:

-- declare some object structures to hold the retrieved data
TYPE driver_rec IS RECORD (
account_no ext_driver.account_no%TYPE,
update_action ext_driver.update_action%TYPE,
customers_rowid ext_driver.customers_rowid%TYPE);
TYPE driver_recs_tt IS TABLE OF driver_rec;
-- cursor to get the records from the driver table

View 10 Replies View Related

Forms :: Getting Current Hostname

May 9, 2011

how can I get the form hostname that it is currently running to? I already tried the form's (forms developer 6i) built in host functionality but it doesn't return the hostname.

Our current approach is to have a shell script that will get and insert the hostname into the table and the form will just query from the table to get the hostname, but that approach has involved too many modules.

Is there an easiest way to get the hostname?

View 5 Replies View Related

SQL & PL/SQL :: Range Between X Preceding And Current Row?

Aug 26, 2011

I have a table and want to calculate the stdev of a parameter over a rolling window (past 250 records, sorted by ID):
stddev(parameter) over (order by ID range between 250 preceding and current row) as SD

I don't get any error, but if I calculate the same in excel (=STDEV(250 row range)), it seems that this code does not the same. Is there something wrong with the 250 row rolling window?

View 4 Replies View Related

Application Express :: How To Get Current Tab Name Or Tab ID

Dec 5, 2012

I would like to hide/show tabs according to users' page privileges list. So I need get current tab ID or tab Name in runtime environment to know if show/hide it for current user.

View 6 Replies View Related

MTS Listener Current Connection

Jan 11, 2013

I have configured MTS server with this parameters:

shared_servers : 42
max_shared_servers : 42
dispatchers : (protocol=tcp)(dispatchers=5)(LISTENER=MDB)

-bash-4.0$ lsnrctl service

LSNRCTL for HPUX: Version - Production on 11-JAN-2013 12:21:51
Copyright (c) 1991, 2009, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=offdb2)(PORT=1522)))
Services Summary...
Service "MDB" has 2 instance(s).
Instance "MDB", status UNKNOWN, has 1 handler(s) for this service...
"DEDICATED" established:15388 refused:0

From doc. I see that the "current" is the number of client connections it is handling, that is, its current load.It is not correlating with the sessions or processes in DB, so what does this means?

Does it show that more than 150 connections try to perform connection simultaneously?

View 8 Replies View Related

PL/SQL :: Query By Current Month

Jul 12, 2013

I have an excel spreadsheet that queries an oracle data for some information.  I am trying to get the information shown to only be from the current month.  I have tried my google-fu, but all of the formulas I have found will not work and return a various error of some sort. The rest of the query works great, but I cant figure this one out.   Select*From&& and I guess i need a where statement, but nothing seems to work.Where"My_Table_Name","OrderDate".

View 6 Replies View Related

Compare Current With Previous Row Until Mismatch

Nov 2, 2011

We have employee salary table which will have salary of an employee on daily basis (only working days). Below is the table structure:

emp_id NUMBER(15) NOT NULL
effective_date DATE NOT NULL
salary NUMBER(15) NOT NULL

Primary key - emp_id, effective_date..This table is yearly partitioned...I have to find out how long the salary is not changed for an employee from given date, and last salary. I am using below query to do this:

CODEWITH salary_tab AS
(SELECT effective_date, salary,
WHEN (LAG (salary) OVER (PARTITION BY emp_id ORDER BY effective_date ASC) =


For emp_id 1, if we ran this query for 10/31/2011, then it has to compare the 10/31 salary with 10/29 and do the same until the salary mismatches. In this case, salary salary mismatch occurs on 10/20, so the stale salary period is from 10/31 to 10/21 which is 7 days.Below query will give that result:

WITH salary_tab AS
(SELECT effective_date, salary,
WHEN (LAG (salary) OVER (PARTITION BY emp_id ORDER BY effective_date ASC) =


View 1 Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved