Adding Up Time Values In Varchar2?

Mar 6, 2009

column name: extra_hour

datatype: varchar2(5)

extra_hour
----------
01:30
01:30
00:00
02:45
and so on...

Problem: I want to add up all the given value and display it in a select statment, that is the output should be:

Output:

XXXXXX
----------
05:45

View 4 Replies


ADVERTISEMENT

SQL & PL/SQL :: Adding Time Zone Onto A Query

Nov 17, 2011

I was given this query: SELECT TZ_OFFSET('EST'),dbTIMEZONE,SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL;

My coworker also said this:

Using this statement as a starting point:

SELECT TZ_OFFSET('EST'),dbTIMEZONE,SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL;

but I need

for

select -5 from dual;

to return

America/NewYork or EST

View 6 Replies View Related

SQL & PL/SQL :: Adding High And Low To Zip Plus Four Values?

Jan 19, 2011

I have a table with zip codes and their plus four values. For ex: zip code of 10000, which has corresponding plus four values of 001, 002, 003, and 008, 009, 010. The issue is just that--a zip code can have sequential plus four values, and then it will skip several potential plus four values, and then start again. I would like to assign a low plus 4 value and high plus four value to a zip code, keeping in mind that the plus four values are not always sequential. So, it would be similar to this:

zip plus4 low plus4 high
10000 001 003
10000 008 010

View 2 Replies View Related

Forms :: Adding Hours And Minutes To Time

Feb 11, 2011

I currently have a problem where I have two date fields with time stamps. The only bit i am currently interested in in these fields is the time factor. When i display them in their field they have a format of HH24:MI .

I have a start time and end time as well as a duration and duration type. What I am trying to do is the following: when the user inputs the start time, along with the duration say 1 for example and the duration type of say HRS for example I would like to have the end_datetime default to 1 HR from the current start time. This is the code I use on a when validate item trigger to acheive this:

case :blk.duration_type
when 'HRS' then
:blk.end_datetime := :blk.start_datetime + ((1/24)* :blk.duration);
when 'MINS' then
:blk.end_datetime := :blk.start_datetime + ((1/24/60)* :blk.duration);

However, every time it triggers the value put into end_datetime is 0:00 is it something to do with the datatypes im using .

View 1 Replies View Related

SQL & PL/SQL :: Adding TimeStamp Values In Table

Apr 4, 2011

I want to have sum of Time Stamp Values in a Table. i.e. Group by Query

example : Select Name, Sum(Timestamp column) from Emp group by name;

OR

I have Time values in character column of a table. i.e. Table TT column t1 char(5) values '03:02'

Above mentioned values in more than one line, i want to get the sum of all the values and it should be a time value.

View 7 Replies View Related

Summing Column - Adding Values Of Attribute

Apr 25, 2013

i have 2 data block (maintenance & maintenanceparts) in one form and i will like to add the values of the attribute "LineCost" in the maintenanceparts datablock and put the sum into 'Totalcost' attribute in the maintenance datablock.

I tried use the code

"begin
select sum(maintenanceparts.LineCost)into :maintenance.totalcost
from MaintenanceParts;
end;"

But it ended up adding all the records that was save in that attribute and putting it in totalcost. I am new to oracle

View 1 Replies View Related

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

VARCHAR2 (100) Changes Column To VARCHAR2 (100 Byte)

Oct 24, 2007

i am fairly new in the oracle arena, but what would cause a statement such as

ALTER TABLE TEST_TABLE MODIFY text_field1 varchar2(100) DEFAULT 'testval' NULL

to change a column's type from VARCHAR2(100) to VARCHAR2(100 byte)? i found a few mentions of the 100 byte concept online but nothing that jumped out at me.

View 2 Replies View Related

Compare Two Time Values In Sql?

Dec 4, 2009

is it possible to compare two time values in oracle sql ie there is a column say 'tot' with values 8:29,11:35 etc(8hrs29 mins etc) can i compare this column with 03:00hrs i tried select case tot>=3 then 1 as days end from tablename;

View 3 Replies View Related

Windows :: Set Values For Best Performances In Response Time?

Jun 12, 2013

I need to create a DB on a pc with 4GB (RAM). I can use the assistant but where I need to set correctly a value related to the memory? Is it necessary to set values for best performances in response time?

View 2 Replies View Related

SQL & PL/SQL :: Check Listener Time Zone Settings / Values In Oracle 10g Database?

Nov 28, 2011

How to check the listener time zone settings/values in Oracle 10g database?

View 5 Replies View Related

Reports & Discoverer :: Lexical Parameters Are Used To Substitute Multiple Values At Run Time

