SQL & PL/SQL :: How To Return Just First Row Which Has Max Date

Sep 1, 2010

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]...

View 19 Replies


ADVERTISEMENT

SQL & PL/SQL :: Return A Field In Date Format

Mar 23, 2011

I'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?

View 10 Replies View Related

PL/SQL :: Making Sql To Only Return Last 12 Months From Date?

Apr 21, 2013

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')

View 3 Replies View Related

Return Results Where Employee ID That Has 2 Different Timezone IDs On Same Date

Jun 21, 2012

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.

View 3 Replies View Related

SQL & PL/SQL :: Return Most Recent Record Previous To Start Date?

Nov 18, 2011

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].....

View 13 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

Application Express :: 4.2 Item - Validation Return Boolean And Return Error Text Are Switched

Oct 17, 2012

In Apex 4.2, the item validation of "Function Returning Boolean" and "Function Returning Error Text"; They seam to be backwards.

Is there a simple statement that can be used to fix this in the apex dictionary?

View 1 Replies View Related

SQL & PL/SQL :: Display Date Ranges In One Column As Separate Date Periods (start And End Date) In Two?

Jun 1, 2010

I'm trying to work out how to take a table like this:

IDDate
12502-Feb-07
12516-Mar-07
12523-May-07
12524-May-07
12525-May-07
33302-Jan-09
33303-Jan-09
33304-Jan-09
33317-Mar-09

And display the data like this:

IDPeriodPeriod StartPeriod End
125102-Feb-0702-Feb-07
125216-Mar-0716-Mar-07
125323-May-0725-May-07
333102-Jan-0904-Jan-09
333217-Mar-0917-Mar-09

As you can see, it's split the entries into date ranges. If there is a 'lone' date, the 'period start' and the 'period end' are the same date.

View 13 Replies View Related

Forms :: Expiry Date To Automatically Show A Date 15 Years After Initial Date

Apr 12, 2010

I have a two date fields in my form; valid from date and expiry date.

Currently my valid from date has an inital value property of $$date$$ which automaitcally brings up todays date.

I need my expiry date to automatically show a date 15 years after this date?

View 8 Replies View Related

Application Express :: Unable To Set Default Date Value For Tabular Form DB Date Field?

Dec 3, 2012

Version : 4.1.1, I have a tabular form on a DB table. One of the columns is a date field. When the user hits the "add Row" button on the tabular form, I want the Date field to be defaulted to sysdate. Here is what I have tried so far,

1. Created a "hidden" item P1_SYSDATE and populated the default value with sysdate. After this, under the DB tabular report date field, I used default type - Item/application on this page and entered P1_sYSDATE

2. Instead of populating the default value of the P1_SYSDATE hidden item, I created a before regions process and added

:P1_SYSDATE := sysdate

and added P1_SYSDATE to default type of the tabular date field with default type as "ITem/application on this page.

I get the error

ORA-01790: expression must have same datatype as corresponding expression

I tried to_Char(sysdate,'dd-mon-yyyy') and then converting it back to to_date. still no luck.

View 1 Replies View Related

Visual Studio :: Set Query Window Default Date Format To Full Date?

Oct 24, 2013

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 Related

Date Arithmetic - Formula To Subtract Years And Give Date

Sep 27, 2007

I am a novice in oracle

I have 2 columns in my table

->Col1 with experience in years entered as an integer
->Col2 with current date

I need to add another column as a date value adn for that i need to subtract Currentdate-Col1 when i tried currentdate-Col1 it just subtracted the days i need the formula to subtract years and give a date

I have worked in DB2 and all u need to do there was add the keyword years at the end but in oracle the same does not work

View 8 Replies View Related

Server Utilities :: How To Get Date And Time In Date Column While Sqlldr

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

PL/SQL :: Retrieve Date Range From 2 Search Input Date Fields

Aug 16, 2012

I would like to inquire how do I code my select statement if my user enter a search date range(search startDt: 01/08/2012 and search endDt :30/09/2012) and I will like to retrieve only the 7records out from my table as shown below ?

