SQL & PL/SQL :: Custom Date Or Data Type To Compare Semesters
Jul 26, 2011
I have a table with two columns called startsem and gradsem they are both CHAR(3). Within those colums are rows that are listed as SemesterYear. For example, F09 is Fall 2009, S09 Spring 2009, and M09 is Summer 2009. I would like to create a constraint that says GradSem must be greater than StartSem b/c no one can travel back in time to graduate. However, as you know you can compare S09 > F09 because it will treat it as a string. I thought I could use a substring and compare the last two digits as a year and that would work but how do I compare the semesters as a time frame? Because in my schema F > S because Spring 2011 comes before Fall 2011 but in reality F < S because to Oracle it is a string and the ASCII value of F is less than S. I cannot chage the coding of the database so editing the rows so they are more date friendly is not an option.
So how can I modify this database to acruately compare StartSem and GradSem.
View 7 Replies
ADVERTISEMENT
May 26, 2010
i have created one custom block in that i am taking one value in text_item and on 2nd form i have data block on that block i want filter by using WHERE clause
in that where clause i want to compare value which is taken in custom block by user..what need to write in where clause?
View 1 Replies
View Related
Apr 17, 2013
I created a custom type what it has a clob member variable:
CREATE TYPE custom_type AS OBJECT(
c_type INTEGER,
c_number NUMBER(38, 8),
c_varchar2 VARCHAR2(4000 CHAR),
c_clob CLOB,
[code]........
The inserting and updating works with constructor: ... custom_type (to_clob('foo')) . But if the data is longest than 4000 characters then the PHP isn't access to it.
So:
The normal case:
$sql = ("INSERT INTO table ( clob_field ) VALUES ( EMPTY_CLOB() ) RETURNING clob_field INTO :clob");
$stid = oci_parse($conn, $sql);
$clobdescr = oci_new_descriptor($conn, OCI_DTYPE_LOB);
oci_bind_by_name($stid, ':clob', $clobdescr, -1, OCI_B_CLOB);
oci_execute($stid);
$clobdescr->save('more than 4000 chars');
...
This case:
I tried:
$sql = ("INSERT INTO table ( ctype ) VALUES ( custom_type(EMPTY_CLOB()) ) RETURNING ctype.c_clob INTO :clob");
$stid = oci_parse($conn, $sql);
$clobdescr = oci_new_descriptor($conn, OCI_DTYPE_LOB);
oci_bind_by_name($stid, ':clob', $clobdescr, -1, OCI_B_CLOB);
oci_execute($stid);
$clobdescr->save('more than 4000 chars');
ORA says: "ORA-00904: CTYPE.C_CLOB: invalid identifier";
How to?Or how to access with any solution?
View 2 Replies
View Related
Oct 13, 2011
How can i get just date from the timestamp data type.
Suppose i have a column timestamp with has data like "2011-05-16 16:19:22.579764-07" when i select from table i just want the date like 2011-05-16.
View 6 Replies
View Related
Oct 26, 2006
How do i insert or select date data type value in am or pm Eg 10:30:00 am or 10:35:00 pm
View 3 Replies
View Related
Jun 13, 2012
I have problem to convert simply varchar to date data type.
For example: 2012-05-28 22:36:08 and I would like to get format 28.5.2012 22:36:08
However I try to do it I get always some errors.
select '2012-05-28 22:36:08',
to_date( '2012-05-28 22:36:08', 'dd.mm.yyyy HH24:MI:SS') ,
to_char('2012-05-28 22:36:08', 'dd.mm.yyyy HH24:MI:SS')
from dual
View 7 Replies
View Related
Sep 2, 2013
First, I'm aware that the equals (=) operator is a "comparison operator compares two values for equality." In other words, in an SQL statement, it won't return true unless both sides of the equation are equal. For example:
SELECT * FROM Store WHERE Quantity = 200; The LIKE operator "implements a pattern match comparison" that attempts to match "a string value against a pattern string containing wild-card characters."
For example:
SELECT * FROM Employees WHERE Name LIKE 'Chris%';
Here,I query about date type data on ORACLE database, I found the following, when I write select statment in this way:
SELECT ACCOUNT.ACCOUNT_ID, ACCOUNT.LAST_TRANSACTION_DATE FROM ACCOUNT WHERE ACCOUNT.LAST_TRANSACTION_DATE LIKE '30-JUL-07';
I get all rows I'm looking for. but when I use the sign equal =
instead :SELECT ACCOUNT.ACCOUNT_ID, ACCOUNT.LAST_TRANSACTION_DATE FROM ACCOUNT WHERE ACCOUNT.LAST_TRANSACTION_DATE = '30-JUL-07';
I get nothing even though nothing is different except the equal sign.
View 6 Replies
View Related
Jun 4, 2012
I have a table with modifieddate column with 'DATE' data type.I am facing date format exception and tried with to_char, to_date but its throwing invalid number exception. how to format date accordingly.
SELECT * FROM EMP WHERE modifieddate > '31-Dec-2011 18:30';
ORA-01722: invalid number
01722. 00000 - "invalid number"
View 9 Replies
View Related
Jun 29, 2012
Is there a seeded function by which I can check all the rows which stored dates in varchar column.
I have a table say test (test_data varchar2(100));
Now I will insert all types of records into the table varchar,number dates and then i will write q query to etch all those records only which has dates only
INSERT INTO test(1);
INSERT INTO test('ABC');
INSERT INTO test(SYSDATE);
INSERT INTO test(TO_CHAR(SYSDATE,'DD-MON-YYYY'));
INSERT INTO test(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
INSERT INTO test('15/01/2012');
...
commit;
View 17 Replies
View Related
Feb 20, 2013
I have imported data from excel to oracle 11g. But i found an error like
"Ensure format is entered for datatypes 'Date' and 'TIMESTAMP' on data type pane".
after that i try to modify type date in oracle become 'dd-mmm-yyyy'
View 14 Replies
View Related
Feb 26, 2012
I have one command compare varchar type with date type by Pl/SQL.
date_entry gave values:" 2012/01/06 22:28:24",
now i want to convert it to date and compare with sysdate.
Select * From qvsystem
Where to_char(to_date(date_entry, 'rrrr-mm-dd hh24:mi:ss'), 'dd-mm-rrrr') = to_char(Sysdate, 'dd-mm-rrrr');
----
note:date_en2012/01/06 22:28:24
after I executed that command It's appear one message .
View 7 Replies
View Related
Mar 5, 2012
i wanted to compare a date in one of my tables to sysdate. I have a table reservation and a field in it is Date Reserved From, i wanted to compare this to sysdate and returned the results
View 2 Replies
View Related
Nov 28, 2012
I'm trying to compare a date and time within a certain range.
If the sysdate date/time range falls within the range of the values within the database tables then show a "Yes", otherwise, show a "No". The date works but the time doesn't seem to work. Maybe you can't use a "between" operator for time?
CREATE TABLE REGISTRATION
(
EARLY_REGISTRATION_START_DATE DATE,
EARLY_REGISTRATION_END_DATE DATE,
EARLY_REGISTRATION_START_TIME DATE,
EARLY_REGISTRATION_END_TIME DATE
[code]....
View 5 Replies
View Related
Feb 3, 2013
I want to retrieve the data which is "n" moths old.
To compare that I want use only SYSDATE without timestamp. I want to use standard timestamp '23:59:59' along with SYSDATE.
The condition should be as below.
UPDATE_DATE <= ADD_MONTHS(15/01/2013 23:59:59,-3)
UPDATE_DATE <= ADD_MOTHS(30/01/2013 23:59:59,-4)
UPDATE_DATE<=ADD_MONTHS(sysdate||' '||'23:59:59',-3);
View 6 Replies
View Related
Oct 17, 2011
Is there some functions to convert the long type field data to varchar2 type?
View 2 Replies
View Related
May 9, 2013
I'm testing the geocoded with my custom data from El Salvador. I have configured all the tables required. My GC_PARSER_PROFILEAFS is:
<address_format unit_seperators="," replace_hyphen="true">
<address_line>
<place_name />
</address_line>
<address_line>
<street_address>
[code]....
Why I'm I getting null when searching for streets and not getting all the geocoded data right?
View 1 Replies
View Related
Jun 15, 2011
I have data block that has hundreds of records. On a screen I display 20 records at a time. On that same screen I have "find badge" function where user types badge/employee id and presses a button to find the record in a data block. I have no problem finding the records in a list and pointing the curser to appropriate record. My question is (which is a user request) if the badge/employee id is found to make that record to be the first record in a list.
View 2 Replies
View Related
Oct 25, 2011
oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"
In the database, NLS_DATE_FORMAT = DD-MON-RR.I have a date field called LOAD_DATE in one of the tables. I want to store the value as '25-OCT-11 01:16:25 PM' in LOAD_DATE field. Is there a way to do it without changing the NLS parameter (ALTER SESSION...) ?
View 8 Replies
View Related
Mar 3, 2011
I am working with Oracle 8i and I have a problem while applying the below query:
select to_char(ENTRYDATE,'MM-DD-YYYY HH24:MI:SS') from TBL_BANDWIDTH_READS
where ENTRYDATE > sysdate-1
and rownum <10
03-03-2011 00:00:00
03-03-2011 00:00:00
03-03-2011 00:00:00
03-03-2011 00:00:00
[code].....
The time appears as 00:00:00. I said it's something weird because if I take the where condition off, then something like this appears:
select to_char(ENTRYDATE,'MM-DD-YYYY HH24:MI:SS') from TBL_BANDWIDTH_READS
12-22-2006 13:13:27
12-22-2006 13:13:27
12-22-2006 13:13:27
12-22-2006 13:13:27
12-22-2006 13:13:27
12-22-2006 13:13:27
12-22-2006 13:13:27
I am using the same query on a different table and there's no problem on it:
select to_char(ENTRYDATE,'MM-DD-YYYY HH24:MI:SS') from TBL_XX_PROGRAM
where ENTRYDATE > sysdate -10 and rownum <10
03-03-2011 17:06:48
03-03-2011 17:06:48
03-03-2011 16:59:08
03-03-2011 16:59:08
03-03-2011 12:04:21
I checked the DDL of both tables and the only difference is that the TBL_XX_PROGRAM have the ENTRYDATE field defined as not null and the ENTRYDATE on the TBL_BANDWIDTH_READS does not have the same constraint, could it affect?
View 3 Replies
View Related
Jan 26, 2011
I have table called INFO and the column called CREATED_DATE . Now the datatype of CREATED_DATE is of varchar2 . Now If I need to query the table through select statement where I need to order the result based on CREATED_DATE , how can i achieve this ?
View 1 Replies
View Related
Aug 15, 2011
I'm having trouble comparing and subtract two date type fields, can not get a significant result.
e.g.
select id, date.started, date.closed, datediff (day, date.started, date.closed) differences diference from Table1 order by desc;
View 4 Replies
View Related
Sep 5, 2012
I am trying to run select query on xml using xmltable. One of the tag returns date and I want to take it in date format in xmltable column.
select t.* from XMLTYPETEST xt ,
XMLTable ('/TestData/trade' passing xt.data
columns
test varchar2(100) path 'GenTest/Id',
testdate date path 'DateTest/Date')t;
But its throwing error as -
SQL Error: ORA-29958: fatal error occurred in the execution of ODCIINDEXCREATE routine
ORA-01830: date format picture ends before converting entire input string
29958. 00000 - "fatal error occurred in the execution of ODCIINDEXCREATE routine"
*Cause: Failed to successfully execute the ODCIIndexCreate routine.
View 2 Replies
View Related
Mar 5, 2010
How to identify the columns which are different between two oracle tables. I have nearly 30 columns in each table comprising of million rows
View 11 Replies
View Related
Nov 23, 2010
We store the date in String Type format of "MON-YYYY", we require to run the report base on that string contain date column, During the Query execute the Error Display of (ora-01858: a non-numeric character was found where a numeric was expected)
The Query is :
select period_name,doc_sequence_value,doc_sequence_id,date_created,name,JE_SOURCE,XX
from
(
select period_name,doc_sequence_value,doc_sequence_id,date_created,name,JE_SOURCE,TO_DATE(XX,'DD/MM/RRRR') XX
from
(
select TO_CHAR(TO_DATE(('01-'||substr(gl.period_name,1,3)||'-'||substr(gl.period_name,5,2)),'DD/MM/RR'),'DD-MON-RRRR') xx,
[code].....
View 4 Replies
View Related
Aug 29, 2012
I have an requirement like below and would like to have SQL for that.
Source Table:
EMP_NO EMP_CODE
1 'A'
1 'D'
1 'E'
1 'F'
2 'S'
2 'A'
2 'W'
2 'Q'
3 'A'
3 'T'
3 'D'
3 'E'
4 'D'
4 'A'
I want to load only data which has EMP_CODE as A and doesn't have subsequent 'E' or 'F' in it. In the above source you can see EMP_NO 2 and 4 satisfy the condition and rest wont. So i want the output data like below.
Desired output:
EMP_NO EMP_CODE
2 'A'
4 'A'
View 4 Replies
View Related
Mar 12, 2012
create type nesttype as table of clob;
create table emp
(empno number,
ename varchar2(1000),
language_known nesttype
)
I want to check whether language is already there in database or not.
i have written the below query
select * from emp where language_known =nesttype('english','hindi');
i am getting the below error
SQL Error: ORA-22901: cannot compare nested table or VARRAY or LOB attributes of an object type
22901. 00000 - "cannot compare nested table or VARRAY or LOB attributes of an object type"
*Cause: Comparison of nested table or VARRAY or LOB attributes of an
object type was attempted in the absence of a MAP or ORDER
method.
*Action: define a MAP or ORDER method for the object type.
How to compare data in nested table
View 16 Replies
View Related
Feb 22, 2013
Is there anyway to compare the non numeric data in a table to a numeric number.Want to run a query like
SELECT rank_id
FROM mas_rank
WHERE rank_code > 26 Rank_code contains numeric as well as some non numeric data
View 8 Replies
View Related
Jun 1, 2010
1) how to to compare sybase table data with oracle database table data?
2) how to connect sybase database from oracle database?
View 5 Replies
View Related
Jan 5, 2011
CREATE TABLE SCHEDULE_DETAILS
(
SCHEDULE_ID NUMBER NOT NULL,
SCHEDULE_TYPE VARCHAR2(10 BYTE),
SCHEDULE_START_DATE DATE,
SCHEDULE_END_DATE DATE,
RUNTIME CHAR(8 BYTE),
TIMEZONE VARCHAR2(40 BYTE));
SET DEFINE OFF;
Insert into SCHEDULE_DETAILS
(SCHEDULE_ID, SCHEDULE_TYPE, SCHEDULE_START_DATE, SCHEDULE_END_DATE, RUNTIME, TIMEZONE)
Values
(1970, 'Daily', TO_DATE('07/26/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
TO_DATE('01/26/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '13:58 ', 'America/New_York');
[code]........
Taking Today date and timezone as EST, I need to run a select that shows all the rows , where sysdate falls in between
Start_date and End_date and RunTime, ( run time is basically the Local time of the TimeZone Column)
Basically we should Display rows by checking/Converting, Start_date||End_date||Runtime||timezone with Sysdate(est) then display.
From the Above Data these rows should be Displayed by that select, how to compare this data with sysdate and display.
SCHEDULE_IDSCHEDULE_TYPESCHEDULE_START_DATESCHEDULE_END_DATERUNTIMETIMEZONE
1970Daily7/26/20101/26/201113:58 America/New_York
2588Daily10/18/20104/18/201115:50 America/New_York
3567Daily12/8/20106/8/20118:40 America/New_York
3386Daily12/27/20106/27/20111:0 America/New_York
1973Daily8/3/20102/3/201111:25 America/New_York
2565Daily9/7/20103/7/20117:0 America/New_York
3580Daily12/20/20106/20/201117:0 America/Chicago
3167Daily11/30/20105/30/20111:0 US/Alaska
3390Daily12/30/20101/15/20117:00 Asia/Calcutta
For Example, Below rows shouldn't come, Since it's end date is less than Sysdate.
SCHEDULE_IDSCHEDULE_TYPESCHEDULE_START_DATESCHEDULE_END_DATERUNTIMETIMEZONE
2579Daily9/17/20109/18/201011:32 America/New_York
View 2 Replies
View Related
Aug 13, 2012
I would like to run below query on all tables, however it doesnt work on clob and long datatypes
select * from owner.table_name
minus
select * from owner.table_name@remote_db;
from dba_tables
where owner in ( '....');
ORA-00932: inconsistent datatypes: expected - got CLOB
How can I compare the data of clob and long datatypes using dblink ?
View 2 Replies
View Related
