Time Format - Way To Change My Select To Creation

Jul 18, 2012

when I select I have to using AND creation_dt>='17-JUL-12' is there a way to change my select to creation_dt='17-JUL-12' ?
on meantime my local format is 18-JUL-12(18-JUL-12) is thee anything I can do?

View 5 Replies


ADVERTISEMENT

Performance Tuning :: How To Reduce Index Creation Time

Aug 17, 2010

Is there any way to reduce the index creation time. I have one table which has 7700000 records and every day this table get truncate and we create with create table as select statement and then create the 4 indexes and each index took 5 minutes so in totality it took 20 minutes in index creation.

View 2 Replies View Related

How To Change Date Format

Nov 22, 2012

I have a simple question, hope it has a simple answer. I changed the default date format for a SCHEMA using a TRIGGER and AFTER LOGON, so it set the NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'. It does work fine, however, the problem is when I connect using a client with JDBC driver. When using SQL PLUS I get the date in the format specified above YYYY-MM-DD HH24:MI:SS, however, when using a client (Aqua Data 6.5.8 I know it is kinda old) and SQuirrel SQL 3.4.0 I always get the date in the YYYY-MM-DD format. I started to think that the issue is with the JDBC because it works fine when I connect with SQLPLUS.

View 5 Replies View Related

SQL & PL/SQL :: Change Date Format

Nov 9, 2010

I want to change the date format at database level in 10g express edition.

When i try to run the command,

ALTER SYSTEM SET NLS_DATE_FORMAT='DD/MM/YYYY',

it throws error like specified initialisation parametr is not modifiabale with this option.

what are the other ways to chnage the database date format to the desired one.?

View 17 Replies View Related

SQL & PL/SQL :: How To Change Date Format

Feb 18, 2010

I have below data

SQL> desc IMEI
Name Null? Type
----------------------------------------- -------- ----------------------------
MSISDN NOT NULL VARCHAR2(20)
IMEI NOT NULL VARCHAR2(16)
DATE_MOD NUMBER(13)
IMSI VARCHAR2(18)
ICCID VARCHAR2(20)
T_PROF RAW(20)
EXTRA_DATA VARCHAR2(100)

SQL> select DATE_MOD from IMEI;

DATE_MOD
----------
1.2199E+12
1.2348E+12
1.2278E+12
1.2263E+12
1.2278E+12
1.2378E+12
1.2414E+12

How can I change date_mod to normal date format like year/month/day hour:minutes:second ?

View 16 Replies View Related

SQL & PL/SQL :: Change NLS Date Format

Dec 28, 2011

Current date format in database session is as below.

SQL> select sysdate from dual;

SYSDATE
---------
28-DEC-11

I would like to change the date format as below (date and time)

