Solved

Oracle trigger for the day name of the week

Posted on 2013-12-12
8
820 Views
Last Modified: 2013-12-16
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
Comment
Question by:jknj72
8 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 39715137
to_char(sysdate,'fmDay')
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 total points
ID: 39715139
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
 
LVL 73

Accepted Solution

by:
sdstuber earned 250 total points
ID: 39715148
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39715166
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 31

Expert Comment

by:awking00
ID: 39715300
I calculated 12/12/2103 to be a Wednesday. Did I do something wrong?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39715414
>>Did I do something wrong?

The typo of 2013?
0
 
LVL 12

Expert Comment

by:stefan73
ID: 39715450
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
 

Author Closing Comment

by:jknj72
ID: 39721398
I decided to split points with you two. Thank you both for your help.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
Creating and Managing Databases with phpMyAdmin in cPanel.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to recover a database from a user managed backup

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now