Application Express :: Date Formatting - Not A Valid Month

Apr 29, 2013

I am using the cloud version of APEX for a college course. The database script I am trying to load has dates formatted in "DD-MON-RR" and I receive 'not a valid month' and 'a non-numeric character was found where a numeric was expected'. Is there a workaround in APEX for this? I'm trying to avoid changing thousands of lines of data.

View 4 Replies


ADVERTISEMENT

SQL & PL/SQL :: ORA-01839 - Date Not Valid For Month Specified?

Nov 1, 2013

our web application has a field DATETO which has no validation (end date). So user can put garbage there. In this field should be SYSDATE + max. 2 years. But also SYSDATE + 1 month OK.

I need to make a script that sets for example "31.12.9999" to null. Because so long end date is non sense.

SQL> SELECT EXTRACT (YEAR FROM (TO_DATE (DATETO, 'DD.MM.YYYY')))
FROM XXX_USER_JC_ORDERID
WHERE DATETO IS NOT NULL;
ERROR:
ORA-01839: date not valid for month specified

no rows selected

Littlefoot writes about ORA-01839 "It usually happens when invalid dates are used, such as 30th of February"[URL]..

I don't see invalid date???

SQL> SELECT DATETO
FROM XXX_USER_JC_ORDERID
WHERE DATETO IS NOT NULL;
DATETO
--------------------------------------------------------------------------------
23.09.2013

[code]...

DATETO
--------------------------------------------------------------------------------
31.12.2014
31.12.2016
31.12.2013
31.08.2014

[code]...

View 7 Replies View Related

Application Express :: Validation On Text Item / User Enter Valid Date Format

Oct 13, 2012

i have text item :P1_ADMISSION_DATE .i want to validate that item if user does not enter valid date forrmat then display me error.

Date Format is 'DD-MON-YYYY'

i have try with PL/SQL Expression in Validation

My validation code

:P1_ADMISSION_DATE=TO_CHAR(SYSDATE,'DD-MON-YYYY');How can i validate :P1_ADMISSION_DATE to enter valid date format by user.

View 2 Replies View Related

Application Express :: Formatting (dollar Value) Within ITEM?

Mar 21, 2013

So I'm trying to display a monetary value in an item.

SELECT to_char(SUM(TXN_AMT),'$9,999') from CMS_COST_TRACKING where UPPER(TXN_NODE)='VMAX'this results in ######### in the item

If I remove the to_char formatting then the SUM displays correctly.Is this a restriction of some kind. The item is a display only.

APEX Application Express 4.1.1.00.23
ORacle 11G.

View 1 Replies View Related

Application Express :: Formatting A Table Without Headings

Apr 3, 2013

I want to make a report which is formated (rebuilding an older tool in Access).

So on the one hand I need some formattings which I implemented with the search bar in the interavtive report. (content of the first column is the heading for a table with the rest of the content of all rows which have the sam content in the first column) On the other hand I don't want to show the headings of the columns, what I solved with the classic report(report attributes/ column attributes: none).
Is the a solution to match these two requirements in one report?

View 4 Replies View Related

SQL & PL/SQL :: ORA-01843 / Not A Valid Month

Jul 25, 2011

I have a date field with varchar datatype. My data look like :

ID Amend_DATE
1 4/24/1974
1 4/18/2001
1 8/2/1971
2 8/4/1965
3 11/9/2001
4 8/15/2001

I need to find the minimum amend_date for each ID.When i calculate min(amend_date), it gives me error:

ORA-01843 : not a valid month

Query i am using is :

select min(to_date(amend_date ,'mm/dd/yyyy') ) from t group by id;

My data on application appears as 24 April 1974.

View 2 Replies View Related

ORA-01843 / Not A Valid Month

Sep 14, 2012

I have 2 cursors. Cursor 1 selects a number and 2 dates (values in the Table are in the format mm/dd/yyyy for both the columns).

I pass these values to Cursor 2.

