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?
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.
How can I change the date format from 'YYYY-MM-DD' to 'DD-MM-YYYY'? to_char / to_date r not working...
View 22 Replies View RelatedI 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
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.
why this query is not working
SQL> select xid,start_time from v$transaction;
XID START_TIME
---------------- --------------------
070006009A010000 08/16/10 13:24:43
SQL> select xid,start_time from v$transaction where start_time>to_timestamp('08/16/2010 12:15:00','mm/dd/yyyy hh24:mi:ss');
select xid,start_time from v$transaction where start_time>to_timestamp('08/16/2010 12:15:00','mm/dd/yyyy hh24:mi:ss')
*
ERROR at line 1: ORA-01843: not a valid month
I am able to see 1Hr difference in my date fields of SQL output because in UI (User Interface) date field was stored in BST format but DB time zone is in GMT format so how to find a solution for 1 hr difference, here i don't have Privileges to alter DB time zone and i couldn't use function as i have so many SQL's and can't apply that function manually. SO is there any other option to change the DB time zone with out alter it and with out using function.
View 1 Replies View Relatedi see my dates in Oracle 11g like this: 2012-December-30 (in all my tables)
how to configure Oracle that i can see it like this: 30/12/2012
I got a table with a Date in it. Lets called it Testdate and an item called start month and end month.
The Item (varchar2) looks like
MM.YYYY f.e. 03.2012 and is a
I want to see all data between start and endmonth.
First I tried to convert the date from my orignial table:
SELECT distinct TO_CHAR(TESTDATE, 'MM'||'.'||'YYYY')
FROM mv_auswertung;This works:
TO_CHAR(TESTDATE,'MM'||'.'||'YYYY')
02.2012
12.2012
01.2012
But now I am trying to get all data between start and enddate:
Select *
from mv_auswertung
where
TO_CHAR(TESTDATE, 'MM'||'.'||'YYYY') between
to_CHAR(:P7_startmonth) and to_char(:P7_endmonth)
DB : Oracle 9i
Query : select to_date(sysdate,'dd-mm-yyyy') from dual
Result : 26-09-0005
Doubt : Why yyyy is represented as 0005 instead of 2005 and what should I do to make it 2005?
I have a column of type date in my DB, I want to display it in this format: DD / MM / YYYY
this my
execute_query;
I have a date column , and i want to show the date in 'DD/MM/YYYY' format for eg days with 2 digit or comming properly but when it comes to single digit the 0 is not comming for example 4/11/2012 should come as 04/11/2012 like 16/11/2012 , i even did the conversion like to_char(rh_dt,'DD/MM/RRRR').
View 4 Replies View Related 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.
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
When I try to extract the date tag value from XML data, the time stored in 20120602153021 format i.e., YYYYMMDD24HHMISS format. The following statement extracts only date as 02-JUN-12 however do not extract the time part.
If I try the same in SQLplus with to_date it works however fails in PL/SQL.
XML data:
<?xml version="1.0"?>
<RECORD>
<REGTIMESTAMP>20120601130010</REGTIMESTAMP>
</RECORD>
PL/SQL Extract:
CURSOR c_xml_record
IS
SELECT extract(value(d), '//ACTIVATIONTS/text()').getStringVal() AS REGTIMESTAMP,
FROM t_xml_data x,
[code].......
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.
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)
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
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
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
how to set interval time every 4hrs in dbms_jobs but starting time 3.00am.
i am set trunc(sysdate)+4/24. but it will take starting at 12.00,4.00,.....in this way..
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
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.
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
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 RelatedThis 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.
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 RelatedI 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.
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
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?
