SQL & PL/SQL :: Days To Years Converter

May 11, 2012

Our "client" is delivering data that contains an "age" field. However, they've put the AGE in DAYS instead of years, while our system can only work with years. So I needed to convert the age to years.

We are getting 2 parameters: LOWAGE and HIGHAGE (both in days). However, for the same "test" (laboratory test), we are also getting some other records

eg:

TEST 1 lowage = 0 upperage = 5 OTHER values = xxx
TEST 1 lowage = 6 upperage = 100 OTHER values = yyy

So if the person would be 5 years or less, the values xxx would apply. If they are 6 years and older, the yyy values apply. Now, the problem is that we are not getting this data in years, but in days. And whichever function I try, the upperage from the first test also seems to be equal to the lowerage from the second test. And this gives an error, because both would apply to this person....

I've tried stuff like:

age/365
age/365.25 (leap years)
trunc(months_between(sysdate+ranges.lowerage,sysdate)/12)

View 6 Replies


ADVERTISEMENT

Server Administration :: Overlapping Days Between 3 Date Ranges To Determine Active / Inactive Days

Sep 2, 2011

I have installed Oracle Database 11g.2 by database configuration assistant on windows XP as and adminstrator on my laptop(no connection to network),but when I want to create database I face this warning: error securing database control ,Datatbase control has been brought up in non-secure mode . to secure the database conntrol execute following command....(error is attached).

View 7 Replies View Related

SQL & PL/SQL :: How To Convert Days Into Weeks And Days (if Any) Oracle 9i

Apr 5, 2011

want to write Procedure to return weeks and days from given set of days.

let suppose we have 72 days to return weeks and days then return should be 7 weeks and 2 days.Can i use date function Or ?

View 5 Replies View Related

SQL & PL/SQL :: Number Of Years Calculation

Mar 16, 2012

for making the query for following data,when we give start and end date then query need to calculate number of years.

if less than one year then return 0
if exact one year then return 1
if exact 1.5 years (18 months) then return 2
if exact 2 years (24 months) then also return 2
if exact 2.5 years (30 months) then return 3
if exact 3 years (36 months) then also return 3
if exact 3.5 years (42 months) then return 4
if exact 4 years (48 months) then also return 4
and so.

also we need to add leap year 1 day if exist in start and end date.

YearSalary
11
1.52
22
2.53
33
3.54
44
4.55
55
[code]....

View 15 Replies View Related

PL/SQL :: Query To Get List Of Years?

Feb 15, 2013

I am trying to generate a list of years, starting with 2010 and counting up through 5 years from the current date. For example, if it is currently 2013, the list would include 2010 through 2018. 2 years from now, I want the list to go from 2010 through 2020.

I am currently using

SELECT to_char(add_months(to_date('2010','YYYY'),12 * (rownum-1)),'YYYY')
FROM dual
CONNECT BY level <= 10to get a list with 10 entries.

How can I make this dynamic enough to return a variable number of records depending on the current year?

View 1 Replies View Related

SQL & PL/SQL :: Find Years Between Any Given Date Range

Apr 5, 2011

I require to find the years between any given date range. For example what are the years between the dates '01/12/2010' and '01/02/2012'? Answer must be '2010,2011,2012'. how to code the query for this result?

View 2 Replies View Related

SQL & PL/SQL :: Chinese Zodiac Associated With Birth Years

Aug 8, 2010

I have a question to write a pl/sql for Chinese zodiac but i cant do it, even i Google it i cant find any solution..the question is as below:

The Chinese zodiac associates birth years with the following; animals:

Birth Year Animal
1924,1936.1948,1960,1972,1984,1996 Rat
1925,1937,1949,1961,1973,1985,1997 Cow
1926,1938,1950,1962,1974,1986,1998 Tiger
1927,1939,1951,1963,1975,1987,1999 Rabbit
1928,1940,1952.1964,1976,1988,2000 Dragon
1929,1941,1953,1965,1977,1989,2001 Snake
1930,1942,1954,1966.1978,1990,2002 Horse
1931,1943,1955.1967,1979,1991,2003 Sheep
1932,1944,1956,1968,1980,1992,2004 Monkey
1933,1945,1957,1969,1981,1993,2005 Chicken
1934.1946,1958,1970,1982,1994,2006 Dog
1935,1947,1959,1971,1983,1995,2007 Pig

Write a command to declare a date variable named birth_date, and assign to it your birth date. Use an IF/ELS1F structure to test every year and determine the animal associated with your birth year. Then display your birth year and the associated animal name. For example, the program would display the following output for someone born in 1984:

I was born in 1984, which is the year of the Rat. Declare and use additional variables as needed.

View 18 Replies View Related

PL/SQL :: Convert A Number To Years And Months

Jul 30, 2012

I would like to convert a number to years & months.

e.g. 119 = 9 years and 11 months

This would be displayed as 9.11

Is there an oracle function or sql that can calculate this value,

View 8 Replies View Related

Forms :: Setting A Date 18 Years In Future?

Mar 26, 2011

I am trying to set a item to system date + 18 years. What type of formula or where to put such a thing. i tried putting- select add_months(sysdate,216) from dual; into the calculation-formula in property palette with no success.

View 1 Replies View Related

SQL & PL/SQL :: Query To Find User Who Did Not Login From Past 3 Years?

Nov 2, 2012

I have two table APPLICATION_1 and APPLICATION_2, where user's login date is captured.There will be multiple records for each user.Same user may or may not be present in both tables.

The requirement is to find those users who have not logged-in in the last 3 years.

-- Test Case