v_promotion_id promotion.promotion_id%TYPE;
v_promotion_start_date promotion.start_date%TYPE;
v_promotion_end_date promotion.end_date%TYPE;
CURSOR c_promotion_list IS
(SELECT DISTINCT p.promotion_id,
p.start_date - 5,
p.end_date + 5
FROM promotion p,

[code]....

I am getting this error: ORA-01843: not a valid month

Initially I was passing just the variables without To_date. But I got the same error.

View 2 Replies View Related

Application Express :: Interactive Report - Break Formatting Style?

Nov 14, 2013

,Is there a way to make Interactive reports break format work/look like classic reports break format? i.e.1st,2nd,3rd column option. The way IR breaks is that is reserves a whole row for the column used in the break, which is not what I want, I want the report to look like the following:

 city               emp           sal 
Chicago     John Miller  1500                
Mark Horton 2000
Denver       Rob Martino 1200 

I tried to use rollup queries which work fine except that it does not do exactly what I want when I sort columns through the front end , I am basically displaying a check box for grouped records ( and I want it to display at the first record of each group), so the order is important. for example: 

checkbox   City               emp           sal[ ]              
Chicago     John Miller    1500         
Mark Horton  2000[ ]             
Denver       Rob Martino  1200 

View 2 Replies View Related

SQL & PL/SQL :: Time Conversion - Not A Valid Month Error

Aug 16, 2010

why this query is not working

SQL> select xid,start_time from v$transaction;

XID START_TIME
---------------- --------------------
070006009A010000 08/16/10 13:24:43

SQL> select xid,start_time from v$transaction where start_time>to_timestamp('08/16/2010 12:15:00','mm/dd/yyyy hh24:mi:ss');
select xid,start_time from v$transaction where start_time>to_timestamp('08/16/2010 12:15:00','mm/dd/yyyy hh24:mi:ss')
*
ERROR at line 1: ORA-01843: not a valid month

View 17 Replies View Related

Server Utilities :: Error Not Valid Month In Sql Loader?

May 17, 2012

I am trying to load below data using sql loader.

05/17/12,07:45:39,resn-j35-ctc113,"USR:ESM.CQueryTypeDlg::RunQuery().wft",0.125,P,schapptbook,22712,25704,705355992045,RBA4010200,10.146.218.154,0, "",0,"","",""

table structure
CREATE TABLE TEMP_CERNER_RESP_TIME_LND
(
INSTALLATION_ID VARCHAR2(50 BYTE) NULL,
TRANSACTION_ID VARCHAR2(50 BYTE) NULL,
SERVER_ID VARCHAR2(50 BYTE) NULL,

[code]...

Below function has been used to transfor data and callled in sql loader control file
CREATE OR REPLACE function return_domain( domain_name varchar2)
return varchar2
as
v_dmn varchar2(100)

[code]...

sql loader control file is as below:

load data
BADFILE '/backup/temp/rajesh/CERNER/BadFiles/FILENAME'
append into table TEMP_CERNER_RESP_TIME_LND
WHEN CLINICAL_TRANSACTION_ID = 'USR:ERM PMSEARCH ENCOUNTER RESULTS DISPLAY'
TRAILING NULLCOLS

[code]...

function takes the parameter as 'DOMAIN50_LPAR5002_slainterval051712_rj35cmi102_08_45_00.csv '

FILENAME in control file will be replace by DOMAIN50_LPAR5002_slainterval051712_rj35cmi102_08_45_00.csv when i run the the the loader i get the below error.

Record 1: Rejected - Error on table TEMP_CERNER_RESP_TIME_LND.
ORA-00604: error occurred at recursive SQL level 1
ORA-01843: not a valid month

View 4 Replies View Related

SQL & PL/SQL :: Function That Accepts 2 Dates - Not A Valid Month Error

Apr 15, 2011

I have function that accepts 2 dates namely sDate and eDate i.e start and end dates. See the declaration part of the function:

startDate eab.eod_date%type;
endDate eab.eod_date%type;

begin
startDate := to_date(sDate,'DD-MON-YY');
endDate := to_date(eDate,'DD-MON-YY');
end

When I called the function it gives me the following error: ORA-01843: not a valid month..And I tried to simulate the process, the below code also show the same error:

select TO_DATE(to_date('13-DEC-09', 'dd-MON-yy', 'nls_date_language=english'),'DD-moN-YY') from dual;

how I can check that error. I need to pass dates to my function.

View 8 Replies View Related

Date Formatting In Oracle

Oct 24, 2006

I have the age of a property stored in my DB. (Lets say 3 yrs old). I would like to extract from that information, the year the property was built. I know I can minus the sysdate by days (-1) and months (add_months), but how can I do it using the YEAR information I have stored?

So if I was doing it against sysdate, I would want the result to be 10/24/2003.

View 2 Replies View Related

SQL & PL/SQL :: Formatting Date And Time

Jul 21, 2011

I have the following piece of

SELECT decode(substr('18900607|000000|20110718112109|23972|002',
17,
14),
'00000000000000',
NULL,
(to_date(substr('18900607|000000|20110718112109|23972|002',
17,
14),
'YYYY/MM/DD HH24:MI:SS')))
FROM dual;

When I run the above code I get an incorrect display ie. "18-JUL-11". when I remove the decode I get the correct display ie."2011/07/18 11:21:09 AM"

View 2 Replies View Related

PL/SQL :: Losing Date Formatting Somewhere?

Jun 26, 2013

So the data is coming from a collection where the first column is like '1-May' 

declare
no_year EXCEPTION;
  holddate VARCHAR2(20);
  totaldate VARCHAR2(20);
  cursor emp_cursor is

[code]...

I can't see what is in the temp table because even when I don't truncate it global temp table and can't get to the information in it. 

View 1 Replies View Related

Application Express :: Unable To Set Default Date Value For Tabular Form DB Date Field?

Dec 3, 2012

Version : 4.1.1, I have a tabular form on a DB table. One of the columns is a date field. When the user hits the "add Row" button on the tabular form, I want the Date field to be defaulted to sysdate. Here is what I have tried so far,

1. Created a "hidden" item P1_SYSDATE and populated the default value with sysdate. After this, under the DB tabular report date field, I used default type - Item/application on this page and entered P1_sYSDATE

2. Instead of populating the default value of the P1_SYSDATE hidden item, I created a before regions process and added

:P1_SYSDATE := sysdate

and added P1_SYSDATE to default type of the tabular date field with default type as "ITem/application on this page.

I get the error

ORA-01790: expression must have same datatype as corresponding expression

I tried to_Char(sysdate,'dd-mon-yyyy') and then converting it back to to_date. still no luck.

View 1 Replies View Related

Forms :: Verification That User Selected Valid Date

Mar 19, 2013

I have 3 types list items:

1) :1 Day -> 31
2) Month: 1 -> 12
3) Year :1990 -> 2013

