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


ADVERTISEMENT

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 :: 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 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

PL/SQL :: Validating Date Without Using Inbuilt Oracle Functions

Jan 2, 2013

writing a manual date validation function without using inbuilt oracle functions.I am planning to write this function in PL/SQL.

View 17 Replies View Related

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 View Related

SQL & PL/SQL :: Date Functions?

Dec 3, 2012

Trying to understand how Oracle DATE works and how it differs to TO_DATE in the below manner...

SELECT DATE '2012-10-25' FROM DUAL;
SELECT TO_DATE('2012-10-25','YYYY-MM-DD') FROM DUAL;

I've had a look through the Oracle Docs but can't find much on this. Basically I want to know how safe the first option is and if it does the same as the second. Also, where does DATE get the Format String from (NLS setting?). Can it be employed to compare a literal with a date that has a TIME portion set to something other than 12:00:00?

View 2 Replies View Related

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 :: Query Using Analytical Functions-date Range

Nov 30, 2010

I have a table which has the attached data.

Sample data is here

LOGON_DATE NUMBER_OF_LOGINS
11/28/2010 02:00:001
11/28/2010 03:00:001
11/28/2010 04:00:002
11/28/2010 06:00:004
11/28/2010 07:00:002
11/28/2010 08:00:003
11/28/2010 09:00:006
[Code] ........

I am trying to do a report like this.

Date PeakUsersBetween6AMand6PM AVGUsersBetween6AMand6PM PeakUsersBetween6PMand6AM AVGUsersBetween6PMand6AM

Output should be
11/28/2010 25 11 49 27
11/29/2010...

I am using analytical function to do this, It throws an error range cannot be used for dates.

View 5 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 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

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

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

XML DB :: Xpath Functions In Oracle

Aug 24, 2012

Is there any function like fn:local-name(.) in oracle XPATH functions. I am new to Oracle & migrating procedure from SQL server to oracle where procedure taken XML script as input.

I need to convert below SQL server select statement to Oracle.

SELECT     converseUID,
'Distributed',
cast(ServiceCalls.query('fn:local-name(.)') as varchar(100)),
1,
WIP.ServiceCalls.value('(TotalTime)[1]','int'),
WIP.ServiceCalls.value('(Status)[1]','varchar(100)'),
[code].......                   

View 5 Replies View Related

SQL & PL/SQL :: Oracle Database To Mysql Conversion

Dec 21, 2012

Is it possible to convert oracle data base to MySql?

View 1 Replies View Related

JDeveloper, Java & XML :: Oracle PL/SQL Conversion

Jun 24, 2011

How to revert the following so that the output should be '<testPrice>1000</testPrice>'.

select htf.escape_sc('<testPrice>1000</testPrice>') from dual;
<testPrice>1000</testPrice>

View 4 Replies View Related

SQL & PL/SQL :: Migrate Sybase To Oracle Conversion

Apr 23, 2010

i want to migrate sybase stored procedure to oracle stored procedure.

sybase stored procedure:

create proc checkcontract @titleid tid
as
delete from titile where titile_id=@titleid
return 0

we need to migrate this procedure to oracle stored procedure. If we are using return 0 in SP in oracle means, it returns the end of the procedure statements(according to oracle statements). but sybase indicates that return 0 does not return 0 value during the run time. The return 0 implies that SP completed successfully. In oracle, i have converted stored procedure into SP and also stored functions.

Oracle Scripts:
Stored Procedure:

create or replace procedure checkcontract(v_titile varchar2)
as
begin
delete from titile where titile_id=v_titleid;
end;
/

Stored Functions:

create or replace functions checkcontract(v_titile varchar2)
as
return number
begin
delete from titile where titile_id=v_titleid;
return 0
end;

Give the comments which one is correct or accurate.

Oracle SQL developer also recommeds STORED FUNCTION. But in my application code, don't assign any output variable to this procedure. If i am using Stored Functions, we need to assign value and assign the output of the SF. we don't want to change the application code.

View 4 Replies View Related

SQL & PL/SQL :: Overriding Pre-defined Functions In Oracle

Apr 11, 2012

Can we override the pre defined functions in oracle. I am able to do this in my schema. But if DB server contains more than one schema do I need to write the same procedure in all schemes.

we can call the same procedure with schema name from different schemes also . But is there any way to over ride the actual function provided by oracle (just like over riding the equals method of object class in java).

create or replace function to_number(var varchar2) return number
is
no number := var;
begin
return 99999;
end;

SQL> select to_number(' 234 ') oracle_function , dbo.to_number(234) user_function from dual;

ORACLE_FUNCTION USER_FUNCTION
--------------- -------------
234 99999

View 4 Replies View Related

PL/SQL :: Pass Statements Using Oracle Semantic Functions In PHP?

Apr 26, 2013

I am developing android application connected to Oracle database using PHP, and I use PL/SQL statements with semantic technologies provided in Oracle(specifically, SEM_MATCH, SEM_MODELS, SEM_RULEBASES or something like this).

However, it doesn't work properly...the connection between android and Oracle works well, though. Also, the query statement is implemented well in the sql developer.The whole php code is shown below :

*<?php*

function db_connect()
*{*
*$db = "(DESCRIPTION=(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 203.249.105.20)(PORT = 1521)))(CONNECT_DATA=(SID=orcl)))";*

[code]...

View 6 Replies View Related

Assign Different Profile For Oracle Database Users According To Their Job Functions

Apr 10, 2011

it is a good practise to assign different profile for oracle database users according to their job functions.what could be the resons for that?

View 3 Replies View Related

PL/SQL :: Writing User Defined Function The Same Way As The Oracle Functions

Sep 26, 2012

In one of the interviews , I have attended the guy asked me to write a user defined function which will take the column name and list all the values .

For example

Table Name:Employees
Column Name: Employee_Name

Employee_name
Scott
Ivgun
Jack
Shane

The query should be in this fromat

SELECT col_agg(Employee_name) emp_name from Employees;

The output should be

Emp_names
scott,ivgun,jack,shane

View 6 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







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