TO_CHAR(SYSDATE,'DD-M
---------------------
28-dec 01:09

I have a question regarding this date format change. Would there be any impact to database if i change the NLS DATE FORMAT as above example (date and time) at database level /system level ?

View 9 Replies View Related

SQL & PL/SQL :: Select Stmt Written For Creation Of Materialized View?

Jun 20, 2011

I have a materialized view (smpl_mview) if i want to see the SELECT statement written for the materialized view, how can i see/retrive select stmt?

View 1 Replies View Related

SQL & PL/SQL :: How To Change Column Data In Row Format

Jun 1, 2012

I want to get the following format of data in row format using PLSQL. I want to do that in using a shell script also.

Suppose I have the data like this

123
45
2
789

how to write it in PLSQL as follows:
1427
25 8
3 9

View 3 Replies View Related

Forms :: How To Change Format Mask Date In Ontime Where

Feb 11, 2010

how to change Format mask date in Ontime Where

same

TO_CHAR(HIRE_DATE,'DD/MM'YYYY')

View 6 Replies View Related

Reports & Discoverer :: Change Format Of Figure Display On Bar Graph?

Aug 18, 2009

how i can change format of figure display on bar graph(data labels) in oracle 10g graphical reports .i want to change format fom 99.99 to 9999 .

View 1 Replies View Related

SQL & PL/SQL :: UTC Date Time Should Be In Format

Mar 1, 2010

iam having a table A where column ABC is a varchar field with varchar2(50).

currently data stored is like this.
02-27-2009 11:01:33
02-27-2009 11:01:46
03-06-2009 09:07:18

now i want UTC Date time should be in format 2010-02-24T17:08:09Z. in the above format.

View 9 Replies View Related

SQL & PL/SQL :: Average Of Time In HH:MM:SS Format

Jun 17, 2010

how to calculate the average of the time in th e HH:MM:SS format stored database table.. column contains hundreds of time values and need to table the avergae of it

my col look like,and column is declared as timestamp(6).

MY COL
------
1:13:00
1:06:00
0:43:00
0:47:00
0:32:00
0:19:00
0:39:00
0:46:00
0:56:00
1:39:00

[Code]...

View 29 Replies View Related

PL/SQL :: Date And Time Format

Dec 6, 2012

converting text values that I have to date and time format (without the millisecs and AM/PM values).

The following is an example:

04-DEC-12 07.47.58.000 AM

or would i need to change this in excel before importing this type of data.

View 6 Replies View Related

SQL & PL/SQL :: Date Time Format

Jan 31, 2013

I am running following statement

select TO_DATE( TO_CHAR(CAST((B.EOAWDTTM_MODIFIED) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),'YYYY-MM-DD') from PS_SR_EX_ER_AW_VW B where rownum < 5

and It gives me the error

ORA-01830: date format picture ends before converting entire input string 01830. 00000 - "date format picture ends before converting entire input string"

View 5 Replies View Related

SQL & PL/SQL :: To Store The Date And Time In GMT Format

May 13, 2010

Our data centers resides in different zones and we would like to store the date and time information in GMT format and it should aware of daylight savings.

Which data type is preferred ? Date or timestamp with timezone or timestamp with local time zone. Do we need to configure any parameter on the database?

View 1 Replies View Related

SQL & PL/SQL :: Date And Time Format Statement

Jan 11, 2012

I would like to do a select to get the current date&time in this format:

2011-10-04T04:20:35.2313103+01:00

do you know how can I do that?

I want to convert this sql statement in this format.

View 12 Replies View Related

Data In Date Format For In Time

Oct 26, 2006

i have data in date format for in time actually it should be 9:30 am and out time at 2:30 pm

SELECT MIN(IN_TIME) AS IN_TIME,MAX(OUT_TIME) AS OUT_TIME FROM EMP_ATND_DETAIL AS ET, EMP_ATND_INFO AS EAI
WHERE ET.EMP_ATND_INFO_ID=EAI.EMP_ATND_INFO_ID AND ATND_DATE = '2006-10-25' AND EMP_ID =24

+---------------------+---------------------+
| IN_TIME | OUT_TIME |
+---------------------+---------------------+
| 2006-10-20 09:30:00 | 2006-10-13 01:30:00 |
| 2006-10-24 02:30:00 | 2006-10-13 06:30:00 |
| 2006-10-24 09:30:00 | 2006-10-13 01:30:00 |
+---------------------+---------------------+

i have should get 9:30 am because that is lessser then 2:30 pm

do i have

SELECT name, emp_id,
min(to_char(IN_TIME, 'DD/MM/YY HH24:MI')) in_time,
max(to_char(OUT_TIME, 'DD/MM/YY HH24:MI')) out_time,
FROM EMP_ATND_DETAIL

View 3 Replies View Related

Forms :: How To Change The Name In Run Time

Jul 20, 2010

I would like the change the name in run time find the attachment.

View 3 Replies View Related

Know User Account Locked Time In HH:MM:SS Format?

May 30, 2011

How we know a user account locked time in HH:MM:SS format ?

View 1 Replies View Related

SQL & PL/SQL :: How To Get Data From A Table In Date And Time Format

Jul 24, 2013

how to get data from a Table in Date and Time format? Curently I'm using To_TIMESTAMP for a date range.

Here is the code Snippet -

Select *
FROM TABLE_Name
WHERE COUNTRY= 'IN'
AND CALC_DATETIME between TO_TIMESTAMP('2013/06/05', 'YYYY/MM/DD HH:MI:SS') AND TO_DATE ('2013/07/08', 'YYYY/MM/DD HH:MI:SS')

View 39 Replies View Related

Format Of Select Statement

Jun 15, 2007

decode (a.cancel_time,'',sum ((to_date('23:59','hh24:mi') - a.alarm_time)*24*60), sum((a.cancel_time - a.alarm_time)*24*60)) Duration,

After executing this statement. I get negative values if cancel time is null

I want to subtract alarm_time by 24 hours, if cancel time is null. If not null then cancel-time - alarm_time

View 1 Replies View Related

SQL & PL/SQL :: Display Time Format In Oracle By Create View

Nov 16, 2011

I am not able to find any information regarding inserting data with timestamp format without putting the date.

This is what i want to do:

ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RR HH24:MI TZR';

I create the column data type as timestamp with time zone. This is value i have inserted:

Insert Into TEST Values('05-OCT-2013 01:00 +08:00','05-OCT-2013 23:00 +08:00','SCIENCE);

And now i wish to display it in 'HH24:MI TZR' only after i deduct both of the time. This is the view i have created:

Create View TESTRECRDS As Select (STARTTIME- ENDTIME)*24 As DURATION, Science;

But when i select it display as weird timing because it would default in 'DD-MON-RR HH24:MI TZR' format.

BUT IF i create the view and display as 'HH24:MI TZR', it would gives me error:

Create View TESTRECRDS As Select To_Char(STARTTIME- ENDTIME, 'HH24:MI TZR')*24 As DURATION, Science;

Error would be invalid number. How can i display only 'HH24:MI TZR' after subtracting?

View 3 Replies View Related

SQL & PL/SQL :: Display Data In Required Format By Select Query?

Dec 12, 2012

i like to display a Table data like the below format,

Output:

EMPNO JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
00094 122 153 145 224 245 545 114 544 444 111 555 222
00095 233 123 145 224 205 545 114 444 444 111 555 222
00096 163 123 145 224 215 545 114 551 444 111 555 222
00097 163 0 145 224 215 545 114 551 444 111 555 222
conditions:

where condition:
where year = 2007

Table Structure:

create table HR_PAYSLIP
(
EMP_NO VARCHAR2(6) not null,
YEAR NUMBER(4) not null,
MONTH NUMBER(2) not null,
BASIC_PAY NUMBER(9,2),
)

Insert Command;

INSERT INTO PAYSLIP (EMP_NO, YEAR,MONTH,BASIC_PAY)
VALUES(00046, 2007, 1, 2314);

Pls Note: The above table data i have mentioned is an example with employee numbers and the basic_pay for all months in the particular year 2007, the employee no may be more and that must be displayed only one time like above for year 2007, and if the basic salary is zero for a month then it should be displayed as zero for a particular month

So how to write a Query for that?

View 9 Replies View Related

SQL & PL/SQL :: How To View Rows In Table Format Using Select Query

May 28, 2012

I need to view the rows of the result of a select query in table format in GUI application.I have used XMLELEMENT to somewhat match the scenario to display as ','(comma) separate values as b belwo

SELECT RTRIM (XMLAGG (XMLELEMENT (e, EMPNO || ',')).EXTRACT ('//text()'),
',')
AS empid,
RTRIM (XMLAGG (XMLELEMENT (e, ENAME || ',')).EXTRACT ('//text()'),

[code]...

But the case is I need to display the value in table format Horizontally as below

EMPIDemployee nameDEPID
778
278CLARK10
397MILLER
934KING

[code]...

View 14 Replies View Related

Application Express :: Change Read Only Condition From Always To None On Real Time

Sep 4, 2012

Can we change the condition of an item from always to none on the real time ? How ?

View 3 Replies View Related

ASM Disk Groups Creation After ASM Instance Creation

Jul 9, 2012

During ASM Disk Groups creation after the ASM instance creation, receive the following error: Disk Group ORAASMGROUP2 already exists. Cannot be created again

The Grid infrastructure was deinstall one time and still the same issue.

View 4 Replies View Related

Application Express :: Capture On Change Event For Select List Using JavaScript

Nov 15, 2012

Is there any way I can capture on change event for the Select List (already created on the page) using java script and then refresh chart region?

View 2 Replies View Related

SQL & PL/SQL :: Time Based Select

May 24, 2010

The following query gives error.

select count(*)
from invoice
where inv_id in (SELECT inv_id FROM invoice_hist WHERE to_char(timestamp) between TO_char('05/12/2010 18:22:00', 'MM/DD/YYYY HH24:MM:SS') and
TO_char('05/21/2010 18:22:00', 'MM/DD/YYYY HH24:MM:SS'));.

Here timestamp is of type DATE in the invlice_hist table.

What change is needed?

View 9 Replies View Related

PL/SQL :: Select Exists On Time

Sep 17, 2013

I have this sample:
the column data1 is datetime datatype  with
t as (   select 'SMITH' nom,to_date('21/09/2013 07:30:00') data1
from dual union all   select 'ALLEN',to_date('21/09/2013 07:40:00')
from dual union all   -- select 'WARD',to_date('21/09/2013 07:50:00')
from dual union all   
select 'JONES',to_date('21/09/2013 08:00:00')
from dual union all   

[Code]..

How can I write a select to check that If I input 10 minutes to nom 'ALLEN' it's ok because the time 07:40 + 10 minutes = 07:50 the row not exists, (the next)but If input 20 it exists because the sum = 08:00 and row  isn't free , indeed, there is 'JONES'?

View 4 Replies View Related

PL/SQL :: Select Time From Sysdate

Sep 20, 2013

I have to concatenate a date(not sysdate) with the system time and store it in a column having datatype as DATE... So I tried like this....

SELECT TO_CHAR (C_DATE, 'DD-MON-YYYY')      || ' '    || TO_CHAR (SYSDATE, 'HH:MI:SS PM')    FROM   DUAL; 

But while inserting the output of the above query in my table it throws error like this... ORA-01830: date format picture ends before converting entire input string Is there any possibility to achieve this.

View 6 Replies View Related







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