DATE question

[bgill]

I have a table called transactions. Each transaction has a date
TRAN_DATE and a branch code TRAN_BR_CODE.

I want to find out the number of transactions per branch over the last year that were transacted on Saturdays only.

All dates on the table are in the DD-MON-YYYY format.
Is there any way that Oracle can extrapolate the actual day from these dates, or will I have to look through the calendar
and have my select as

SELECT
TRAN_DATE, TRAN_BR_CODE, TRAN_AMOUNT
FROM
TRANSACTIONS
WHERE TRAN_DATE IN (Enter all dates for 52 saturdays in the year)



Any advice appreciated
Thanks

[ales]

The data type of TRAN_DATE is not clear to me.
If it's a DATE column you could use

select to_char(tran_date,'DD-MON-YYYY'), TRAN_BR_CODE, count(*)
from transactions
where tran_date>=sysdate-365
and to_char(tran_date,'DAY')='SATURDAY ' -- do not forget the space after Y!
group by to_char(tran_date,'DD-MON-YYYY'), TRAN_BR_CODE;

If the TRAN_DATE column is of a character data type you'll have to convert it to date with the TO_DATE function.

[bgill]

Thanks ales.

Sorry, I forgot to mention the datatype. It is type DATE.
I will try your solution now.

Thanks again

[mkumarnk]

Hi

Let me make a littlecorrection...

"......last year that were transacted on Saturdays only"

select to_char(tran_date,'DD-MON-YYYY'), TRAN_BR_CODE, count(*)
from transactions
where tran_date>between trunc(add_months(sysdate,-12),'YEAR') and trunc(sysdate,'YEAR')-1
and to_char(tran_date,'DAY')='SATURDAY '
----- do not forget the space after Y!
group by to_char(tran_date,'DD-MON-YYYY'), TRAN_BR_CODE;

[stecal]

You can also try using day of the week instead of DAY like SATURDAY, where day of the week is 6 or 7 (depending on you NLS format for date).

This is from MetaLink:

From time to time people have asked if there is a view which can be interrigated to find what the "Week Start Day" is. Unfortuately there is no such view which will give you this information. However, with the following pl/sql function this
information can be readily available.

create or replace function get_first_day return varchar2 is
v_sun varchar2(30) := '06-jan-2002';
v_mon varchar2(30) := '07-jan-2002';
v_day_num number;
begin
v_day_num := to_char(to_date(v_sun,'dd-mon-yyyy'),'D');

if v_day_num = 1 then
return 'Sunday';
end if;

v_day_num := to_char(to_date(v_mon,'dd-mon-yyyy'),'D');

if v_day_num = 1 then
return 'Monday';
end if;

return 'unknown';
end;

The following is an output from sqlplus on how the function is used.

********************************************************************************

SQL> alter session set nls_territory = america;

Session altered.

SQL> select get_first_day from dual;

GET_FIRST_DAY
-----------------------------------------------------
Sunday

SQL> alter session set nls_territory = france;

Session altered.

SQL> select get_first_day from dual;

GET_FIRST_DAY
-----------------------------------------------------
Monday