STARTDT_     ENDDT_
01/08/2012 01/08/2012
01/06/2012 31/12/2012
01/08/2012 01/08/2012
01/09/2012 01/09/2012
01/09/2012 31/12/2012
01/07/2012 01/07/2012->not retrieved out
01/08/2012 01/08/2012
01/01/2012 31/12/2012

View 6 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

Can Partition A Table Based On Date If It Does Not Have A Date Column

Jun 21, 2012

How can we partition a table based on date if it does not have a date column.

Actually I have to compare two tables on daily basis and fetch few rows from those two tables and enter it to a third table.But both these tables does not have a date column.

I am confused if i need to alter those tables and add date column or if there is some way in which i can compare the data from the two tables for that particular day only and not the whole table data.

View 1 Replies View Related

Converting Date Field To Datestamp (Epoch Date)

Apr 11, 2013

I have a small prolem thats best described like this....

a table called TONY with a field named VISITED (date as YYYYMMDD).

We want to populate the field TIMESTAMP (Last visited timestamp, 18 digits) using midnight or 00:00:00 on VISITED value.

Something like:

UPDATE TONY SET TIMESTAMP = �(whatever the formula is involving VISITED).

but i cannot figure out the best way to derive the TIMESTAMP value...

it's a date to epoch conversion, and i can find many examples of Epoch to date, but thats the wrong way around for me i'm afraid!

Oracle 11gR2 by the way...

View 4 Replies View Related

SQL & PL/SQL :: Converting Char Into Date With Column Having Non-date Formats

Aug 16, 2010

i have a table with the following description

create table gl_periods(period_name varchar2(10),transactions number (2) );

with the data as :

period_name transactions
------------ --------------
JAN-10 12
FEB-10 12
MAR-10 8
APR-10 23
ADJ_TOM-10 25
MAY-10 37
JUN-10 41
JUL-10 10
PHY_JAY-10 6
AUG-10 14
SEP-10 22

My requirment is to find out the period names and transactions which are in valid date formats and are less than sysdate and the non date formats are adjustments made by different users for their transactions

View 8 Replies View Related

Server Utilities :: Loading Date Value Into DATE Column?

Oct 18, 2012

I want to load data from a file using sqlldr.I have a table commissions
(
technician_id char(5)
, tech_name char(30)
, Comm_rcd_date DATE
, Comm_Paid_date DATE
, comm_amt number(10,2)
)

my file is
00001,TIMOTHY TROENDLY,2011-03-04T01:45:12+0006,2011-03-04T01:45:12+0007,123.56
00002,KENNETH KLEMENZ,2011-03-04T01:45:12+0006,2011-03-04T01:45:12+0009,123.56
00003,SHUNDAR ARDERY,2011-03-04T01:45:12+0006,2011-03-04T01:45:12+0005,123.56
write a ctl file to load this data.

View 6 Replies View Related

SQL & PL/SQL :: Required Data For Month To Date And Year To Date?

Apr 30, 2012

I want to get data for month to date. For example, If I pass today or any day date as parameter then i should get data for that month(month of passing date) up to passing(parameter) date. As well as i have to get year to date.For example, If I pass today or any day date as parameter then i should get data for that financial year(year of passing date) up to passing(parameter) date. how to get month to date and year to date data.

View 3 Replies View Related

Forms :: Date Field Gets Disabled When Date Format Is Given?

Oct 3, 2012

A field named xxx_date is a text item which we have to enter manually so as to update a record in that particular date. This is a mandatory field without which we cannot continue the data entry..

I am getting this error while trying to update the record

FRM-40509 :Oracle error :unable to update record

I have kept the enabled = yes
required=no
data type=Date.. in the property pallet

View 2 Replies View Related

PL/SQL :: Reset Date In Specific Format (Date / Time)

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

PL/SQL :: Date Difference - Include Both Start And End Date In Range

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

