Thursday, 7 March 2013

PLSQL what day is it?

PLSQL what day is it

Say that for some reason you need name of the day for certain reason...
You can create function that will return name of the day of specified date....


function f_name_of_the day (i_date) return varchar2 as
l_day varchar2(50):=null;
begin
   SELECT TO_CHAR( trunc(to_date( to_char(to_date(i_date,'YYYYMMDD')) )),'day') into l_day FROM DUAL;
   return(day);
end f_name_of_the day ;

So what does this function do?

You enter number in date format like 20130306 which is 2013 year 03 month 06 day and function should return Wednesday value....
You can manipulate with desired input value by changing format of date
 (to_date(i_date,'YYYYMMDD')) into lets say (to_date(i_date,'YYYYDDMM')) so that input now must be in year day month shape.

CODE in SQL developer

Here is  code for simple developer test that will display name of the day

declare
l_day varchar2(50):='20130306';
begin
   select to_char( trunc(to_date( to_char(to_date(l_day,'YYYYMMDD')) )),'day') into l_day from dual;
   dbms_output.put_line(l_day);
end;

0 comments:

Post a Comment