SQL & PL/SQL :: Getting Date Gaps From Data Of Date Field
Sep 24, 2011
I have a date field that should be filled everyday with today's date and I need to get the days that were not entered.
i.e. :
CREATE TABLE TRY_F (DAT DATE);
INSERT ALL
INTO TRY_F VALUES (to_date('01/01/2011','DD/MM/YYYY'))
INTO TRY_F VALUES (to_date('02/01/2011','DD/MM/YYYY'))
INTO TRY_F VALUES (to_date('04/01/2011','DD/MM/YYYY'))
INTO TRY_F VALUES (to_date('05/01/2011','DD/MM/YYYY'))
INTO TRY_F VALUES (to_date('06/01/2011','DD/MM/YYYY'))
INTO TRY_F VALUES (to_date('08/01/2011','DD/MM/YYYY'))
INTO TRY_F VALUES (to_date('10/01/2011','DD/MM/YYYY'))
INTO TRY_F VALUES (to_date('14/01/2011','DD/MM/YYYY'))
SELECT * FROM DUAL;
I need a smart way of getting the dates that were missed in DAT.
BANNER Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi PL/SQL Release 10.2.0.5.0 - Production "CORE10.2.0.5.0Production" TNS for Linux: Version 10.2.0.5.0 - Production NLSRTL Version 10.2.0.5.0 - Production [code]....
Requirements for the result set:
1. List the root division as column headers 2. Start date is March 15, 2012 3. End date is June 30, 2012 4. Do not skip or omit dates 5. The values under each column are percentages where completed = 'Y' 5a. Example, D5_Total = 277, D5_Completed(Y) = 254, D5_Percent(Y) = 254/277 = 92% 5b. Rounded to whole percentage like 92% and NOT 91.696%
The result set will be used to create a line graph. The graph will show which root division is closer to reaching 100% before the deadline of June 30, 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.
A field named xxx_date is a text item which we have to enter manually so as to update a record in that particular date. This is a mandatory field without which we cannot continue the data entry..
I am getting this error while trying to update the record
FRM-40509 :Oracle error :unable to update record
I have kept the enabled = yes required=no data type=Date.. in the property pallet
I am having problems with the XMLTable function. I cant get it to see the entire date/time value in a date field. This wont work
select x1.* from XMLTABLE('/DOCUMENT' passing xmltype('<DOCUMENT><STR>abc def ghi</STR><NUM>1234</NUM><DT>2013-02-17T04:24:02</DT></DOCUMENT>') columns STR varchar2(25), NUM number, DT date) x1;
However if I change the DT tag to just the date only "2013-02-17" it works. Why wont Oracle see the entire date/time format even if its ISO 8601 compliant?
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.
What i wnat is to update the Customer_inactive_date with the Incative_date field from Customer_type based on their Customer_type... So james and Jill would have their rows updated in this scneario ..How can i achive this in pl/Sql
I have teh code using merge function..I want something in traditional old fashion..
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.
Begin insert into employee_rev values(111, 'DEC-09', '31-DEC-2009', '01-APR-2009', 1300); insert into employee_rev values(111, 'JAN-10', '31-JAN-2010', '01-APR-2009', 1000); insert into employee_rev values(111, 'FEB-10', '28-FEB-2010', '01-APR-2009', 800); insert into employee_rev values(111, 'MAR-10', '31-MAR-2010', '01-APR-2009', 1000); insert into employee_rev values(111, 'APR-10', '30-APR-2010', '01-APR-2010', 1000); insert into employee_rev values(111, 'MAY-10', '31-MAY-2010', '01-APR-2010', 1100); insert into employee_rev values(111, 'JUN-10', '30-JUN-2010', '01-APR-2010', 2100); End;
I also need a YTD (Year to Date) field at the last which is sum of current month revenue_amt and sum(revenue_amt) for previous months for a particular financial_year_date.
The output should be:
Person_ID Month End Date Year Date Rev YTD 111 Dec-09 31-Dec-09 1-Apr-09 1300 1300 111 Jan-10 31-Jan-10 1-Apr-09 1000 2300 111 Feb-10 28-Feb-10 1-Apr-09 800 3100 111 Mar-10 31-Mar-10 1-Apr-09 1000 4100 111 Apr-10 30-Apr-10 1-Apr-10 1000 1000--change in financial year 111 May-10 31-May-10 1-Apr-10 1100 2100 111 Jun-10 30-Jun-10 1-Apr-10 2100 4200
I guess this should be achievable using some analytical functions, but I am unable to get the desired output.
I have a field (called Date_Time) which displays for example 1/31/2005 12:00:00 AM. I would like to run a query that converts that value to '200501' in a created field.
I tried the following below but I keep having problems.
select Account_Number, Date_Time, concat(year(Date_Time), month(Date_Time)) as Date_Time_Modified from table where Account_Number = xxxx
if i make the source for a field is a current date with format dd/mm/rrrr hh:mi:ss and the report published on the application server and the clients from different machines run this report
what the field display on Cline or on DB or On Application Server?
In a detail tabular form I am referencing a date picker field from the master form as default value.This is the situation in the detail form:
TABULAR FORM Column Name: FECHAREGISTRO -- the field's name in the detail tabular form
Default Type: Item (application or page item name) Default: P68_FECHAREGISTRO -- It's a field in the master form and also is Date Picker format Reference Table Owner: SAMPEDRORIVEROS Reference Table Name: BITACORAABOGADO Reference Column Name: FECHAREGISTRO -- It's a date field in the database
At execution time I have the error:
report error:ORA-01790: expression must have same datatype as corresponding expression
I think it is because i must use to_date and to_char in order to change the datatype and i have tried using:
I have a question regarding a Date field on one of my form.
How do I make a Date field filter in a descending order when the form is opened? Also, can I add a 'when-mouse-doubleclick' trigger to sort the date field in ascending and descending order (this is a client requirement)?
In a data block(it lists all the records created), I tried to search for the records which are created on a particular date. So I entered the date in query mode in that data block. But it is not returning any rows. But if I searched through other fields in that data block except that date data type it is returning the appropriate records.
Find the date difference. I need to find that how many days the task is pending, if ACT_NAME field switching from 'SET PENDING%' to 'RESUME PENDING%' by using ACTIONTAKENDATETEXT field in the History table.
Example as needed: NoPendingDays = 23 (8+15)
I have attached Create table and Insert table values sample as SQL file.
I have table named purchage with 2 columns (order_no number,order_date date) in my database. I want to load the data from a file into that table. The below is the file format
100,4/3/2013 1:18:18 AM 101,4/3/2013 1:18:18 AM 102,4/3/2013 1:18:18 AM 103,4/3/2013 1:18:18 AM 104,4/3/2013 1:18:18 AM 105,4/3/2013 1:18:18 AM 106,4/3/2013 1:18:18 AM
how to load the date filed along with the time stamp.
When working in MS Access, I can build parametric queries, where I can input the date field for example at run-time. How can I do something similar in SQL Oracle
Example
SELECT Sector.Date, Sector.RNC, Sector.Site, Sector.Cell,Sector.PSC FROM Sector WHERE Sector.Date=[Enter Date];
I have created a form with a Date From and Date To fields.
I am using the Date Picker format (the new one, not the Classic), and have set the calendar to Show on Both (Focus and Icon Click).
I would like to restrict the field so that the user cannot enter any text - they can only use the Date picker/calendar to choose a date. This would them remove the clunky validations - so I can restrict the users so they can't enter the wrong format in the first place.
declare i INTEGER:=0; flag boolean:= FALSE; vcInsert:='INSERT INTO CRM_DELT_IMPORT ('; if length(i_vcColumn1) > 0 then flag:=TRUE;
[code]....
i am fetching data from excel all data is of general type, here i am passing value is VTDATE varchar2(100) but actual database column filed is DATE , How can i pass '13-06-13 05:54:33' to database table.
if i put this condition vcInsert:=vcInsert|| ','||''''||i_ldata(i).to_char(VTDATE,'DD-MM-RR HH12:MI:SS') i get the error to_char must be declare.
if i remove this line
i am getting this error ORA-01843: not a valid month
I've started using Apex 4.2(new to Apex). I have built a form based on a table with a date column on it. The form allows update on all fields/columns of the base table. For the date field/column, the goal is to present the user with sysdate on load and update the date with sysdate if the record is changed any of the fields. How can I accomplish this?
When I run a query form the the Query Window in Visuial Studios 2012 all the date fields truncated to 'mm/dd/yyyy', but i need the full date returned. I am able to get full date from TO_char(MyDateField, 'yyyy-mm-dd hh24:mi:ss'), but if I do TO_DATE(MyDateField, 'yyyy-mm-dd hh24:mi:ss') it only returns 'mm/dd/yyyy'. I'm sure this is a simple setting in Visual studios but I cant find it to save my life. Is there there a way to have the full date returned by default?