I want to check that the user has selected a valid date .. if not an error message will be displayed, for example: 31-02-2013: will be rejected

View 9 Replies View Related

SQL & PL/SQL :: Required Data For Month To Date And Year To Date?

Apr 30, 2012

I want to get data for month to date. For example, If I pass today or any day date as parameter then i should get data for that month(month of passing date) up to passing(parameter) date. As well as i have to get year to date.For example, If I pass today or any day date as parameter then i should get data for that financial year(year of passing date) up to passing(parameter) date. how to get month to date and year to date data.

View 3 Replies View Related

Application Express :: Date Without Weekend

Apr 22, 2013

I need to block the weekends in Date Picker item. It must appear disabled.

View 1 Replies View Related

Application Express :: How To Put More Than Date Per Row On Calendar

Jun 11, 2013

I have a table with  ID_ITEMITEM_NAMEITEM_DEV_DATEITEM_PROD_DATEITEM_TESTING_DATE 

I need to put the ITEM_NAME+'DEV', ITEM_NAME+'PROD', ITEM_NAME+'TESTING' on one calendar,

the calendar provided by APEX is supposed to ask one date per item, so how I can put more than a date per item?  So for example

ITEM ITEM_NAME='Release 1'ITEM_DEV_DATE='15-May'ITEM_TESTING_DATE='21-May'ITEM_PROD_DATE='30-May'

View 1 Replies View Related

Application Express :: Validate Date And Time

Jul 18, 2012

I am using Apex 3.2

I have a field on my form called P4_START..It has a format mask of DD.MM.YYYY HH24 MI and a default value of TO_CHAR(SYSDATE, 'DD.MM.YYYY HH24:MI').

I need to create a validation which fires on save, to ensure the user is inputting a date and time

View 5 Replies View Related

Application Express :: Global Item Date

Dec 19, 2012

in my application I have several pages with the item of date. How to ensure that all items had the same date for all pages and can be set on each page ?.

Application Express: 4.1.0.00.32
DB: Version     10.2.0.4.0

View 7 Replies View Related

Application Express :: 3.2 - Validate Date Format

Oct 17, 2013

Apex 3.2 I have a field called P14_START_DATE.The display as type is Date Picker (DD.MM.YYYY). Now if the user uses the pop up calendar, the field is populated with eg 07.12.2013. I have 2 standard validations, not null and item specified is a valid date. If the user decides to type in the date, they can use a different format, eg 07/12/2013, which I do not want to happen.How can I validate the field, so they get an error if they do not use format dd.mm.yyyy.

View 2 Replies View Related

Application Express :: Date Picker With Seconds?

Apr 11, 2013

I'm using Apex 4.2.1 against Oracle 11gR2 and mod_plsql.create a date picker item that allows users to select seconds as well as hours and minutes.

I have searched this Forum and see that others have asked this question. The answers have all been "No". However, I've seen no such question since version 4.1 has been released, and so, am hoping there now is a way to do this.

I have adjusted the Date Format field to be "DD-Mon-YYYY HH24:MI:SS", both under the date picker item itself as well as under the "Global" parameters section of my application. All to no avail. The date picker shows only hours and minutes for the time portion.

View 2 Replies View Related

PL/SQL :: How To Get Min Date Of Every Month For Six Months

