I want to convert a number to this format using below query but i'm getting error. how to correct the below query.
SELECT TO_CHAR(12345678, '99G9999D99') FROM dual;
error:-###########
i am using one stored procedure where in one variable which is declare as date value is coming like that '10-OCT-12 11.30.54 AM' and i am inserting this value in one table which has one column vdate with date datatype but it is not inserting there.
View 16 Replies View RelatedI have year/quarter number field (200903 3-rd quarters of 2009) and I need to convert to data format.
View 5 Replies View RelatedAs we know that date datatype can store both date part and time part. If I specify the Date format for my database as 'DD-MM-YYYY HH@$:MI:SS' can i ensure i anyways for a particular columns in the database containing date values the format is 'DD-MM-YYYY' i.e without the time part.Can we specify seperate date formats for specific columsn in database during table creation?
View 1 Replies View Relatedi'm creating a website where i can search between 2 specific dates which user will enter in the format of DD-MM-YYYY. the 2 dates will be inserted into 2 fields which is $input1 and $input2.
extract($_POST);
//to set the date format
//***************************
$input1 = date('DD-MM-YYYY h:i:s');
$input2 = date('DD-MM-YYYY h:i:s');
[code]...
but i get the error message saying...Warning: odbc_exec() [function.odbc-exec]: SQL error: [Oracle][ODBC]Option value changed., SQL state 01S02 in SQLExecDirect in C:phpwwwaaas_bbb.php on line 86...Error in SQL statement
I am using the below query to get time stamp along with time zone format.
>>SELECT CURRENT_TIMESTAMP FROM DUAL
Now the requirement i got is i need to get convert the format in DD-MON-YY, HH:MM:SS, Timezone (GMT or PST)
how to convert this format. Or is there any alternative time stamp option to do conversion.
I want to convert the date in to a particular format,
1)
2011-03-04T01:45:12
I want to convert the above date value into the below format.
04-MAR-11 01.45.12.000000000 AM
2)
2011-03-04T01:45:12+0006
The above date format into
04-MAR-11 01.45.12.0006 AM
I'd like to know if it is possible to track DML actions issued on a specific table by a specific user, for example , i tried :
AUDIT SELECT on SCOTT.DEPT by HR by ACCESS;
I get an error, where is my syntax error ?
i want to know if it's possible to do it without trigger ?
I have a string in this format '2011/06/01 00:00:00'. How do I convert this to date format.I tried to_date,to_char and they give errors invalid number & literal does not math format string. I don't have much control on the string since that is the way is comes from the application.
View 3 Replies View Relatedis it possible convert data from forms 6i to pdf format?
View 4 Replies View Relatedconvert a HTML to PDF format using plsql using oracle 9i.
View 9 Replies View RelatedI am having one table in which for currentdate column is having below values
10142
10143
10144
10145
10146
10147
10148
These values represents the date.
Ex 10146 = 146th day of 2010
10147= 147th day of 2010
I want to convert values in currentdate column in yyyymmdd format.
This is what I've been trying to do.
I have the following table:
FOOTBALL PLAYERS < ID, NAME, ATTRIBUTE>
100-JIM-TALL
101-BOB-STRONG
102-MARK-SMART
...etc
I want to form a query that regardless of the total returned records, I will be able to specify how many of each "kind" of players I want returned. There are several good reasons that it has to be one query and not many.
I have job, working hours, employee id, employee name in test_emp table. The job name and employees are not fixed in this table and it varies from project to project. We don't know how many employees are there and needs to be fetching on runtime.
I have the data like below
JOB---------WRKHR---EMPID-----EMPNM
ANALYST-----10------5478------RAMESH
MANAGER-----10------4258------SACHIN
LEAD---------10------4789------VIVEK
DEVELOPER---20------5632------ROHIT
ANALYST-----20------5843------VIRAT
MANAGER-----20------4789------VIVEK
PROGRAMMER-30------5479------SURESH
LEAD---------30------4258------SACHIN
DEVELOPER---30------5231------PRAVEEN
I need the output like below format.
JOB---------RAMESH--SACHIN--VIVEK--ROHIT--VIRAT--SURESH--PRAVEEN--TOTAL
MANAGER-----0--------10-------20-----0-------0------0---------0---------30
LEAD---------0--------30-------10-----0-------0------0---------0---------40
ANALYST-----10-------0---------0-----0------20------0---------0---------30
DEVELOPER---0--------0---------0-----20------0------0---------30--------50
PROGRAMMER-0--------0---------0------0------0------30--------0---------30
--TOTAL-----10-------40--------30-----20-----20-----30--------30--------180
the below request.
My company has many products, whenever customer purchase any product purchase timestamp should be inserted into product_details table .
Note: timestamp of UK, Philippines, Singapore, India time should be inserted automatically as my company is world based company .
I have tried to convert the date format to other(uk,singpore,manalia) timings but i couldn't get proper reply.
SELECT to_char(sysdate,'DD-MON-YYYY HH:MI:SS AM') INDIA,
to_char(new_time(sysdate, 'EST', 'GMT'),'DD-MON-YYYY HH:MI:SS AM') D2 ,
to_char(new_time(sysdate, 'PST', 'GMT'),'DD-MON-YYYY HH:MI:SS AM') D3 ,
to_char(new_time(sysdate, 'EST', 'PST'),'DD-MON-YYYY HH:MI:SS AM') D4
FROM dual
I got the below output.
INDIA D2 D3 D4
----------------------- ----------------------- ----------------------- -----------------------
31-OCT-2011 06:06:16 PM 31-OCT-2011 11:06:16 PM 01-NOV-2011 02:06:16 AM 31-OCT-2011 03:06:16 PM
i have create one standard Calender from that i pickup month date and year separately like 2/6/1987 now i want to convert it into standard date format how to convert it and pass to another block....
View 3 Replies View RelatedWe are interested to find the number of connections from specific client. Is tracing on sqlnet.ora in the client machine the answer? If yes, which trace has the information?
View 1 Replies View RelatedCREATE TABLE EMP(NAME VARCHAR2(10 BYTE))
INSERT INTO EMP VALUES ('C');
INSERT INTO EMP VALUES ('A');
INSERT INTO EMP VALUES ('T');
SELECT xmlelement("NAME",NAME) FROM EMP;
I am trying to convert column data to xml format, but I get this error message:
Quote:The query fails because all columns types are currently not supported. I am using:
Quote:Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
SQLTools 1.5.0 Beta build 9 as EDITOR
I am using oracle database 9iR2 (9.2.0.8) on windows 2003 server.
Is there any method to convert following output in to ASCII format ??
select ename from scott.emp;
ENAME
--------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
i m using oracle 9i (9.2.0.8.0) & developer 2000..I am getting below mentioned error when i convert Oracle Report to excel format.
"Report Builder
REP-1401:'afterreport':Fatal PL/SQL
error occurred ORA-43356 : Message 43356
not found; product = RDBMS ; facility = ORA "
when we ran SELECT statement against CUBE, we got below wait event: resmgr: cpu quantum.Further, we checked below 2 parameters :
NAME VALUE
------------------------- --------------------------------------------------
resource_limit TRUE
resource_manager_plan SCHEDULER[0x12B943C]:DEFAULT_MAINTENANCE_PLAN
[code]....
It has been found that these sessions did not get enough CPU to process the request. how to find out how many CPU has been allocated to this database ?
> uname -a
Linux dukedmts03db02.corp.cox.com 2.6.18-128.1.16.0.1.el5 #1 SMP Tue Jun 30 16:48:30 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux
how to analyze how much % of CPU being utilized for a specific session ?
how can I convert incorrectly imported data into it's proper unicode format.
example:
FULL_NAME
GöRAN JOHANSSON
GÖRAN JOHANSSON
The first record is incorrectly imported and the second is how it should be looking like, if it has been properly imported.
NLS parameters are:
NLS_CHARACTERSET: WE8MSWIN1252
NLS_NCHAR_CHARACTERSET: AL16UTF16
In the example above, full_name is of a NVARCHAR2(100) type but the same problem applies to columns with VARCHAR2 type.
Is there a function or a peace of code I could use to convert value of the first record to be look alike of the second record?
I want to reset my date to this format: 12/31/2012 11:59:59 PM - see code below:
DECLARE
v_latest_close DATE;
BEGIN
v_latest_close := TO_DATE ('12/31/2012 23:59:59 ','MM/DD/YYYY HH24:MI:SS');
DBMS_OUTPUT.PUT_LINE('The new date format is : '|| v_latest_close);
END;
the code above displays only : 12/31/2012 instead of 12/31/2012 11:59:59 PM
can we convert RAW to number in sql.because i have to bitand of one raw and number variable.
View 3 Replies View Relatedwould need to convert a number in this way:10.1 ---> 10101.90 ----> 190 How can i?
View 1 Replies View Relatedquery only specific date only. example: '06-MAY-2010'.
1.from the statement below, it will display out more than 06-mAY-2010.
2.if i want more the date from 03-MAY-2010, 04-MAY-2010 and 05-MAY-2010.
select * from PNG_ORA_SERVER_PERF where SERVER_NAME = 'MLYDESPINTF1' and DATE_TIME >= TO_DATE('06-MAY-2010','DD-MM-YYYY');
I've got a table with varchar records and I need to separate numerical value and convert into the number datatype.
create table tmp_mape
(remark varchar2(100) )
insert into tmp_mape values ('Dobitie zdarma 3,32EUR 0910105067 02/02')
insert into tmp_mape values ( 'Dobitie pravidelné 9,00EUR' )
I just need to get values 3.32 and 9 from that example into the new column.
The select below returns ORA-01722: invalid number
how to solve it?
select remark,
to_number( to_number(REGEXP_SUBSTR (remark, '[[:digit:]]+', 1) ) ||','|| to_number(substr(REGEXP_SUBSTR (remark, ',[^EUR]+', 1) ,2) ) )
from tmp_mape
How to convert varchar2 to number data?
View 8 Replies View Relatedhow can convert this number 00001021992 to this format
1-02-1992
i used thie query but no result
select substr(to_date('00001021992','dd-mm-yyyy'),(6,6)) from dual;
the basic salary has data type character.when i write
select basic_salary from table_name
it shows output.but when i need annul for that basic salary(basic_salary*12) it shows error. invalid number.