SQL & PL/SQL :: How To Get Max (value) With Its Corresponding Datetime

Sep 22, 2013

This is my table

ID timestamp value
1 2013-09-09 01:09:00.000 1234
2 2013-09-09 02:00:00.000 123
1 2013-09-09 03:09:00.000 1233
2 2013-09-09 21:09:00.000 125

I need to find max(value) with its corresponding time stamp .. this table has approximately 500000 records with 180 distinct IDs. Need to find max(value) group by IDs.

Expected result:

ID timestamp value
1 2013-09-09 01:09:00.000 1234
2 2013-09-09 21:09:00.000 125

We have a query but its returns 00 in hh:mm:ss instead of exact timestamp.

View 15 Replies


ADVERTISEMENT

SQL & PL/SQL :: How To Get The Time From Datetime

Oct 12, 2012

i am using one stored procedure there in one variable value is coming like this: Jan 1 1900 6:00AM

from this i want time how to get the time from that.

View 3 Replies View Related

SQL & PL/SQL :: Set Datetime Globally

Jun 23, 2011

I am facing problem to set date & time globally in oracle. I set date-time many time but it is on session base.

View 28 Replies View Related

SQL & PL/SQL :: Convert Datetime To Date?

Nov 8, 2012

I know this question has been asked several times. but i am starting out and i am struggling to get my head aroung it.I would like to convert datetime column of Oracle source(RPT.SHIPMENT_VW) to date while loading to sql Here is my

SELECT SHIPMENT_NBR,RECEIVED_DATE_TIME, RCVD_AT_DATE_GMT_FK
FROM RPT.SHIPMENT_VW
where WHERE RECEIVED_DATE_TIME = TO_DATE(TO_CHAR(:fromdate, 'DD/MM/YYYY'))
here RECEIVED_DATE_TIME is in 'DD/MM/YY hh:mi:si' fromat which, i want to convert to 'DD/MM/YY'
the above code throwing an error ORA:01843 not a valid month

if I use where clause like:WHERE (RECEIVED_DATE_TIME = TO_DATE(TO_CHAR(:fromdate, 'DD/MM/YYYY'), 'DD/MM/YY'))then its not retrieving any data

View 6 Replies View Related

Forms :: Datetime Format

Mar 31, 2011

I have written the following assignment statement. :dshift_hdr.startdate := to_date(sysdate,'DD-MM-RR HH12:MI:SS');

the startdate is a display item. Its data type property has been set to datetime and the formatmask is DD-MM-RRRR HH12:MI:SS but the result of above assignment statement is 31-03-2011 12:00:00 i.e. it does not take the actual current time.

i have promised to install the application to the client tommorrow.

View 3 Replies View Related

SQL & PL/SQL :: DateTime Between Dates - How IDate Is Used

Apr 25, 2010

How 'idate' is used. For example in the following statement:

DELETE FROM SCHEMA.TABLE WHERE DATETIME BETWEEN TO_DATE(IDATE,'DD-MM-YY') AND TO_DATE(IDATE,'DD-MM-YY')+1-1/1440;

View 10 Replies View Related

Oracle Dataset - How To Split Datetime

Jun 2, 2008

my problem is:

i running a sql-query in visual studio 2005 with the oracle dataset. currently my datetime is in format mm/dd/yyyy hh:mm:ss. I wish to split the datetime in dd/mm/yyyy only(without the hh:mm:ss)

EXEC ('
SELECT
XNP_TIMER_VIOLATION.VIOLATION_TIME,..
FROM XNP_TIMER_VIOLATION,..
) AT npcrpt ;

View 4 Replies View Related

Convert DateTime Field To Date

Jun 11, 2010

I want to force a datetime field to display as date only. How can I do this? This is so when prompting for a value for this field a user doesn't have to also enter the time. At the moment the prompt returns nothing when entering only a date as it does not match any value as they all have times also.

View 12 Replies View Related

Application Express :: DateTime Calculations On A Form

Jul 19, 2012

Version of the database you are using: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
Version of Application Express: 4.1.1.00.23

I have a requirement that is driving me stark raving bonkers.

I have a form that a user fills out, it sets the Status to 'Open' and I capture the Date Created On in a hidden field. When someone goes back in to the Form and sets the Status to 'Closed' I capture the Date Closed On in a hidden field. I need to calculate how many hours that it took to close the issue and display it on the form.

I found some javascript that does this

<Code>
<script type="text/javascript">
var msecPerMinute = 1000 * 60;
document.write("msecPerMinute -" + msecPerMinute + "<br />")
var msecPerHour = msecPerMinute * 60;
[code].......

While I can get it to work from dreamweaver I cannot get it to work from APEX. Both of my date fields in my database are defined as:

Type: TIMESTAMP
Fractional Precision: 6
Time Zone: LOCAL TIME ZONE

View 17 Replies View Related

Client Tools :: Handling DATETIME Format Using SQLPLUS COPY Command

Jul 29, 2012

I am using the SQL*PLUS COPY command to move the data from my database to another remote database. The data in my database also contains DATETIME format. But since COPY command cannot handle DATETIME format, I am wondering is there any workaround for this.

Note: Due to some limitations, I cannot use other methods like DATABASE LINK or EXPDP/IMPDP commands.

View 12 Replies View Related







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