SQL & PL/SQL :: Date Conversion

Jan 12, 2012

I have an issue with date conversion. In my table dates are in this formate:

04-DEC-90 10:46:46

I need to convert it in To_date.

SQL> select to_date('04-DEC-90','dd-mon-yy') from dual;

TO_DATE('04-DEC-90','DD-MON-YY
------------------------------
04/12/2090

SQL>

But the year shoul be 1990 not 2090.

View 3 Replies


ADVERTISEMENT

Epoch Date Conversion

May 31, 2011

My company uses Remedy which stores date/time as an integer. I use the following formula to convert it to a readable date:

TO_CHAR(TO_DATE('01/01/1970', 'MM/DD/YYYY HH:MI:SS AM')+ ((createdate - 4*3600)/(60*60*24)),'MM/DD/YYYY HH:MI:SS AM') as Create_Date

I have to use the "4*3600" in order to get the date to show up correctly, but even then the date sometimes comes up wrong. If the date occurs in the morning, then the date shows up as the previous day. I am sure this is probably due to the offset I am adding in the above formula. If I don't add the 4 hour offset, then the date shows up 4 hours off.

View 3 Replies View Related

SQL & PL/SQL :: Char To Date Conversion?

Feb 7, 2013

converting a string to date, my string looks like '01022013' '04022013', I tried to_date('01022013', 'dd-mm-yyyy') but couldn't work.

View 6 Replies View Related

SQL & PL/SQL :: Date To Number Conversion

May 6, 2010

I want to change a table datatype from date to number where already existing data should get convert.any possibility of doing where i tried like this but no get changing. Even as Julian format is working a bit i want the data to come as GMT format

Scenarios where i tried are like this.

ALTER TABLE Contacts ADD ALERT_DATE1 NUMBER(20,0)
/
UPDATE Contacts SET ALERT_DATE1 = TO_NUMBER(ALERT_DATE)
/
ALTER TABLE Contacts DROP COLUMN ALERT_DATE
/
ALTER TABLE Contacts RENAME COLUMN ALERT_DATE1 TO ALERT_DATE
/

but second statement failing.

Julian fomat like

SELECT sysdate,
TO_CHAR(sysdate, 'J'),
TO_DATE(TO_CHAR(sysdate, 'J'),'J')
FROM dual;

View 10 Replies View Related

SQL & PL/SQL :: Date / Time Conversion

Apr 4, 2010

I am converting data from an old paradox table to a new oracle table, one of the problems im having is incompatibility with date and time formats:

some columns contain times in the format : "00:00:00" eg..... "15:00:00"
some columns have date in the format: "dd/mm/yyyy" eg....... "21/08/2000"
some columns have time and date eg.. "05/09/2000 15:49:39"

Currently I have the data held in tables within an access database, and in CSV format.

eg, I have dates like 03/04/2010 which i need to be 03-APR-10....

how I can get the following into Oracle date formats? there is over 1000 records so manual conversion is out of the question

View 21 Replies View Related

PL/SQL :: String To Date Conversion

Jun 25, 2012

I recently became involved with databases, and i've came across with a little obstacle. I have strings that represent a date, they are very oddly formatted and need to store them as dates. the string format looks like this: 'Monday, May the 13th of 2001'

How can i effectively convert them to DATE type?

View 7 Replies View Related

SQL & PL/SQL :: Conversion Of Julian Date Into Dd / Mm / Yyyy

Mar 8, 2010

I have been given some data in excel sheet to be uploaded in an Oracle Table. The dates are in Julian. The date in Julian in excel sheet is as :-'110048'.

In the excel file, I found that the cell was formatted as General and when I changed the formatting to Date I got the result as '19/04/2211'.

tell me a way to convert this Julian to mm/dd/yyyy format to be inserted into a table in Oracle.

Tried this :-

SQL> SELECT to_char(to_date(to_char(110048), 'J'),'DD/MM/YYYY') FROM dual;

TO_CHAR(TO
----------
18/04/4411

Not sure how to go about it.

View 5 Replies View Related

SQL & PL/SQL :: Oracle Date Conversion Errors

Mar 14, 2011

I am having a strange issue with date conversion. The errors encountered are :

PLS-00382: expression is of wrong type
PLS-00306: wrong number or types of arguments in call to '>'

The situation or test case is as follows:

create table sand_programs(prog_code varchar2(20), prog_end_dt date);

create table sand_program1(prog_code varchar2(20), filing_date number(15));
Result : Both the tables are created.

Now I create a procedure below as mentioned, to check if the filing_date is greater than the prog_end_dt or not.If the filing_date is greater than prog_ end_dt, then it should go to the 1st "dbms_output.put_line" message else it should go to the 2nd "dbms_output.put_line" message.

Here's my procedure:

create or replace procedure test_sand(p_program_cd in number) is
v_prog_end_dt date;
v_filing_date number;
begin
[code]....

View 6 Replies View Related

SQL & PL/SQL :: Date Conversion From Unix To Oracle

Jun 14, 2010

I have a UNIX shell script as following,

#runs the selected process
${ORACLE_HOME}/bin/sqlplus -s ${USER_PASS} >${TMP_FILE} <<EOF
set pause off
set verify off
set pagesize 0
set linesize 2000
set timing off
[code].........

I am passing a Date to the Oracle Procedure in `date +'%b_%d-%H:%M:%S'` format.

rmsqa813.2 /home/ashlaksh> echo `date +'%b_%d-%H:%M:%S'`
Jun_14-02:26:37

The oracle Procedure's SQL is as following,

SELECT ffdh.fiscal_doc_id INVOICE_ID,
ffdh.location_id LOCATION_ID
FROM fm_fiscal_doc_header ffdh,
fm_schedule fs
[code]........

The above SQL is not getting the data as the Date format "AND ffdh.last_update_datetime <=i_last_update_datetime " is not matching..

Do I need to convert the Date ? ( But i_last_update_datetime is DATE)

View 1 Replies View Related

SQL & PL/SQL :: Oracle Functions Date Conversion

Jan 20, 2011

the data in the column is in below format
---------------------
2/10/2009 9:28:41 PM
mm/dd/yyyy

my requirement is
--------------------
20090210
YYYYMMDD

View 5 Replies View Related

SQL & PL/SQL :: Oracle Date / Time Zone Conversion

Aug 13, 2012

I would like to know how to convert the following:

13-AUG-12 03.30.06.146 PM

to the format:

"MM/DD/YYYY HH:MI:SS AM"...

I believe there is a time zone issue here as well, since the should actually be around 11:30am. I am not sure what time zone is being used.

View 2 Replies View Related

Date To Timestamp Conversion Of Column With 150 Million Rows?

Oct 5, 2010

Due to some business requirements a table field needs to change from date to timestamp in order to handle the millisecs.

1>When i alter the row , for a table with 150 million recs will there be a conversion. Is there a recommended way to convert the field. Mind you this field is used as a part of composite PK.

2> There is a interfacing application which connects and copies the data to its system and is using the date type, will that application be able to continue to work without any changes, if it does not care about the millisecs.

3> Will there be performance impact on an existing application that uses the date field to sort

4> Will DB need more space due to the change

View 3 Replies View Related

Conversion Hours Incorrect When Revert To Actual Date

May 18, 2011

I can't seem to understand why the hour is incorrect. Below query "dte_computation_on_data" is the old function they use to convert date and insert it to the table. Problem is when I revert it to the actual date the hour
is incorrect.

CODE
SELECT -- THIS HERE IS MY TEST TO REVERT TIME AND DATE ON THE FORMULA OF WITH RESPECT TO THEIR FUNCTION
to_char(TO_DATE('19700101', 'YYYYMMDD')+(tb1.dte_computation_on_data/86400),'MM/DD/YYYY') || ' ' ||
to_char(to_date(mod  (tb1.dte_computation_on_data,86400) ,'sssss'),'hh24:mi:ss ') revert_test,
systimestamp,tb1.dte_computation_on_data
from
( SELECT -- THIS IS THE FORMULA OF THE OLD FUNCTION THEY USE TO CONVERT DATE TO NUMBER AND INSERTED ON THE ROW
    floor((CAST(SYS_EXTRACT_UTC(systimestamp) AS DATE) - TO_DATE('19700101', 'YYYYMMDD')) * 86400) dte_computation_on_data
  FROM dual)tb1;

results
---------------------------------------------------------------------------------------
REVERT_TEST             SYSTIMESTAMP                            DTE_COMPUTATION_ON_DATA
05/19/2011 03:46:18     5/19/2011 11:46:18.005171 AM +08:00     1305776778

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

SQL & PL/SQL :: Value Conversion?

Apr 23, 2012

Some app (wich I can't touch) stores a value that I need to modify with a trigger or even with a view.

Original value: 7B751BF0 (2071272432 in decimal)
Stored value: 4028331387 (F01B757B in hexa)

To make things short, I'm getting 4028331387 and I need to convert it to 2071272432.

View 6 Replies View Related

LMT To DMT Conversion?

Mar 12, 2013

I need to move one of my LMT tablespace to DMT, Can I do it , I know that the DMT's are depreciated since Oracle 9i but still need to know this.I am trying the below mentioned method to achieve the same.

SQL> exec DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL('USERS');But I am facing the below mentioned error:
BEGIN DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL('USERS'); END;

*
ERROR at line 1:
ORA-10616: Operation not allowed on this tablespace
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 216
ORA-06512: at line 1

View 10 Replies View Related

SQL & PL/SQL :: Number Conversion

Jan 4, 2013

I have a table datatype number (12,10) that I am reading out of. I am taking the value from this source table and inserting it into a destination table of datatype number (12,15).

I do not have the ability to alter the tables. How can i convert this number so i can insert. I am currently getting the error "ORA-01438: value larger than specified precision allowed for this column"

I am trying to use the to_number, but it not working. How can i format this number field so i can read it from source where i have number (12,10) and insert it successfully in a higher precision table of number(12,15)

View 1 Replies View Related

Example For Conversion Functions

Mar 27, 2012

Again i getting confused with conversion function especially Explicit data type conversions. some cases oracle server automatically converts the data to the required type. This is called IMPLICIT CONVERSION. Explicit conversions are done by using the conversion functions.

Oracle Explicit Data Type Conversions are

1 TO_CHAR
2 TO_DATE
3 TO_NUMBER

View 1 Replies View Related

SQL & PL/SQL :: Row To Column Conversion 2

Jan 13, 2011

SELECT EMPNO ,ESAL,DEPTNO FROM EMP;

O/P

EMPNO ESAL DEPTNO
101 1000 10

I WANT O/P LIKE THIS

REQUIRED O/P

101
1000
10

View 5 Replies View Related

PL/SQL :: Column To ROW Conversion?

Jan 21, 2013

My requirement is to converts row to Column information and sum of column in Weight kg in below manner Data in columns are not of same nature they can keep on changing

Item Supplier Consumer Package DWP_NO DWP_ED Quality_code Pakaging Material WEIght kg
80026020 13984
90225217 21385 1 1 3 Not Applicable 22-paper .001
90218863 16578 1 1 1 Not Applicable 04-low density polyethylene (0.002+.002+.002)=.008
90218349 14507 1 1 1 Not Applicable 05-polypropylene,22-paper (.02+.05) =.07
40221108 21519 3,2,1 1 2 IKEA-CB-60 22-paper (.32+0.3+0.45)=1.07

find table structure

CREATE TABLE TEST_ROW_TO_COL
(
ITEM_CODE VARCHAR2(10 CHAR),
SUPPLIER_CODE VARCHAR2(10 CHAR),
"Consumer Package" NUMBER(2),

[code]...

ans insert statement of sample data

--SQL Statement which produced this data:
-- select * from Test_row_to_col where
item_code in ('80026020','90225217','90218863','90218349','40221108')

[code]...

I am using Oracle 11g

View 1 Replies View Related

SQL Server To Oracle Conversion

Aug 29, 2013

I have one query in SQL server. I want to convert that query to Oracle. Well i am not that good with writing queries in Oracle. Following is the query in SQL server.

DECLARE @StartYear AS INT = 2010;
DECLARE @EndYear AS INT = 2014;

WITH
years
AS (SELECT YYYY = @StartYear
UNION ALL
SELECT yyyy + 1
FROM years
WHERE yyyy < @EndYear)
[code].......

Which Gives output in following format.

id_datedateYearMonthNumberMonthDayOfMonthDayOfWeekNumberDayOfWeekWorkingDay
201001011/1/201020101January16Friday1
201001021/2/201020101January27Saturday0
201001031/3/201020101January31Sunday0
201001041/4/201020101January42Monday1
201001051/5/201020101January53Tuesday1

View 3 Replies View Related

Number To Float Conversion

Aug 11, 2010

I have coluum value like 00000000399. i need to convert the float like i mentioned

00000000399 should be loaded as 3.99

00000001197 should be loaded as 11.97

00000000003 should be loaded as 0.03

wat option i try to change the value

View 1 Replies View Related

Conversion From SQL Server To Oracle

Nov 6, 2011

is there any licensed version tool available to convert objects from sql server to oracle especially procedures? I have tried with open source tools but that tool didn't convert properly.

View 7 Replies View Related

Ora-01460 With No Conversion Attempts?

Feb 1, 2007

This is my query:

DELETE FROM GR_GUEST WHERE GUEST_NAME='Ambassador Jack Binns bring Patrick Clawson' AND RESERVATION_ID_FK=21635

It gives an ORA-01460 which is "unimplemented or unreasonable conversion requested"Removing the " AND RESERVATION..." gives the same error.

The problem is that GUEST_NAME is a VARCHAR (150) and RESERVATION_ID_FK is an INT so I don't see where any conversion is coming in.I had changed the name a little bit as it used to a (, ) and a : in the GUEST_NAME. I thought that might be causing the issue so I removed them with a REPLACE through an UPDATE query. However, the record still needs to be deleted.

It should be a simple query as it happens on this table all the time, so I don't know why this one is different.

View 3 Replies View Related

Time Conversion To Yyyy - Dd - Mm

Mar 5, 2008

1075329297.572

This is unix time where 1075329297 is seconds 572 is milliseconds.

first il store this time in oracle database.when i am retrieving it i want this date into yyyy-dd-mm format. Is it possible to do it in oracle.(using convert function) or is there some other way?

View 8 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 :: Blob To Long Raw Conversion

Dec 19, 2010

I have a problem i need to convert a blob column contains pic file to long row

i had many tries but no one succeeded

-----------------------------------
Source table | destination table
id number | id number
img blob | img long raw
------------------------------------

1 - INSERT INTO destination table SELECT id , img FROM Source table WHERE ROWNUM < 2

i have this error ORA-22835 Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 146092, maximum: 2000)

2 - INSERT INTO destination table SELECT id , dbms_lob.SUBSTR(img,0,2000) FROM Source table WHERE ROWNUM < 2

now errors but the lengh is 0 (i have no file )

What can i Do

View 7 Replies View Related

SQL & PL/SQL :: Conversion Of CLOB To NCOLB

Sep 19, 2011

I want to convert Clob column datatype to NClob datatype. I tried to modify the column by using below method but got the mentioned error:

SQL> alter tab1 TABLE_NAME modify COLUMN_NAME nclob;
*
ERROR at line 1:

ORA-22859: invalid modification of columns

One of the other method with which I am familiar with is :

Quote:

First to Create a new column of the desired type and copy the current column data to the new type using the appropriate type constructor.

how to change the clob to Nclob datatype without using the temp column usage?

View 1 Replies View Related

SQL & PL/SQL :: Sybase To Oracle Conversion

May 6, 2010

I am converting sybase to oracle database. during the migration, i have small doubt in triggers.

sybase source

create trigger testss_dt
on test
for delete
as
if @@rowcount = 0
return
insert into test
select id from deleted
return

I have converted oracle code here

create or replace trigger testss_dt
before delete on test
for each row
begin
insert into test
values(:old.test);
end;

But i have ignores @@rowcount concepts in oracle. what @@rowcount indicates in trigger?

View 10 Replies View Related







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