SQL & PL/SQL :: Declare Date Variable And Assign It To Current System Date

Apr 19, 2011

Write a program to declare a date variable and assign it to the current system date. Depending on the day of the month the program should print the following:

If day is 1-10 then print "It is day<day number> of <month name>. It is early in the month".
If day is 11-20 then print "It is day<day number> of <month name>. It is the middle of the month".
If day is 21-31 then print "It is day<day number> of <month name>. It is nearly the end of the month".

For example, if the day is November 30, then print "It is day 30 of November. It is nearly the end of the month".

View 9 Replies


ADVERTISEMENT

SQL & PL/SQL :: Difference Between Sysdate / Current Date / Current And Local Timestamp

May 30, 2012

What is the difference between the following . In my schema all are giving the same results with some different format

SQL> SELECT sysdate , current_date , current_timestamp , localtimestamp from dual;

SYSDATE CURRENT_DATE CURRENT_TIMESTAMP LOCALTIMESTAMP
----------- ------------ ------------------------------------------------- -------------------------------------------------
5/30/2012 8 5/30/2012 8: 30-MAY-12 08.27.22.037703 AM -04:00 30-MAY-12 08.27.22.037703 AM

View 1 Replies View Related

SQL & PL/SQL :: Verify Current Date Is Greater Than 15th Of Current Month

Sep 22, 2010

I need to verify if the current date is grater than the 15th of the current month. If its grater than the 15th of the current month i need to do an action or if else its lesser than 15th of the current month i need to do an other operation.

View 5 Replies View Related

SQL & PL/SQL :: Assign The End Date

Dec 19, 2011

I had as source a set of actions where I have to assign the end date, the general rule is that each action has the same end date to start date of next action, if there is no next action it will be assigned the default('35001230').

I can do the assignment with the date of order:

NVL (LAG (DAT_INI) OVER (PARTITION BY ID_CLIENT DAT_INI ORDER BY DESC, DESCCOD_SEVERITY), TO_DATE ('35001230 ',' YYYYMMDD ')),

However, I have an exception to the rule, if the action have COD_SEVERITY = 3 or 4 and the next action have the COD_SEVERITY = 3 or 4, the end date will be for the previous day. If in the same day I have actions with COD_SEVERITY 3 and 4, the end date of actions with COD_SEVERITY = 3 will be DAT_INI -1 and the action with COD_SEVERITY = 4 will have the same date of the next action or DAT_INI -1 if the next action had COD_SEVERITY = 3 or 4

Input
ID_CLIENTDAT_INICOD_SEVERITYVALUE
1201112011100
1201112013100
1201112044101
1201112073100
[code].......

Output except
ID_CLIENTDAT_INICOD_SEVERITYVALUEDAT_END
120111201110020111201
120111201310020111203
120111204410120111206
120111207310020111213
[code]........

The source
SELECT 1 as ID_CLIENT, 20111201 as DAT_INI, 1 as COD_SEVERITY, 100 as VALUE FROM DUAL
UNION ALL
SELECT 1 as ID_CLIENT, 20111201 as DAT_INI, 3 as COD_SEVERITY, 100 as VALUE FROM DUAL
UNION ALL
SELECT 1 as ID_CLIENT, 20111204 as DAT_INI, 4 as COD_SEVERITY, 101 as VALUE FROM DUAL
UNION ALL
[code].......

View 1 Replies View Related

SQL & PL/SQL :: Display Date Ranges In One Column As Separate Date Periods (start And End Date) In Two?

Jun 1, 2010

I'm trying to work out how to take a table like this:

IDDate
12502-Feb-07
12516-Mar-07
12523-May-07
12524-May-07
12525-May-07
33302-Jan-09
33303-Jan-09
33304-Jan-09
33317-Mar-09

And display the data like this:

IDPeriodPeriod StartPeriod End
125102-Feb-0702-Feb-07
125216-Mar-0716-Mar-07
125323-May-0725-May-07
333102-Jan-0904-Jan-09
333217-Mar-0917-Mar-09

As you can see, it's split the entries into date ranges. If there is a 'lone' date, the 'period start' and the 'period end' are the same date.

View 13 Replies View Related

Forms :: Expiry Date To Automatically Show A Date 15 Years After Initial Date

Apr 12, 2010

I have a two date fields in my form; valid from date and expiry date.

Currently my valid from date has an inital value property of $$date$$ which automaitcally brings up todays date.

I need my expiry date to automatically show a date 15 years after this date?

View 8 Replies View Related

Get Current Date And Time?

Apr 24, 2005

I only know to retrieve data (date & time) by the following statement : "select sysdate from ..."

but now I want to make the fields current date and current time separately and put in a adult file with these two fields. how to do it.

View 11 Replies View Related

PL/SQL :: Current Quarter End Date

Jan 25, 2013

My below query is working fine with sysdate but when m passing a date it's throwing an error to get the current qtr end date..

select add_months(trunc('20-dec-2012','q'),3) - 1 from dualERROR

ERROR at line 1:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER

even after adding the to_date function between trunc and date.                             

View 5 Replies View Related

Find Records That Have Most Current Date?

