PL/SQL :: To Find Leap Year?

Aug 23, 2012

How to find given year is leap year or not, if leap year i want the feb month no : of days.

View 5 Replies


ADVERTISEMENT

SQL & PL/SQL :: Find Out Date Difference In Day-month-year?

Jul 8, 2010

i want to find out difference between two dates in day-month-year. How can i do it?

For example how many days,months are years are between '01-jul-1979' and '08-jul-2010'

View 4 Replies View Related

SQL & PL/SQL :: Find Missing Date For A Year Excluding Saturday And Sunday

Feb 3, 2011

I given the table name,column name,datatype and sample record in the table. I have given the sample record for 01-jan-2008 to 8-Jan-2008, but in the real thing it will be for 30 years.

My Requirement:

For each class_no (202,203..), I need the missing date excluding weekends (sat, sun), I have provided the sample output below.

Table Name : ABC

Column Name : Class_no Data Type : Number
Column Name : Class_DateData Type : Date

Sample Record in the Table :

Class_noClass_Date
202 1-Jan-08
202 2-Jan-08
202 7-Jan-08
202 8-Jan-08
203 1-Jan-08
203 2-Jan-08
203 3-Jan-08
203 7-Jan-08
203 8-Jan-08

OUTPUT:

Class_noClass_Date
202 3-Jan-08
202 4-Jan-08
203 4-Jan-08

View 5 Replies View Related

SQL & PL/SQL :: Same Day At Last Year

Apr 20, 2011

my propble is supose if today is Tuesday 01�32011 which is week 16 of this year i need to get same day tuesday on week 16 of last year

View 12 Replies View Related

To_date - Using Just Dd / Mm And Not Year

Jun 18, 2008

i am sure it may be wrong because it doesn't mention year however i have been told to get a list of all students who enrolled in MAY. and i have tried doing

