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;
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 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').
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 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;
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
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'
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]....
#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.
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
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;
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 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
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.
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
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].......