Apr 13, 2004

I have two tables

table_1
--Emp_id--|--Sup_id--|Sup_name|--Date--|
-------------------------------------------------
--00001--|--00005 --|---ABCD--|01-MARCH-2004
--00002--|--00006 --|---BCDE--|02-MARCH-2004
--00003--|--00007 --|---CDEF--|03-MARCH-2004
--00001--|--00008 --|---DEFG--|04-APRIL-2004
--00003--|--00009 --|---EFGH--|05-APRIL-2004

table_2
--Emp_id--|Emp_name|
--------------------------------
--00001--|--QWER--|
--00002--|--ASDF--|
--00003--|--ZXCV--|
--00004--|--POIU--|

table_1 contain records on employee and the supervisor they are under at a certain date.

As some employee(00001 & 00003) have a different supervisor from different date, I'll like to extract from table_1 the record of each employee in the table that only contain the supervisor info on the most recent date.And from table_2, i'll like to extract the employee's name. These records extracted from both the tables would the be put into a new table,table_3

Example: For employee 00001, only extract record that have the most recent date which is 04-APRIL-2004 and not on 01-MARCH-2004

table_3
Emp_id|Emp_name|Sup_id|Sup_name|Date|
------------------------------------------------
00001 |--QWER--|00008 |--DEFG---|04-APRIL-2004
00002 |--ASDF-- |00006 |--BCDE---|02-MARCH-2004
00003 |--ZXCV-- |00009 |--EFGH---|05-APRIL-2004

How to write an SQL statement to perform this?

View 6 Replies View Related

SQL & PL/SQL :: Create Table With Current Date

Jul 22, 2010

How can we create a table with current date in oracle this is to be done daily a job as following today's table name is MYTABLE-2010-07-23

so every day a job is to create a table with tablename-current date

is this possible? and how to

View 11 Replies View Related

Display Data From Current Date To Next 15 Days?

Jan 24, 2007

I have a Employee data as below with EID and DOB

EID DOB
----------------------
1 03/01/1950
2 14/01/1950
3 27/01/1950
4 02/02/1950
5 04/03/1950
6 12/03/1950
7 28/03/1950
8 28/02/1951
9 08/03/1951
10 28/03/1951

I need a query to display the birth date of all emp in such a way that if i run a query today the dob which lies between today & today+15 days data must be displayed.

View 8 Replies View Related

SQL & PL/SQL :: Date - How To Get Past 12 Months From Current Month

Dec 17, 2011

I need to get the past 12 months from the current month

for example

input:

march2010

output:

apr2009
may2009
june2009
july2009
augest2009
september2009
october2009
november2009
december2009
january2010
february2010

View 23 Replies View Related

PL/SQL :: Date Is Greater Than Current Time Plus 24 Hours?

May 26, 2013

how to pull data from a table where date is greater than current time (+24 hours)... my date field is in the following format 15-MAR-2013 20:07:00

I want to do something like this

select * from table_A where date_field > (sys_date_time) +24h

as an example, when I run a query @ 4 PM on March 26, I want to pull data that has date > 4 PM March 27

View 6 Replies View Related

Reports & Discoverer :: Current Date Field

Sep 13, 2010

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?

View 3 Replies View Related

SQL & PL/SQL :: Display The Current Date With Post Fix Notation In Oracle?

Jul 2, 2013

Is there any way to display the current date with post fix notation in oracle?

Ex : 2nd ,5th , 3rd , 1st , 8th ,

select to_char(sysdate,'DD') from dual;

View 2 Replies View Related

JDeveloper, Java & XML :: Inserting Current Date From Node?

Mar 22, 2013

How can we insert current date which is present in xml node into a column having date datatype.

View 6 Replies View Related

PL/SQL :: Attribute In DB - Return Difference Between Start_date And Current Date

Feb 15, 2013

I have an attribute in my DB called start_date of type date.I want a query that return the difference between the start_date and the current date.for example if start_date = 2/14/2013 *2*:35:00 PM and the current date = 2/14/2013 *1*:35:00 PM the query returns 1.

I want the result in hours.

View 13 Replies View Related

SQL & PL/SQL :: Fetch System Date Using Function?

Oct 18, 2010

create or replace function get_date(nn date)
return date as age date;

BEGIN
age := sysdate;
END;

i want to return that value in front end using callable statament..

View 2 Replies View Related

Forms :: System Date Change

May 18, 2013

after change the system date form not working. (after enter value in a text field ,cursor not moving)

View 4 Replies View Related

Adding Time Part To Current Date To Form Timestamp

May 17, 2011

I have a column where I have time stamp with date and time(8-May-10 10:20:55 Am) from that i will get only time(10:20:55 Am)[query for this mentioned below] Now i need to append the time to current date and insert into a Timestamps column only .

so my result should be 17-May-11 10:20:55 Am(Assuming current Date is 17-May-11) . I tried search it by i found a way in sql server by using DataAdd and DataDiff .

query to get the time is:select to_char(to_date('11-MAY-11 05.00.00 PM', 'DD-Mon-YY HH:MI:SSAM'),'HH24:MI:SS') "Time Now" from dual