DROP TABLE application_1;
DROP TABLE application_2;
CREATE TABLE application_1
(
seq NUMBER ,
user_id VARCHAR2(30),

[code].....

Using the below query I'm able to get the desired output.

SELECT user_id, MAX(login_date) last_login_date FROM (
SELECT user_id, MAX(login_date) login_date FROM application_1 GROUP BY user_id
UNION ALL
SELECT user_id, MAX(login_date) login_date FROM application_2 GROUP BY user_id
) GROUP BY user_id

[code].....

View 10 Replies View Related

SQL & PL/SQL :: Calculate Balance For Last Two Cooperative Years On Input Date

Aug 24, 2012

I need to calcuate balance for last two cooprative years on input date.

example

if input date is-->"01-AUG-2012" if (month >=7) -->then i need to calculate from 01-july-2010 to 30-june-2011 and 01-july-2011 to 30-june-2012 and 01-july-2012 to input date.

if input date is-->"01-june-2012" if (month<7) -->then i need to calculate from 01-july-2009 to 30-june-2010 and 01-july-2010 to 30-june-2011 and 01-july-2011 to input date.

how to achieve this logic

View 4 Replies View Related

PL/SQL :: Set Boolean Flag To True If Hire_date Is Greater Than 5 Years

Dec 27, 2012

For Just learning purpose This is an example found in text book but while i try to execute it fails..I am trying to set Boolean flag to true if the hire_date is greater than 5 years otherwise boolean flag to false

DELARE
v_Hire_date date :='12-Dec-2005';
v_five_years BOOLEAN;
BEGIN
IF
[code].....

View 17 Replies View Related

Pivot Query In Oracle To Get Years From Column And Make Separate Column For Each

Jul 22, 2009

I'm trying to do a pivot query in oracle to get the years from a column and make a separate column for each. I found an example of the code to use on the internet and i changed it for my own tables but i'm getting errors. Namely a "FROM keyword not where expected" error at the beginning of the 'avg(...' statements.

I have copied the code used in

select stud_id, 2006, 2007, 2008, 2009
from (
select stud_id,
avg(case when year=2006 then ((present/poss)*100) else null end) 2006,
avg(case when year=2007 then ((present/poss)*100) else null end) 2007,
avg(case when year=2008 then ((present/poss)*100) else null end) 2008,
avg(case when year=2009 then ((present/poss)*100) else null end) 2009
from attendance.vw_all_attendance_perc
group by stud_id
);

View 11 Replies View Related

Date Arithmetic - Formula To Subtract Years And Give Date

Sep 27, 2007

I am a novice in oracle

I have 2 columns in my table

->Col1 with experience in years entered as an integer
->Col2 with current date

I need to add another column as a date value adn for that i need to subtract Currentdate-Col1 when i tried currentdate-Col1 it just subtracted the days i need the formula to subtract years and give a date

I have worked in DB2 and all u need to do there was add the keyword years at the end but in oracle the same does not work

View 8 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

SQL & PL/SQL :: Get Consecutive Days?

Oct 18, 2010

I need to know if a customer appears at least 4 consecutive days. Here's an example of data

CLI_ID , DATE
-------------------------
123 , 2010/10/01
123 , 2010/10/04
123 , 2010/10/05
123 , 2010/10/06
123 , 2010/10/07
123 , 2010/10/08
123 , 2010/10/10

456 , 2010/10/01
456 , 2010/10/02
456 , 2010/10/03
456 , 2010/10/06
456 , 2010/10/07
456 , 2010/10/08
456 , 2010/10/11

In the example the client 123 appears from 2010/10/04 to 2010/10//08 (5 consecutive days), so this client must appear in the output. In the example customer 456 does not appear at least 4 consecutive days, so should not appear in the output.

View 12 Replies View Related

SQL & PL/SQL :: Days Between Dates

Mar 14, 2012

I have not defined the table ( I only have privileges to query data).

I am unable to copy and paste my real code here, and the actual results from the run, as my company will fire me if I do so... so here is how things approximately look like (tried to keep it as real as possible).

Let's say that the table CYCLE has client numbers (clientid), cycle number (cycleno), date of visit (visdt).

I am trying to create a query to calculate how many days there are between each two consecutive visits/cycles for a single client(let's say 1200004)

clientid / cycleno / visdt
---------------------------
1200004 / 1 / 10OCT2011
1200004 / 2 / 31OCT2011
1200004 / 3 / 21NOV2011
1200004 / 4 / 05DEC2011
1200004 / 5 / 03JAN2012
...
1000005 / 1 / 04NOV2011
1000005 / 2 / 03DEC2011
1200004 / 1 / 10JAN2012
1200004 / 2 / 15FEB2012
.
.
.

The code below is the only one that kind of seemed to work, but it is definitely not giving me the right results.

SELECT cycleno1, visdt1, cycleno2, visdt2, to_date(visdt1) - to_date(visdt2) days

FROM (SELECT clientid, cycleno cycleno1, visdt visdt1,
LEAD (visdt, 1) OVER (ORDER BY cycleno) visdt2
FROM CYCLE) a

[Code]....

I am getting a mess of a result of the kind:

cycleno1 / visdt1 / cycleno2 / visdt2 / days
--------------------------------------------
1 / 10OCT2011 / 1/ 18OCT2011 / -8
1 / 10OCT2011 / 2/ 18OCT2011 / -8
1 / 10OCT2011 / 3/ 18OCT2011 / -8
1 / 10OCT2011 / 4/ 18OCT2011 / -8
1 / 10OCT2011 / 5/ 18OCT2011 / -8

I need my result to look like:

cycleno1 / visdt1 / cycleno2 / visdt2 / days
--------------------------------------------
1 / 10OCT2011 / 2/ 31OCT2011 / 21
2 / 31OCT2011 / 3/ 21NOV2011 / 22
3 / 21NOV2011 / 4/ 05DEC2011 / 15
4 / 05DEC2011 / 5/ 03JAN2012 / 30
5 / 03JAN2012 / / /
.
.
.

View 3 Replies View Related

Number Of Days Between Dates?

Jan 14, 2008

What I need to do is take 2 dates from a table named 'projects' and insert the number of days between them into a table named 'time_record'.How do I go about this?

View 1 Replies View Related

SQL & PL/SQL :: How To Select Last 10 Days Rows

Aug 2, 2012

My Table structure is

SL# Start_ts
1 7/31/2012 3:53:42.864070 AM
2 7/26/2012 2:13:58.280928 PM
3 7/25/2012 2:13:41.692569 PM
4 7/21/2012 2:13:26.821070 PM
5 7/18/2012 2:13:07.827278 PM
6 7/13/2012 2:05:35.023520 PM

Question

How to select last 10 days rows only (from sysdate)

Error

1) SQL> select * from Test where to_date(start_ts, 'DD-MON-YYYY HH24:MI:SS') > to_date(sysdate, 'DD-MON-YYYY HH24:MI:SS')-10 ; (or)

2) SQL> select * from Test where to_date(start_ts, 'MM/DD/YYYY HH24:MI:SS') > to_date(sysdate, 'MM/DD/YYYY HH24:MI:SS')-10 ;
ORA-01858: a non-numeric character was found where a numeric was expected

