One table ACCOUNT_T.CREATED_T is number (38). It is mapped to /ACCOUNT's PIN_FLD_CREATED_T, which is timestamp. ACCOUNT_T table has one record, CREATED_T = 1362063600. This field is displayed at the GUI (Customer Center) as Feb 28, 2013 So my question, how to convert the number value (1362063600) to a date value (Feb 28, 2013)?
View 4 Replieshow 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;
I created table Contains then following columns
cheq varchar2(50)
date_due varchar2(50)
and data entry in this columns
cheq 500,1500,5000 all values numbers in this columns
date_due 1-1-2012 , 15-9-2010 all values in this columns date
i want sum the column "cheq"
when used this code but it's not working
select sum(to_number(cheq))
from table_name but the code not working
second column "date_due"
i want search between to date i used this code but also not working
select cloumn1,cloum2
from table_name
where to_char(date_due,'dd-mm-yyyy')
between to_char(date_due,'dd-mm-yyyy') and
(date_due,'dd-mm-yyyy')
but not work
I have year/quarter number field (200903 3-rd quarters of 2009) and I need to convert to data format.
View 5 Replies View RelatedThe HTML code for the bracket character '[' is & # 91; (without spaces). In my SQL, I would like to convert the word [you into & # 91;you. Is there a way to do that?
I know that replace() will work, but with that you have to supply a list of chars to replace; I
I Want to make a query to select finished goods product in sales having product code greater than 280 but i have face a problem that order by is not working because products column have character code as well as number. how to sort that column.
View 2 Replies View RelatedI need to convert the type of GMT to Local time, during data entry. I have a source table and a target table.
To make the insert get the most value, I have no problems:
insert into destination (SELECT * FROM WHERE source pointnumber = (SELECT MAX (pointnumber) FROM source));
But since times are different between the tables, I want to convert the data obtained to GMT -4:30 Time (Caracas - Venezuela), before inserting it.
I can use a function?
i am working on the oracle 10g.currently i want to build one procedure,where i get a variable which contain character as well as number.my aim in the procedure is to replace all the character like ( a,b..z) in this variable with the number field.
example
suppose i have one variable say
var := 'MALT011000012345MTLCAST001SMT84'
now i want to replace each character say
A with value 23
B with value 56
L with value 99
9 i will get these value form another table).
i will have handle thousands of variable and they can start with any character and they contain character anywhere.
I am attempting to insert date data into a column using sqlldr...Here's the current format:
2010-03-01 00:20:19.277
So far, I haven't gotten anything to work. I would like to trim the .277 from the existing date. Here's my latest attempt:
birthdate DATE "to_date(substr(birthdate,1,19),'YYYY-MM-DD HH24:MI:SS')"
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 RelatedI'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 Relatedthe 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.
I want to convert below MS-SQL query in oracle 10g.
for eg:
Select numasdate,
Cast(numasdate / 60 as Varchar) + ' hours ' +
Cast(numasdate % 60 as Varchar) + ' minutes'
as [TotalHoursAndMinutes]
From
#SampleTable
Output:
9436 157 hours 16 minutes
537 8 hours 57 minutes
9323 155 hours 23 minutes
12525 208 hours 45 minutes
I need a query that convert number into time.
View 5 Replies View RelatedI have a value that I need in the number format, but without decimals. Is this possible?
TO_NUMBER(SY_KEY1,'9999999') AS SY_KEY1The result I get back is:
1114225.0So, I need a result of 1114225 and in the number format.
I have a date saved in varchar2 colomn.
how can I convert the vaules into date, so I can use the date to compare data.
Ex: I have the value '20-03-2007 05:31:29', but this value is saved as varchar2.
how can I take from this value the date '20-03-2007' as date format?
I currently have a table with a VARCHAR column which is used to store notes. Currently the notes read something like 'Verified 01/01/2012'. I am trying to convert it to a date column so I can run reports using the date (select between dates etc).
I have tried with the substr function but since the records are all different doesn't really work. (plus that doesn't make it a date so not sure it would work for searching).
how to proceed?
I know this question has been asked several times. but i am starting out and i am struggling to get my head aroung it.I would like to convert datetime column of Oracle source(RPT.SHIPMENT_VW) to date while loading to sql Here is my
SELECT SHIPMENT_NBR,RECEIVED_DATE_TIME, RCVD_AT_DATE_GMT_FK
FROM RPT.SHIPMENT_VW
where WHERE RECEIVED_DATE_TIME = TO_DATE(TO_CHAR(:fromdate, 'DD/MM/YYYY'))
here RECEIVED_DATE_TIME is in 'DD/MM/YY hh:mi:si' fromat which, i want to convert to 'DD/MM/YY'
the above code throwing an error ORA:01843 not a valid month
if I use where clause like:WHERE (RECEIVED_DATE_TIME = TO_DATE(TO_CHAR(:fromdate, 'DD/MM/YYYY'), 'DD/MM/YY'))then its not retrieving any data
I currently have a field which stores a date (date employer started with the company). I want to create a view which stores this as number of days employed (e.g. if star date 1st October view will say 8). How to convert a date to a number?
View 14 Replies View RelatedI have an requirement as follows.
I have an column QTR_DATE which contains data like below
20123
20111
Which means YYYYQ ( Year with Quarter) and i need to get date of first SUNDAY of the quarter. Example
20123 has to be converted to 20120701 which is 2012-July-01.
query to getting it without using any functions/proc?
How to convert/trunc date
Example
source
column name CONFIRMED_DTE
16-APR-09 12.00.00.000000000 AM
target
column name CONFIRMED_DTE
16-APR-09
I want to date like 16-APR-09 from 16-APR-09 12.00.00.000000000 AM. In a columns have null values and 16-APR-09 in source and target
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 want to convert decimal number 44 to character(" ' " i.e single quotation);
I have already tried to_char function but it's not working.
I would like to convert a number to years & months.
e.g. 119 = 9 years and 11 months
This would be displayed as 9.11
Is there an oracle function or sql that can calculate this value,
I need to convert below functionality in Oracle. At present it is working with SQL server
current_end_time TIMESTAMP,
current_start_time TIMESTAMP
current_duration int
current_end_time := DATEADD(second, current_duration/10, current_start_time)
I want to force a datetime field to display as date only. How can I do this? This is so when prompting for a value for this field a user doesn't have to also enter the time. At the moment the prompt returns nothing when entering only a date as it does not match any value as they all have times also.
View 12 Replies View Relatedhow to convert date into UNIX time in oracle ?
View 1 Replies View Relatedi 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 Related