View 1 Replies View Related

SQL & PL/SQL :: Date Variable / IF Statement?

May 26, 2010

am new to oracle based coding and am having a heck of a time trying to figure this out. Basically i need to declare 2 date variables, Begin date and End Date.

I then need an if statement that says if begin date = 0 or is null then set it to the first day of the previous month. and if end date = 0 or is null then set it to the last day of the previous month.

So for today 5/26/2010 i would need

begin date = 4/1/2010
end date = 4/30/2010

I have searched and tried but so far to no avail.

View 10 Replies View Related

SQL & PL/SQL :: How To Store Value In Date Variable

Oct 17, 2012

i am having one query which is

select to_char(date '1900-01-01' + 1439/(24*60),'DD/MM/YYYY HH24:MI:ss')from dual;

when i am running this query i am getting this value: 01/01/1900 23:59:00 and this value i am inserting in one variable which has date datatype.here i am storing like this:

v_nshiftmax := to_char(date '1900-01-01' + 1439/(24*60),'DD/MM/YYYY HH24:MI:SS');

while storing getting error: not a valid month....how to store it in this variable?

View 3 Replies View Related

SQL & PL/SQL :: Read Date From String Variable

Jul 29, 2013

In our mainframe file we have date coming as format June 1,2013 . How to read and convert it in 01/06/2013 format to load in oracle table?

View 11 Replies View Related

PL/SQL :: Declare Variable As Row Type For A Table?

Jul 19, 2012

Can I declear a variable in PLSQL as the row type for a table, who's name is unknown during compile time, but will be determined when the PLSQL is runnning. The code is like following:

Procedure operTable( tableName IN VARCHAR2)
IS
TYPE ty_Row IS tableName%ROWTYPE
v_Row ty_Row;
CURSOR v_quey_cur

[code]...

View 5 Replies View Related

PL/SQL :: Declare Variable In Stored Procedure

Dec 21, 2012

how do you declare a variable in a store procedure

View 11 Replies View Related

SQL & PL/SQL :: How To Declare A Variable Which Reference Level Type

Aug 5, 2010

I have a problem :

In package head I declare a variable , which reference 'level' type , but I don't know how to declare the variable , because I don't know what type of 'level'.

View 9 Replies View Related

Client Tools :: PL/SQL Developer Debug Date / Time Variable

Oct 17, 2012

I have a little problem when debugging in pl/sql developer. All date variables are always displayed DD-MON-RR. I already changed the values for Date and Time to YYYY-MM-DD HH24:MI:SS in

Tools => Preferences => User Interface / NLS Options.

But when I start debugging and watch a variable I still get eg. 17-OCT-12 without the time. I already restarted pl/sql developer. No change.

View 4 Replies View Related

Forms :: How To Declare Global Variable And Call Same In Other Form

Oct 6, 2008

I want to call a value from one from to another form in d2k forms 4.5. I think this can be done only by declaring a global variable. I don't know how to declare global variable and also calling the same in other forms.

View 14 Replies View Related

SQL & PL/SQL :: How To Declare Global Variable In Package Body (Not Spec)

Feb 26, 2013

I want to know how we can declare a Global Variable in Package body(Not Spec), So that i can use it in any procedures or function(Defined in same package).

View 11 Replies View Related

Precompilers, OCI & OCCI :: Variable Declarations In DECLARE SECTION?

Mar 9, 2010

I am getting some odd results from a Database Insert Function. The function receives an Array of elements. The elements are a defined structure (containing around 21 data items). I need to insert these records into an Oracle table.For each element of the Array the function reads the structure into a local c structure of the same type.

I then go through this local structure and get a copy of the data into local variables declared in the EXEC SQL BEGIN DECLARE SECTION of the function. I then use the local vars to do the insert, using null INDICATOR variable to handle those variables that could be empty. The local variables look like this....

EXEC SQL BEGIN DECLARE SECTION;
int dbServiceTypeId;
int dbRecordType;
char dbRbmCustRef[MAX_CUST_REF_LEN];
int dbServiceSeq;

[code]...

BUT.. when I uncomment the dbCOS variable (even though I don't populate it or try to include it in the insert) I get the following in the table (The RATE value goes missing completely and the multiplier is wrong)...

ID SERVICE CODE R/T RATE MULTI
41325-SCODE-1084 1 542139762
11326-SCODE-1086 1 542139762
11326-SCODE-1086 2 542139762
21327-SCODE-1087 1 542139762
21327-SCODE-1087 2 542139762
21327-SCODE-1087 3 542139762
21327-SCODE-1087 3 542139762

However, a printf statement just before the insert based that returns the variables shows the following...

Service Type ID: '4'
Record Type ID: '1'
Service Cust Ref: '1325-SCODE-1084'
Service Unit Rate: '1200.00'

Indeed, for this record the Multiplier doesn't even get populated. The other odd thing is if I recomment the dbCOS but remove the dbOraDateFmt variable definitions, it corrupts the data again, though different fields. I can't understand why individual local variables are behaving this way. Is this a problem with the way variables are declared in this section?

View 3 Replies View Related







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