[Code]...

View 5 Replies View Related

SQL & PL/SQL :: Select Employee According To Days

Jun 8, 2010

i have an employee table where there is a column called join date. Now i have to select the employee according to the days. Means how many employee joined on Monday/Tuesday etc.

View 17 Replies View Related

SQL & PL/SQL :: Convert Days To Months

Nov 21, 2011

I have the following in my select statement and I am getting the days like 1130, 50, 60 etc

round(MAX (TO_DATE ('31-OCT-2011 23:59:59','dd-mon-yyyy hh24:mi:ss') - a.tx_dtime),0) DAYS

Now I want this to convert these days into months like
1.10 this denotes (one month 10 days)
1.25 this denotes (one month 25 days)
2.05 this denotes (two months 5 days)

I know I am using old Oracle but what to do my company is not spending to upgrade.

View 6 Replies View Related

SQL & PL/SQL :: Days Wise Separation

Nov 2, 2010

i want create a report like this

location sysdate refdate No_of_days SL1(0-90) SL2(91-180) SL3(>180)

So when no. of days between (0-90) data put in SL1
And when no. of days between (91-180) data put in sl2

I want to achieve this only in single query.

View 11 Replies View Related

PL/SQL :: To Find Months And Days Between 2?

Oct 9, 2013

 I want to find the months and days between 2 dates. For Eg. Date-1 : 25-Aug-2013Date-2 : 23-Oct-2013 If we consider every month as 30 days it should give 25-Aug-2013 to 30-Aug-2013 = 6 days 01-Sep-2013 to 30-Sep-2013 = 1 Month 23-Oct-2013 to 30-Oct-2013 =   8 days Total = 1 month and 14 days

View 33 Replies View Related

Generate Last Seven Days Batch Run Times

Jan 8, 2013

I would the query to do the following:

1. Make the jobname distinct, because it keeps giving me multiple entries for each jobname
2. Add the the start_time of SOD_start_data9_UAT1 to end_time fodba_MUAT1 to get the combined duration
3. CONCAT jobnames SOD_start_data9_UAT1 and end_time fodba_MUAT1
4. Generate the last seven days batch run times
5. Generate a report into .csv format and email out
6. I have access to sqlplus and plsql developer

select distinct JOBNAME, schedtab
, to_char(to_timestamp(trim(timestmp)
, 'YYYYMMDDHH24MISS') - numtodsinterval(elaptime / 100
, 'SECOND'), 'YYYY-MM-DD HH24:MI:SS') as start_time
[code]...

View 3 Replies View Related

SQL & PL/SQL :: Oracle Query For Days Output

Aug 5, 2010

I want to pull out the output only when Day is 1 and 2 and 3. I don't want if either Day is 1 or Day is 2 or Day is 3 is present.

Test Case