How To Convert GMT Date To Local Time Date

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

SQL & PL/SQL :: Getting Date Gaps From Data Of Date Field

Sep 24, 2011

I have a date field that should be filled everyday with today's date and I need to get the days that were not entered.

i.e. :

CREATE TABLE TRY_F (DAT DATE);

INSERT ALL
INTO TRY_F VALUES (to_date('01/01/2011','DD/MM/YYYY'))
INTO TRY_F VALUES (to_date('02/01/2011','DD/MM/YYYY'))
INTO TRY_F VALUES (to_date('04/01/2011','DD/MM/YYYY'))
INTO TRY_F VALUES (to_date('05/01/2011','DD/MM/YYYY'))
INTO TRY_F VALUES (to_date('06/01/2011','DD/MM/YYYY'))
INTO TRY_F VALUES (to_date('08/01/2011','DD/MM/YYYY'))
INTO TRY_F VALUES (to_date('10/01/2011','DD/MM/YYYY'))
INTO TRY_F VALUES (to_date('14/01/2011','DD/MM/YYYY'))
SELECT * FROM DUAL;

I need a smart way of getting the dates that were missed in DAT.

View 4 Replies View Related

SQL & PL/SQL :: Date Overlap And Merge Date Query

May 11, 2011

Lets 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.

View 14 Replies View Related

PL/SQL :: Entire Date / Time Value In A Date Field

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

SQL & PL/SQL :: How To Return In One Row Only

Aug 21, 2012

i need to have an SQL statement that will return only in one row.

in this SELECT statement,

select NVL(a.jj_crdr,'CR'),
nvl(a.CR_cnt,0) CR_cnt,
NVL(a.CR_amt,0) CR_amt,
NVL(b.jj_crdr,'DR'),
NVL(b.DR_cnt,0) DR_cnt,
NVL(b.DR_amt,0) DR_amt

[Code]...

it returned

Tot_cr tot_cr_amt tot_dr tot_dr_amt
1 100 0 0

because there is record for CR and there is no DR.

but when there's record in DR and no in CR, there's no record returned.

because the outer join is in the 2nd Select. i need to change the condition to: 'where a.jj_creator(+) = b.jj_creator' in order to return a record.

what i need is to be flexible so that it will return records even if one of the source doesn't have record.

View 10 Replies View Related

SQL & PL/SQL :: XML - Return New Row For Each Variable?

Oct 31, 2013

I have some XML being returned from a web service, and it returns almost 900 variables. Whilst I am familiar with how to return these in a single row, do I can return a row for each variable? My DBA is very uncomfortable with creating a table with almost 900 columns, for obvious reasons. However, we already have plenty of tables with tens of millions of rows, so he's fine with that. I'll try and expand on the requirement. Below is some XML from the data returned to us:

<APPLICANT app_no="1">
<APPLSUMMARY>
<MAIN W="ZZ" X="{ND}"/>
<COUNTS Z="3" AB="0" BB="3" CB="0" DB="3" EB="3" FB="3" GB="0"/>
</APPLSUMMARY>
</APPLICANT>

I would like to be able to return a new row for each variable, For example:

VARIABLE | VALUE
----------------
W | ZZ
X | {ND}
Z | 3
And so on.

View 2 Replies View Related

SQL & PL/SQL :: Min / Max With Multiple Row Return

Apr 22, 2010

Client managerCont. Start DateCont. End Date
abcman11-Jan-0830-Jun-08
abcman21-Jul-0831-Dec-08
abcman11-Jan-0930-Jun-09
abcman11-Jul-0931-Dec-09
abcman21-Jan-1031-Mar-10
abcman21-Apr-1031-Aug-10

I need to code a SQL statement (Not PL/SQL) to display following records:

Client managerCont. Start DateCont. End Date
abcman11-Jan-0830-Jun-08
abcman21-Jul-0831-Dec-08
abcman11-Jan-0931-Dec-09
abcman21-Jan-1031-Aug-10

View 4 Replies View Related







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