select data with same range of period

What is the error message please?

Always include error code(s)/message(s) if hitting an error

it stands on the last raw and release this error :

ORA-00920: invalid relational operator

Select allOpen in new window

Just a hint.. could be one of the reasons for the error...prior join and conversion.. check whether both the date conditions are in the same format.. if not use to_char or to_date appropriately..

extract(year from to_date(column_name,'YY-MM-DD')

select b.tran_no,b.doc_date,a.tran_no2,a.tran_no,A.DOC_DATE,(extract(year from A.DOC_DATE) ) as yr,(extract(month from A.DOC_DATE) ) as mn
from gen_ledgers b,master_gen_ledger a
where
a.tran_no = b.tran_no
and (extract(year from to_char(A.DOC_DATE,'DD-MM-YY')) =  (extract(year from to_char(b.DOC_DATE,'DD-MM-YY')));

if you still encounter the error.. do post the error as suggested

I don't see what would cause that error from the query provided. However I would suggest this as te starting point:

SELECT
           b.tran_no
	, b.doc_date
	, a.tran_no2
	, a.tran_no
	, A.DOC_DATE
	, (extract(year FROM A.DOC_DATE)) AS yr
	, (extract(month FROM A.DOC_DATE)) AS mn
FROM gen_ledgers b
INNER JOIN master_gen_ledger a ON a.tran_no = b.tran_no
;

Select allOpen in new window

Currently your existing query is simply asking that all dates get aligned by year, but no specific date range has been requested (i.e. all years would be returned). What is it you really want to achieve?

WASIM I GOT SAME ERROR
paul , each year has new serial of tran_no
so the query should have a condition of same year also

"SAME"

if you can post the "SAME" error message here.. then we would be able to help you quickly..!!

did you try to_date too?

select b.tran_no,b.doc_date,a.tran_no2,a.tran_no,A.DOC_DATE,(extract(year from A.DOC_DATE) ) as yr,(extract(month from A.DOC_DATE) ) as mn
from gen_ledgers b,master_gen_ledger a
where
a.tran_no = b.tran_no
and (extract(year from to_date(A.DOC_DATE,'DD-MM-YY')) =  (extract(year from to_date(b.DOC_DATE,'DD-MM-YY')));

YES , i tried
same error

I wonder if the tools and/or versions you are using don't like the EXTRACT function?

Try TO_CHAR everywhere you are using EXTRACT?

from:
(extract(year FROM A.DOC_DATE))

to:
(to_char(a.doc_date,'YYYY'))

The last code of Paul works well , and return a result , but I will check the result if it is correct tomorrow I will accept his solution , thanx

paul
the code is 100 % correct , i got an answer
but if you don't mind i have 2 questions :
1- why EXTRACT not works here inspite that i'm using it in another queries and reslut correct ?
2- actually doc_date in both tables should be same value to link
i mean the join should be
where a.tran_no = b.tran_no
and a.doc_date = b.doc_date
but in fact i found some of records dosent match same doc_date within same year
example :
record :  
a.tran_no = 1500   a.doc_date = '18-jan-15'
b.tran_no = 1500   b.doc_date = '19-jan-15'
as per your last query they match according to same year , which is correct
but i need to get those records whom their doc_date doesnt match in same year in purpose to update them

I don't know why extract isn't working for you, but as there was no obvious syntax error I could see I simply used TRUNC() as the alternative as I know this works on every version of Oracle I have ever encountered.

---------------
a JOIN should  NOT be formed in the WHERE clause
that is very ancient approach (pre dating a 1986 standard)

please use explicit join syntax
-------------

>>"actually doc_date in both tables should be same value"

date/time information can be very tricky in joins particularly if the precision of the time information is very finegrained.
(really the transaction number by itself should be enough, but the current design does not allow that)

>>"i need to get those records whom their doc_date doesn't match"

This should find records where the year does match but the day does not. Is this sufficient?

SELECT
           b.tran_no
	, b.doc_date
	, a.tran_no2
	, a.tran_no
	, A.DOC_DATE
	, (extract(year FROM A.DOC_DATE)) AS yr
	, (extract(month FROM A.DOC_DATE)) AS mn
FROM gen_ledgers b
INNER JOIN master_gen_ledger a ON a.tran_no = b.tran_no
              AND TRUNC(A.DOC_DATE,'YEAR') = TRUNC(b.DOC_DATE,'YEAR')
WHERE TRUNC(A.DOC_DATE) <> TRUNC(b.DOC_DATE)
;

Select allOpen in new window

ok thanx paul