--Creat Table
create table dummy_name
(name varchar2(5) not null,
day number(3) not null);

--Insert Values
insert into dummy_name values ('A', 1);
insert into dummy_name values ('B', 2);
insert into dummy_name values ('C', 3);
insert into dummy_name values ('B', 1);
insert into dummy_name values ('B', 2);
insert into dummy_name values ('C', 1);
[code]...

View 8 Replies View Related

SQL & PL/SQL :: Month Wise Days For Between Given Dates?

Apr 28, 2010

I have a table with starting date and ending date.

I want to fetch monthwise days for given two dates

IE starting 25/12/2009 ending 25/03/2010

Result should like below.

Dec-09 7
Jan-10 31
Feb-10 28
Mar-10 25

I have tried this but it is not giving me the result which want..

select to_char(thedate,'mon-yy') mnth,count(to_char(thedate,'mon-yy')) days from
(SELECT TRUNC(to_date('25/12/2009','dd/mm/yyyy'),'Y')+ROWNUM-1 THEDATE
FROM ( SELECT 1 just_a_column
FROM dual
CONNECT BY LEVEL <= 366

[code].....

View 3 Replies View Related

SQL & PL/SQL :: Days Difference - Start And End Date

Jul 7, 2012

Tables
--------
ROWID START_DATEEND_DATE
101/26/2012 00:00:0001/31/2012 00:00:00
201/26/2012 00:00:0002/02/2012 00:00:00
302/03/2012 00:00:0003/31/2012 00:00:00

How to find out the days difference in sql query, start date of next records and end date of previous records as highlighted on blue color

query out put should give as below

start_date + next record ,end_date - Previous records should give the difference of
6
1

View 12 Replies View Related

SQL & PL/SQL :: Records For Next 3 Days From Access Time

Jan 18, 2012

I have a table like this below one.

CCCID MCODECNODDEADLINEDATEDDEADLINETIME
IN00100213201201219/01/20121050
IN00100213201201118/01/20121050
IN00100213201201320/01/20121050
IN00100213111219821/01/20121050

I need a query which will fetch only 3 rows for a day. For eg if today is 18-jan-2012, then i have to show rows whose deadline date is 18-jan-2011, 19-jan-2011 and 20-jan-2011. And on 18-Jan-2011, after 10.50 a.m it should not consider deadline date 18-jan-2011, instead it show deadline date is 21-jan-2011.

This deadlinedate will not have all dates, only exchange working dates, so at one time we should show only the probable 3 deadline dates..

View 10 Replies View Related

SQL & PL/SQL :: How Many Days Thursday And Friday Between Two Dates

Nov 13, 2011

I want function return me how many days Thursday and Friday between two dates.

View 1 Replies View Related

SQL & PL/SQL :: Extract Absent Days From The Month

Dec 29, 2012

i need to find the number of days employee was absent for the month, we are maintaining a table of attendance , where the daily data of hours is captured, what i need now is to capture total number of days he was absent or not present.How can i get the sum of absent days per employee based on given month, as the difficulty is i have to consider 30 days as standard working days for the month , regardless of months having either 30 days (April,June,September,November),or 31(January,March,May,July,August,December) or 28(Consider leap year also february) days.For eg ,If he is absent for 31 days in december then it should consider only 30 days as absent or if he is absent for 28 days in february still it should consider 30 Days as absent.

create table ot_job_det (jt_txn varchar2(12),jt_no number,jt_dt date ,jt_emp_code varchar2(6),jt_duration number )
delete from ot_job_det

[Code].....

I managed to do this with the given query , but this will check only for 31 days and 30 how to consider the february month or any leap year.

SELECT JT_EMP_CODE,DECODE(SIGN(30-COUNT(JT_DT)),-1,30,30-COUNT(JT_DT)) a FROM
OT_JOB_DET WHERE
TO_CHAR(JT_DT,'YYYYMM')='201212'
GROUP BY JT_EMP_CODE;

View 16 Replies View Related







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