Home » SQL & PL/SQL » SQL & PL/SQL » Find Out Day By Number Of Day (10g)
Find Out Day By Number Of Day [message #406408] Wed, 03 June 2009 22:47 Go to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

Dear All

I want to find out the day by passing number of day. For examaple 1=Sunday and if i pass 1 in function it should return day name Sunday and if i pass 2 it should return monday.

I know it can be solved by Decode or Case Statment but i need oracle builtin function or any other function.

Thanks In Advance.

Regards.
Asif.
Re: Find Out Day By Number Of Day [message #406418 is a reply to message #406408] Wed, 03 June 2009 23:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use DECODE or CASE.

Regards
Michel
Re: Find Out Day By Number Of Day [message #406421 is a reply to message #406408] Wed, 03 June 2009 23:43 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
You can use something like this (not a direct built-in function though).
select to_char(to_date(5+&num_of_day,'J'),'DAY')
from dual;
But I'm not sure whether it works for all type of calendars.

You need to take care of the validations on num_of_day parameter 0<num_of_day<=7

By
Vamsi
Re: Find Out Day By Number Of Day [message #406490 is a reply to message #406421] Thu, 04 June 2009 04:22 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you use TO_DATE to do this, then you're going to hit regionalisation problems - the first day of the week in some places is Sunday, and in other places it's Monday
Re: Find Out Day By Number Of Day [message #406493 is a reply to message #406490] Thu, 04 June 2009 04:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No problem with the julian (J) format.
It is a nice trick.
SQL> alter session set nls_territory=america nls_date_language=american;

Session altered.

SQL> def num_of_day=1
SQL> select to_char(to_date(5+&num_of_day,'J'),'DAY') d1, 
  2         to_char(to_date(5+&num_of_day,'J'),'D') d2
  3  from dual;
D1        D
--------- -
SUNDAY    1

1 row selected.

SQL> alter session set nls_territory=france;

Session altered.

SQL> select to_char(to_date(5+&num_of_day,'J'),'DAY') d1, 
  2         to_char(to_date(5+&num_of_day,'J'),'D') d2
  3  from dual;
D1        D
--------- -
SUNDAY    7

1 row selected.

Regards
Michel
Re: Find Out Day By Number Of Day [message #406524 is a reply to message #406493] Thu, 04 June 2009 07:04 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
D'oh - I must read the post more carefully.
Re: Find Out Day By Number Of Day [message #406528 is a reply to message #406524] Thu, 04 June 2009 07:26 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
When I say all type of calendars, I mean
SQL> alter session set nls_calendar='Arabic Hijrah';

Session altered.

SQL> def num_of_day=1
SQL> select to_char(to_date(5+&num_of_day,'J'),'DAY')
from dual;  2  
old   1: select to_char(to_date(5+&num_of_day,'J'),'DAY')
new   1: select to_char(to_date(5+1,'J'),'DAY')
select to_char(to_date(5+1,'J'),'DAY')
       *
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
It is only working for Gregorian and Persian.
Quote:
Arabic Hijrah
English Hijrah
Gregorian
Japanese Imperial
Persian
ROC Official (Republic of China)
Thai Buddha
All others are giving either the above error or the below one.
SQL> alter session set nls_calendar='Thai Buddha';

Session altered.

SQL> def num_of_day=1                                   
SQL> select to_char(to_date(5+&num_of_day,'J'),'DAY') from dual;
old   1: select to_char(to_date(5+&num_of_day,'J'),'DAY') from dual
new   1: select to_char(to_date(5+1,'J'),'DAY') from dual
select to_char(to_date(5+1,'J'),'DAY') from dual
       *
ERROR at line 1:
ORA-01863: the year is not supported for the current calendar
By
Vamsi
Re: Find Out Day By Number Of Day [message #406529 is a reply to message #406528] Thu, 04 June 2009 07:34 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Ah!

The sweet joy of unexpected correctness.

Id go with @Michels original solution:

CREATE OR REPLACE FUNCTION get_day(p_day in number) return varchar2 is
begin
  return case p_day when 1 then 'Sunday'
                    when 2 then 'Monday'
                    when 3 then 'Tuesday'
                    when 4 then 'Wednesday'
                    when 5 then 'Thurday'
                    when 6 then 'Friday'
                    when 7 then 'Saturday'
         end;
END;
/


Re: Find Out Day By Number Of Day [message #406530 is a reply to message #406529] Thu, 04 June 2009 07:42 Go to previous message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Yes!! Me too.
That is easy to understand, easy to write, no confusion, quick and portable..... Surprised

By
Vamsi
Previous Topic: Is it safe to rely on ROWID if the row is locked?
Next Topic: How to find out the user name or login name
Goto Forum:
  


Current Time: Sun Dec 04 18:56:28 CST 2016

Total time taken to generate the page: 0.04316 seconds