select * from table where enrolDate > to_date('01-05','DD-MM') and enrolDate < to_date('31-05','DD-MM') and it doesnt work (says no rows selected when in fact there are some rows!

i can do it fine when using YEAR ie '01-05-2000','DD-MM-YYYY' another problem.

I am trying to do this:

Select to_char(to_date(01012008,DD-MM- YYYY),DAY) from dual;

and it is giving me the error however if i do this:

Select to_char(to_date(16012008,DD-MM-YYYY),DAY) from dual;

View 3 Replies View Related

SQL & PL/SQL :: Date Without Year

Sep 27, 2010

I got a table with a date-typed column called: "Birth_date", and I wanna write a function that retrieves all the records that "got a birthday" this week.

in order to check weather a record got a birthday this week I need to check only the day & month of "Birth_date" that BETWEEN (sysdate-7) AND (sysdate),

but I don't know how..

View 39 Replies View Related

Day Wise Count For A Given Year

Jul 2, 2011

In oracle query can i want find out how many day wise count for a year days (for example how may sundays, mondays, tuesdays, wednesdays ,thursdays,fridays,saturdays) in a given year (we can give the start day of the year and the end day of a year).

example
----------
jan sun-5 mon-4 tue-5 wed-5 thu-5 fri-4 sat-5
feb ------------do---------------------------------
like this for all 12 months at a single query.

View 3 Replies View Related

Changing Year Format

Nov 23, 2009

I have a table in which years are stored in the form '2008/2009'. This is making it very difficult for me to do any calculations on that field and so I was wondering if there was a way to change the years (in a query and not in the actual table) so that if the year was '2008/2009' I would have just '2009'.

View 4 Replies View Related

SQL & PL/SQL :: Start And End Date Of The Year

Mar 1, 2012

I have a sql like this

select inv_dtime where inv_dtime between trunc(sysdate,'YYYY') and last_day(trunc(sysdate,'YYYY')) from temp;

I want to have the start date of the year and end date of the year in my condition. like between 01-JAN-2012 AND 31-DEC-2012. I tried the above but it doesn't come.

View 4 Replies View Related

SQL & PL/SQL :: Why To_Date Not Returning Whole Year

Jan 16, 2011

I run following query.

select to_date(sysdate,'DD/MM/YYYY') Dte FROM DUAL

Result is

Dte
--------
01/17/0011

why it is not returning '01-17-2011'?

View 3 Replies View Related

SQL & PL/SQL :: Max And Min Values Of Month And Year

Jun 25, 2010

can we take the maximum and minimum value of month and year

View 7 Replies View Related

SQL & PL/SQL :: Count For Null Year

Feb 24, 2010

with test as

( select 0001 item_code, to_date(1997,'yyyy') Yr from dual union all
select 0002 , to_date(1998,'yyyy') from dual union all
select 0003 , to_date(1999,'yyyy') from dual union all
select 0004 , null from dual union all
select 0005 , null from dual)
select count(Yr) from test group by yr

COUNT(YR)

1
1
1
0

But I want the completed year count and non completed year count
The non completed year count should be 2 as there are two null
How I would acheive?

==============

View 3 Replies View Related

SQL & PL/SQL :: Year And Month Query

Mar 20, 2011

I have two Queries.

Query 1:
SELECT DISTINCT YR FROM(
SELECT TO_CHAR( ADD_MONTHS (TRUNC (TO_DATE('01/01/2007' ,'DD/MM/YYYY'), 'YYYY'), 1*LEVEL -1) , 'YYYY')
YR FROM Dual
CONNECT BY LEVEL <= MONTHS_BETWEEN(TO_DATE(:to_dt ,'DD/MM/YYYY'), TO_DATE('01/01/2007' ,'DD/MM/YYYY')) + 1
ORDER BY YR
)

Gives the Output as
YR
****
2007
2008
2009
2010
2011

Query 2
*******
SELECT DISTINCT MONTH FROM(
SELECT TO_CHAR( ADD_MONTHS (TRUNC (TO_DATE('01/01/2007' ,'DD/MM/YYYY'), 'MM'), 1*LEVEL -1) , 'MM')
MONTH FROM Dual
CONNECT BY LEVEL <= MONTHS_BETWEEN(TO_DATE(:to_dt ,'DD/MM/YYYY'), TO_DATE('01/01/2007' ,'DD/MM/YYYY')) + 1
ORDER BY MONTH
)
Gives the Output as

MONTH
***
01
02
03
04
05
06
07
08
09
10
11
12

I want to combine these two. I need the output as

2007-01
2007-02
2007-03
....
...

View 8 Replies View Related

PL/SQL :: How To Get Monday Of Every IW Week In A Year

Jun 29, 2012

I had to get a query to give me all the Mondays of a IW week in a year. I think as per the IW weeks there are around 52.1 weeks in a year. So basically, I want the starting Monday of every week. I would then store those values in a variable and use them in APEX as column headers. Online I saw some samples not related with this, but where looping was done using the all_objects. Not sure if that is the right approach.

The problem is since this is not stored in any table, how can I loop like 52/53 times such that the output shows all the Mondays (date format) for a given year. Basically, the output would be 52/53 rows with a date(Monday of the week).

View 10 Replies View Related

Interval Year To Month Parameter

Mar 20, 2008

I have a procedure that has a 'INTERVAL YEAR TO MONTH' parameter. What value do I pass to this parameter?

View 1 Replies View Related

SQL & PL/SQL :: Get Date From Year / Week And Weekday

Apr 5, 2011

How to get a date using Year (say 2009), Week (35) and Weekday (5). I have read only permission to database, so I can not create a function.

View 8 Replies View Related

SQL & PL/SQL :: Financial Year Wise Grouping?

Feb 20, 2012

I need to query by fiscal year wise.

For example

1-Apr-2010 to 31-Mar-2011

View 11 Replies View Related

SQL & PL/SQL :: Year To Date Value In Amount Field

Oct 18, 2010

I have a table and below are the rows:

create table employee_rev(employee_id number, month_name varchar2(10), month_end date, financial_year date, revenue_amt number)

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.

View 7 Replies View Related

SQL & PL/SQL :: De-Aggregate Year To Date Figures?

Apr 27, 2011

I have a table as below:

Year Month Value
2011 01 15
2011 02 26
2011 03 34

[code]...

The value is an aggregate Year to Date Figure And I was wondering what the best method of splitting this data out into a Monthly Figure so that it would look like below:

Year Month Mth Value
2011 01 15
2011 02 11
2011 03 8
2011 04 9

[code]...

View 9 Replies View Related

SQL & PL/SQL :: Get First Week Starting With First SUNDAY Of Year

Nov 9, 2012

I want to get the week of the year.

Conditions are:

1. Year's first week starts with first Sunday of the year. (6th Jan 2013 will be the starting week (week 1) in Year 2013)
2. 2nd Jan 2013 will be the last week of the previous year i.e. 2012 (either 52th or 53rd week)

At many place I found the below solution:

select to_char(to_date('01-JAN-2008','DD-MON-YYYY')+1,'IW') week_number from dual;

But its not working for the given dates (2nd Jan 2013, which should fall in the last week of 2012, and 6th Jan 2013, which should be the starting week of 2013).

View 6 Replies View Related

SQL & PL/SQL :: Compare The Sales With The Same Month Last Year

Jul 6, 2012

I wanted to compare the sales with the same month last year. But the issue is only one value on each customers.

File Attached Table as JPG file..
-----------------------------------------------------------------------
SELECT WORDERS.BP_ORDER,
(SELECT SUM (WINVITEMS.ITEM_VAL)
FROM WINVITEMS
INNER JOIN WORDERS ON WORDERS.ORD_NO = WINVITEMS.ORD_NO
WHERE WORDERS.DATE_ORDER >= TRUNC(ADD_MONTHS(SYSDATE, -3),'MM')

[Code]....

View 7 Replies View Related

SQL & PL/SQL :: Finding List Of Second And Forth Saturday In Particular Year?

Jun 30, 2011

i have one query. i want to Find the list of second and forth Saturday in a particular year.

View 6 Replies View Related

SQL & PL/SQL :: Parameter For Date - Quarter Year

Jan 26, 2013

IF THE USER ENTER parameter for date(yymm) in a report as 201301

select vndr#,sum(net_sales_value) from mnthly_sales_value where vndr# = 111 and yymm = :yymm group by vndr#;

but I need result of 3 months, but in my table data is stored for one month so how to get 3 months then again 3 months so on for Q1,Q2,Q3,Q4

yymm between 201301 and 201303 and
yymm between 201304 and 201306 and
yymm between 201307 and 201309 and
yymm between 201310 and 201312
in the same query

what i have to do for this

View 3 Replies View Related

PL/SQL :: GL_Cancelled_Date Displaying Incorrect Year?

Sep 24, 2013

I currently have around 560 rows of bad data where

GL_Encumbered_Date and TO_CHAR(PRD.GL_ENCUMBERED_DATE,'YYYY'  display correctly.

The problem lies in

PRD.GL_CANCELLED_DATE which displays correctly (example: 30-APR-10) but TO_CHAR(PRD.GL_CANCELLED_DATE, 'DD-MON-YYYY') for the same row displays as 30-APR-0010. 

I have separated out the incorrect year with:

to_char(prd.gl_cancelled_date,'YYYY') where each of the 560 rows display 0010 instead of 2010. I am unsure if arithmetic with dates is allowed within SQL- but am curious if the operation date + number will result in a date.

If this is so, how could one go about taking 0010 as a date and add 2000 to create 2010 for: PRD.GL_CANCELLED_DATE.

View 2 Replies View Related

SQL & PL/SQL :: How To Sort Date By Month (JAN) And Year (2009)

Jun 4, 2010

I've done this once before, but can't seem to find the sql.

How can I sort by month and year on a column called ex: TEST_dATE

JAN 2007
FEB 2007
APR 2008
SEP 2009
OCT 2009
FEB 2010
JUN 2010

View 15 Replies View Related

SQL & PL/SQL :: Create Procedure Have Month And Year As Argument

Aug 5, 2010

Need to create a procedure that will retrun me monthly count of records

create table sample ( S_name varchar2(20), S_Date Date);

insert into sample values('1','01-JAN-2005');
insert into sample values('1','1-JAN-2006');
insert into sample values('2','2-JAN-2007');
insert into sample values('3','4-JAN-2005');
insert into sample values('4','11-JAN-2004');
insert into sample values('3','2-JAN-2005');
insert into sample values('2','12-JAN-2006');
insert into sample values('1','21-JAN-2005');
insert into sample values('2','11-JAN-2005');
insert into sample values('7','01-JAN-2005');
[code]....

View 5 Replies View Related

SQL & PL/SQL :: Convert A String In Date Obtaining Just A Year?

Nov 12, 2012

basically I have to insert in a date field a string that is a year(four characters).

The problem is that I have to convert a string in date obtaining just a year. How can i do this?

View 14 Replies View Related

SQL & PL/SQL :: Prompt To Pull Year Worth Of Data

Dec 30, 2011

I am writing a query where I'd like to pull one year's worth of data. Ideally I want to prompt for the END DATE and have the query go back in time one year from that date.

Here is what I've got after doing some research online... but It's not quite working for me.

select *
from mrtcustomer.profile
where reg_type = 'B'
and contact_type = 0
and active_ind = 'Y'
[code].....

View 4 Replies View Related

SQL & PL/SQL :: How To Sort Query Based On Financial Year

Jun 12, 2012

Haw to sort the data based on financial year.

For example Indain Fiscal year 01-Apr-2010 to 31-Mar-2011

Apr2010 -1
May2010 -2
.
.
.
.
Feb2011-11
Mar2011-12

I had given a query..for Quarter idendification

SELECT distinct
'Qtr'
|| CASE
WHEN PERIOD BETWEEN TO_DATE ('01-04-2010', 'DD-MM-YYYY')
AND ADD_MONTHS (TO_DATE ('01-04-2010', 'DD-MM-YYYY')-1, 3)

[code].....

View 2 Replies View Related

SQL & PL/SQL :: Display Week Number As Per Financial Year (Apr - Mar)

Dec 9, 2012

I hava an requirement to get week number from particular date as per indian financial year(ie apr-01 to mar-31).I have tried with to_char(the_date_field,'w') and to_char(the_date_field,'iw') formats but I know its only shows the iso standard.

But I want the weeknumber 1 has to be started from april-01 not from jan-01.For more explanation see below,

Date week_no
apr 01 to 07 1
apr 08 to 14 2
apr 15 to 21 3
.
.
.
.
.
Next year mar - 31 n

how to do this am waiting for your reply.

View 4 Replies View Related







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