Jan 15, 2012

I studied a document about lexical parameter in that it says "Lexical parameters are used to substitute multiple values at run time and are identified by a preceding '&'. Lexical s can consist of as little a one line where clause to an entire select statement"

Select * from emp, deptno &where.

and i know about substitution variables using & is this are same (lexical and substitution) or different.

View 14 Replies View Related

SQL & PL/SQL :: Use Of Order By In Varchar2

Feb 21, 2011

I have a column named "col1" with datatype "varchar2(10)" and row wise entries like "1,1A, 2,3...,10,2A,..." like. I want to order it like "1, 1A ,2,2A, 2B,3... 10...".I tried it with to_number() but it gives me

1,10,11,2,....like that.

View 14 Replies View Related

SQL & PL/SQL :: Substitute For VARCHAR2

Jun 13, 2012

What is the best alternative to using VARCHAR2 if the output gets truncated after 4000 bytes in a PL/SQL function?

View 10 Replies View Related

SQL & PL/SQL :: Using Clob Instead Of Varchar2

Mar 23, 2010

in our application we are using clob column instead of varchar2 because varchar2 does not allow more that 4000 chars, so Using clob allows to put data of any length, will it cause performance issues ? we have this column in almost in all tables .

View 2 Replies View Related

SQL & PL/SQL :: Order By For VARCHAR2

Sep 28, 2010

create table members(
memberID varchar2(7),
locationID number(5),
roomID varchar2(5)
)
/

[Code]....

MEMBERI LOCATIONID ROOMI
------- ---------- -----
2007209 1 1
2006253 1 1-A
2006253 1 1-A
2006265 1 1-B
2006240 1 1-C
2005064 1 1-D
2007836 1 100
2007211 1 101
2007075 1 102
2007110 1 105

[Code]...

Function created.

SQL> select * from members
2 order by safe_to_number(roomID);

MEMBERI LOCATIONID ROOMI
------- ---------- -----
2007209 1 1
2007185 1 2
2007089 1 3
2007023 1 4

[Code]....

How can I sort the output to get the result like below

2007209 1 1
2006253 1 1-A
2006253 1 1-A

[Code]...

and so on...

View 13 Replies View Related

10g - Some VARCHAR2 Fields Empty?

Jul 21, 2011

Once a year in the application we have a specific query that gets used a lot. It's an UPDATE that updates a single record in a single table with a few different datatypes, but the issue is happening with one of the VARCHAR2 fields. It updates one VARCHAR2(2000) and three VARCHAR2(4000) fields at the same time.