Mar 25, 2013

I have data like this.

Process_date SEQ_No
------------- ---------
16-MAR-13     733
09-MAR-13     732
02-MAR-13     731
24-FEB-13     730
16-FEB-13     728
09-FEB-13     727
02-FEB-13     726
26-JAN-13     725
21-JAN-13     724
12-JAN-13     723
05-JAN-13     722
29-DEC-12     721
24-DEC-12     720
15-DEC-12     719
08-DEC-12     718
03-DEC-12     717
22-NOV-12     716
17-NOV-12     715
10-NOV-12     714
03-NOV-12     713
29-OCT-12     712
23-OCT-12     711
13-OCT-12     710
05-OCT-12     709
28-SEP-12     708
22-SEP-12     707
15-SEP-12     706
08-SEP-12     705
01-SEP-12     704

every month admin will refresh actual data table and automatically this above table will update with unique seq_no and process_date.

I need to extract min date of every month(First refresh of last 6 months - excluding current month) and also seq_no related to that month so using joins(using seq_no - that is available in main table) i can combine actual data.

I need result like:

02-MAR-13     731 ( I don't need MAR as it should not take current month data)

so i need final result like below:

02-FEB-13     726
05-JAN-13     722
08-DEC-12     718
03-NOV-12     713
05-OCT-12     709
01-SEP-12     704

View 8 Replies View Related

Application Express :: How To Validate Date In Column Section

Dec 19, 2012

How to validate a date in column section?

View 1 Replies View Related

Application Express :: Report String Column To Date?

Apr 22, 2013

v: Apex 4.2

I have Dynamic report and column say some_date column and data can be in the columun (01-JAN-13) OR (01-JAN-13,04-JAN-13), (01-JAN-13,04-JAN-13,10-JAN-13) so on.

in actual table column some_date is date field but in report as above (string) because using RTRIM(some_date, ',') some_date within the select MODEL clause.

my problem is when run the report and select filter from action menu then it doesn't give option such as < , > or so on because of string but I want it should give <, <=, >, >= operators too as date column.

View 5 Replies View Related

Application Express :: ORA-01821 / Date Format Not Recognized

May 15, 2013

My environment settings:

Oracle Database 11.2.0.3.0 64 bits
Weblogic 10.3.5
APEX 4.2.2.00.11

When I try to access the development environment of the apex or any other screen, this error occurs intermittently.These errors (ORA-01821: date format not recognized) and (ORA-02063: preceding line from) are related to existing dblink's.

Parameters from apex database:

SQL> select * from nls_instance_parameters;
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_SORT SPANISH
NLS_DATE_LANGUAGE AMERICAN

[code]....

Parameters from dblink database:

SQL> SELECT * from NLS_instance_PARAMETERS;

PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_SORT SPANISH
NLS_DATE_LANGUAGE

[code]....

View 0 Replies View Related

Application Express :: IR Report Date Column Popup

Apr 30, 2013

I have an IR report and want to add another column such as complete_date. I want to add as a link so when user click it then should display the popup window or javascript popup for date selection. i don't want to use apex_item.date_popup or apex_item.date_popup2. On date selection popup user can choose only two dates such as yesterday or today all other date should be disabled?

View 2 Replies View Related

Application Express :: Update Record In Another Row When Date Changed

Oct 10, 2013

As all of you know, In the Apex,  when you create a form with report in the page, you are able to insert and edit data. But when you edit the data, the data will be modified in the same row. In other word, you loose the old data.

What I need to do is: I have revised_num field and production_date field. I want to create a form with report and insert and edit data as is in the apex and insert 0 to the revised num until production date is null. But when  production date is not null, then from that point, I want to insert data to another row and modify revised num to 1. and I want the revised num be incremented by 1 each time the user modifies the data after the production date is not null.I don't know where I should start.

View 12 Replies View Related

Application Express :: Combined Date Sorting With Anychart

Apr 25, 2013

I have a table in which I extract the year and the month and both in combination (from a date type). This is needed to summarize for example the year in a chart.

I need the combination of month and year because people could do a query which switches through years. For expample: From 02.2012 to 03.2013.My problem is I dont know how to tell anychart to sort the dates. I have already tried:

to_number(to_char(testdate,'YYYY')) as Year,
to_number(to_char(testdate,'YYYY'))||to_number(to_char(testdate,'MM')) as MonthyearApex now sorts:

20121,201212,20122

I would like that apex realizes to order:

1.2012,2.2012,3.2012 .........01.2013,02.2013.03.2013

The report itself has now four date relating collums:

1) Date (Type Date)
2) Year (Type Number)
3) Month (Type Number)
4) Monthyear (Type varchar)

View 3 Replies View Related







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