Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 912
  • Last Modified:

Oracle trigger for the day name of the week

I am saving values to a table and I have a date that I need to get the DayOfWeek saved to another column. I want to do it in a proc or maybe in a trigger? I am not sure what the syntax is. I know in MySQL I can use DAYNAME('12/12/2103') and it would return Thursday but Im not sure what it is in Oracle.

Thanks
JK
0
jknj72
Asked:
jknj72
2 Solutions
 
sdstuberCommented:
to_char(sysdate,'fmDay')
0
 
slightwv (䄆 Netminder) Commented:
try this:
select to_char(to_date('12/12/2103','MM/DD/YYYY'),'Day') from dual;

It's all about the format masks.  The online docs for your version will show you what they all are.
0
 
sdstuberCommented:
you might not want to use 'Day'  because that will right pad the names

'fmDay' will have them trimmed

for a trigger to do it, it might look something like this...


CREATE OR REPLACE TRIGGER trg_your_table_briu
    BEFORE INSERT OR UPDATE
    ON yourtable
    FOR EACH ROW
BEGIN
     :new.dayofweek := to_char(:new.your_date_column,'fmDay');
END;
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
slightwv (䄆 Netminder) Commented:
If you are using 11g or above, I would look at using Virtual Columns.

These are derived when the column is selected and not permanently stored. Then you don't have to worry about triggers or procedures.

Check the example in the docs:
http://docs.oracle.com/cd/E11882_01/server.112/e25494/tables.htm#ADMIN11004

...
 hrly_rate  NUMBER(7,2) GENERATED ALWAYS AS (sal/2080),
...

hrly_rate is derived based on the sal column's value.
0
 
awking00Commented:
I calculated 12/12/2103 to be a Wednesday. Did I do something wrong?
0
 
slightwv (䄆 Netminder) Commented:
>>Did I do something wrong?

The typo of 2013?
0
 
stefan73Commented:
Why do you want to store the weekday in a column? This results in de-normalized data. Use a view (or virtual column) for this.
0
 
jknj72Author Commented:
I decided to split points with you two. Thank you both for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now