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.
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;
SYSDATE CURRENT_DATE CURRENT_TIMESTAMP LOCALTIMESTAMP
----------- ------------ ------------------------------------------------- -------------------------------------------------
5/30/2012 8 5/30/2012 8: 30-MAY-12 08.27.22.037703 AM -04:00 30-MAY-12 08.27.22.037703 AM
I want to change the current visual attribute color of my form at the application level, reason being i don't want to generate all the forms again from scratch.I know that we can change this using visual attributes but is there a way to change this through some form setting?(Oracle 10g)
View 3 Replies View RelatedI 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 RelatedI have been tasked to validate the date format of some attributes in a table. So, to verify the data we will be getting the filed and the format to check against.
View 4 Replies View RelatedWrite 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".
What are the differences of;
1. ALTER SYSTEM SWITCH LOGFILE;
2. ALTER SYSTEM ARCHIVE LOG ALL;
3. ALTER SYSTEM ARCHIVE LOG CURRENT;
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?
What is the difference between these return statuses for Report_object_status:
Canceled, Invalid_job, terminated_with_error
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 Relatedselect 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 ?
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.
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.
I have two tables
table_1
--Emp_id--|--Sup_id--|Sup_name|--Date--|
-------------------------------------------------
--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_2
--Emp_id--|Emp_name|
--------------------------------
--00001--|--QWER--|
--00002--|--ASDF--|
--00003--|--ZXCV--|
--00004--|--POIU--|
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
table_3
Emp_id|Emp_name|Sup_id|Sup_name|Date|
------------------------------------------------
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?
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
This query returns 2 rows and the output is displayed as well. how I can return just the first row where the max end_date is 4/30/2011?
select
pt.customer_number,
pt.customer_name,
lease.lease_number,
lease.lease_name,
lease.property_name_disp,
lease.location_code_disp,
MAX(pt.end_date) end_date,
pt.attribute1 Disabled
[code]...
I have a Employee data as below with EID and DOB
EID 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.
I need to get the past 12 months from the current month
for example
input:
march2010
output:
apr2009
may2009
june2009
july2009
augest2009
september2009
october2009
november2009
december2009
january2010
february2010
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
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?
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;
How can we insert current date which is present in xml node into a column having date datatype.
View 6 Replies View RelatedI'm trying to return a field in a date format, but my minutes keep returning as 03. See example below:
TO_CHAR (i.editdate, 'mm/dd/yyyy HH24:MM:SS') AS "Date",
Actual data field in the table contains:
10/27/2010 1:07:42 PM
Data returned is:
10/27/2010 13:03:42
Why are the minutes incorrect?
i have written an sql to look as sales orders but i want it to only return the last 12 months worth of sales for the date its run, the date file [sdtrdj] is julian date so i used to_char to conver it to dd-mm-yyyy how can i get it to only return the last 12 months from the date its run
select
sdshan As "Location", sddcto As "Order Type", sddoco As "Order Number",sdlitm As "Product Code", sddsc1 As "Product Description",
to_char(to_date(sdtrdj + 1900000, 'yyyyddd'),'DD-MM-YY')As "Order Date", sduorg As "Order Qty", sdaexp As "Extended Amount", sdsoqs As "Quantity Shipped",
ibsrp4 As "Srp4", ibsrp6 As "Srp6", ibsrp7 As "srp7", ibsrp9 As "Srp9", ibsrp0 As "srp10", sdsrp2 As "Franchise"
From proddta.f4211, proddta.f4102
Where sdmcu = ' UKC001'
and sddcto = 'KO'
and sdlitm = iblitm and sdmcu = ibmcu
and sdsrp2 In ('504','973','322','236','232','856','233','566','590','470','343','266','279')
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
I have a table with the following columns
EMPLOYEEIDNUMBER(12,0)
PUNCHDTM DATE
TIMEZONEIDNUMBER(12,0)
I want to return any results where any employee id that has 2 different timezoneid's on the same date. I would actually like, if its possible, to select these entries to display on one row per employee per day. So for example
EMPLOYEEID - PUNCHDTM - TIMEZONEID - PUNCHDTM - TIMEZONEID
12345 - 6/20/2012 5:00 am - 123 - 6/20/2012 10:00am - 456
To me who is newer with SQL this sounds like i would be 'joining' the table to itself so i've searched for that but not found what i need.
I have a sub query (already dervived from several other tables) that has a list of children in with the date they started their course (tableofchildren). Child IDs may be duplicated in this query but each record will have unique start dates per child ID.
I have a second sub query which lists workers involved with the children in the first query (tableofworkers). A worker may be responsible for more than one child but has the unique child in the record to identify involvement with the child.
I need to join these queries together to return the child's record with the WorkerName and the AllocatedStartDate of the worker who was most recently involved with the child prior to the date (EnteredCourseDate) the child started their course (OutputWanted) - the worker associated with the child when they started their course.
A couple of points - I need to deploy this into another reporting application that doesn't support cursors etc or the 'With' operator. Also, I tend to join tables/queries in the Where clause so if it's possible that way that would be great.
OC.
create table tableofchildren
(ChildID varchar(20),
ChildName varchar (50),
[Code].....
How one can get date difference in days between two dates in Oracle reports ?
View 2 Replies View RelatedIn my application I show the Date in Hijri using the NLS setting. but the issue that "i face a deference between the real hijri date with oracle hijri date"
Assume that the real hijri date on 18-SEP-2012 was 02/11/1433 in hijri but in oracle was 03/11/1433
when I execute the following query it shows me "1433/11/03", but the actual Hijri date is "1433/11/02"
select to_char(to_date('20120918','YYYYMMDD'),'YYYY/MM/DD', 'NLS_calendar='''||'ARABIC hijrah') FROM DUAL;
I'm having trouble comparing and subtract two date type fields, can not get a significant result.
e.g.
select id, date.started, date.closed, datediff (day, date.started, date.closed) differences diference from Table1 order by desc;
Tables
--------
ROWID START_DATEEND_DATE
101/26/2012 00:00:0001/31/2012 00:00:00
201/26/2012 00:00:0002/02/2012 00:00:00
302/03/2012 00:00:0003/31/2012 00:00:00
How to find out the days difference in sql query, start date of next records and end date of previous records as highlighted on blue color
query out put should give as below
start_date + next record ,end_date - Previous records should give the difference of
6
1