This year, 9 of the 95 times it was used resulted in one of the VARCHAR2(4000) fields as null in the database. The users would not want this field to be null and 5 of the 9 have told us they entered something (the form they're filling out is a research proposal and leaving this field empty would be pointless because it's part of the funding request, so they're not doing it). The application isn't doing it because it's not consistent. I've checked the application and these fields can't be nulled any other way.

We just found the issue so I looked back over the past years back to 2005. Last year it didn't happen at all. In 2010 it happened a handful of times. Some years there were even more times. It's not always the same field but it's always a VARCHAR2 of at least 2000 characters.

I have a lot more information but it's all just details (let me know if you need to know more). I'm wondering if there is a bug in 10g with these types of fields. I don't believe it's malicious behavior on an individual's part but I suppose that's always possible.

how to research something like this. I tried to get access to Oracle Support and the Knowledge Base I heard they have but it doesn't look like I can do that

View 2 Replies View Related

SQL & PL/SQL :: Conversion Varchar2 To Number

May 14, 2011

I have 2 tables.The column in table A is number and Column in table B is a varchar2 datatype.I have to use the Column of table B as a filter to column of Table A.Below is the example.

create table A(Col1 number);
Inert into A values(1);
Inert into A values(2);
Inert into A values(3);
Inert into A values(4);

Create table B(Col1 Varchar2(100));
Insert into b value ('1,2,3');

Select * from A where col1 in (select col1 from b)
Error: Invalid Number

Is there a way to convert the varchar to number.The varchar field have multiple characters (numbers) seperated by commas.

View 7 Replies View Related

SQL & PL/SQL :: How To Convert Varchar2 To CLOB

Mar 28, 2008

How to convert a varchar2 column to CLOB when there is a thousands of records in it.

View 15 Replies View Related

SQL & PL/SQL :: How To Convert Varchar2 To Number

Jan 15, 2013

How to convert varchar2 to number data?

View 8 Replies View Related

SQL & PL/SQL :: Difference Between Varchar And Varchar2?

Aug 31, 2012

What is the exact difference between varchar and varchar2?

As i know only the length is the one difference.Apart from this length,what are all the differences?

View 3 Replies View Related

SQL & PL/SQL :: Can Insert CLOB In Varchar2

May 12, 2010

I'm trying to insert CLOB to varchar2 from one DB to another using database link but failed.

INSERT INTO tmp_std_master@aapsbhx.iss.soton.ac.uk
(student_id, title, first_name, middle_name, surname, hus_id,
date_of_birth, email_address, prog_desc --- varchar2 (100)
)
SELECT DISTINCT c.ref1 student_id, c.title, c.firstname firstname,

[code]....

View 16 Replies View Related

PL/SQL :: Invalid Number With VARCHAR2

Apr 3, 2013

I keep getting this error when I run my update statement. Here is what the coding looks like. I'm running Oracle 11g.

CREATE TABLE A (
SUPP_CD_EIM VARCHAR2 (20),
SUPP_CD VARCHAR2(20),
DSN_FAC_CD VARCHAR2(5));
[code]......

I want the output to look like

A1_1234
A2_2345
A3_3456

View 2 Replies View Related

SQL & PL/SQL :: Sorting VARCHAR2 Field

Mar 23, 2010

sort data set A,B,1,2,A1,A2,B1,B2,2B,1000 as A,B,1,2,1000,A1,A2,B1,B2,2B.

I tried with LPAD and EXPREG_REPLACE funtion. But it did not work.

View 13 Replies View Related

Conversion Number(19) To Varchar2(19)

Sep 18, 2009

When I try to convert numeric values � number(19) p.s 111111111111111111, the to_char function returns �1111111111111110000� because the to_char functions doesn�t support precision bigger than 15.

Is there any way to solve it?

View 3 Replies View Related

How To Order Date Which Has Its Type As Varchar2

Jan 26, 2011

I have table called INFO and the column called CREATED_DATE . Now the datatype of CREATED_DATE is of varchar2 . Now If I need to query the table through select statement where I need to order the result based on CREATED_DATE , how can i achieve this ?

View 1 Replies View Related

SQL & PL/SQL :: Inserting Data From Clob To Varchar2?

Aug 21, 2006

I have to Insert data from one table to another table. First table contains some clob datatype columns and second table have carchar2 datatype.

Below are the structures of both tables;

SQL> DESC TBL_MAINSQL> DESC TBL_MAIN2
Name Type Name Type
----------------------------- ----------------------------------------- --------------
TXT_FEIN_NUMBER VARCHAR2(9) TXT_FEIN_NUMBER VARCHAR2(9)
TXT_QUOTE_NUMBER VARCHAR2(12)TXT_QUOTE_NUMBER VARCHAR2(12)
TXT_POLICY_NUMBER VARCHAR2(12)TXT_POLICY_NUMBER VARCHAR2(12)
TXT_AGENT_CODE VARCHAR2(10)TXT_AGENT_CODE VARCHAR2(10)

[code]....

The Table contains 5000 records. how to convert CLOB to VARCHAR2.

I used DBMS_LOB.SUBSTR but I received BUFFER TO SMALL ERROR.

View 10 Replies View Related

SQL & PL/SQL :: Passing IN Parameter Of Datatype Varchar2

Feb 16, 2010

I have a stored proc which takes IN parameter of datatype varchar2.When I am trying to run the proc it is throwing error that "input buffer too small".The datatype assigned to IN parameter is of varchar2(200) but actually the length of the parameter passed is around 500 characters.the way to increase the length of Input parameter to 500 characters??

View 16 Replies View Related

PL/SQL :: Remove Spaces Between Two Number From A Varchar2?

Oct 18, 2012

I have a question respect to remove spaces from a varchar2.

The varchar2 is '7987451 1234567' and i need that string like '7987451 1234567', because the field has a length of 15.

i try with this, but does nothing

Select TRIM('7987451 1234567') from dual

Other possibilities are find several spaces and try to replace with only one, but would a heavy work (may be with sentences Loop).

This problem is now in our loading processes. Exists some function to replace spaces between numbers of varchar2?

View 2 Replies View Related

SQL & PL/SQL :: Better Option To Use When The Quote Needs To Be Concatenated To A Varchar2 Value

Apr 13, 2006

which is the better option to use when the quote needs to be concatenated to a varchar2 value i.e.

In order to insert 'test' into a column exactly as yet see I used:
chr(39)||'test'||chr(39)

However, the DBA (OCP) says that is not a good way to do it and should be changed to:
''''||'test'||''''

View 24 Replies View Related







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