Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Oracle trigger for the day name of the week

Posted on 2013-12-12
8
Medium Priority
?
906 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 74

Expert Comment

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

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1000 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 74

Accepted Solution

by:
sdstuber earned 1000 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
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
LVL 77

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
 
LVL 32

Expert Comment

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

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

704 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