[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Sysdate Decode Statement EASY POINTS

Posted on 2005-05-16
4
Medium Priority
?
1,460 Views
Last Modified: 2008-02-01
hi i have this statment

TO_CHAR(decode(to_char(sysdate,'dy'),'Mon',trunc(sysdate-3),trunc(sysdate-1)),'dd-Mon-yyyy')

essentially i wanted to operate like this, If the day is Monday, then sysdate = sysdate - 3
else sysdate = sysdate - 1

what i have here works fine on every other day except monday

could someone explain why? does it have something to do with the way its formatted?

can you provide an example of what might be a better solution

Thanks
0
Comment
Question by:daveleblanc
  • 2
4 Comments
 
LVL 11

Accepted Solution

by:
pennnn earned 150 total points
ID: 14010400
I hope this explains it:
SQL > select to_char(sysdate,'dy') from dual;

TO_CHAR(SYSDATE,'DY')
--------------------------------------------
mon

SQL > select to_char(sysdate,'Dy') from dual;

TO_CHAR(SYSDATE,'DY')
--------------------------------------------
Mon

The "Mon" you're comparing to in the decode starts with an upper case "M", that's why it doesn't match. Your format mask needs to be 'Dy', or if you leave it as 'dy' the string you compare to needs to be 'mon' (instead of 'Mon').
Hope this helps!
0
 
LVL 7

Expert Comment

by:Nievergelt
ID: 14010528
You can use:

TO_CHAR(CASE
                 WHEN UPPER(TO_CHAR(SYSDATE, 'dy')) = 'MON' THEN SYSDATE-3
                 ELSE SYSDATE-1
               END,
               'dd-Mon-yyyy')

Share and Enjoy  Christoph
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 14010648
I would use something like this to get down to just one call to sysdate:

select case to_char(sysdate,'dy') when 'mon' then -3 else -1 end "Adj" from dual;

To use this in PL\SQL, just replace "sysdate" with your date column or variable and drop the alias "Adj" and the "from dual".
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 14011391
Just remember that unless you are on Oracle10, each call to sysdate causes at least one logical block read.  If this is in a procedure that gets exectuted frequently, those multiple calls to sysdate can add up to a noticeable performance impact.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to recover a database from a user managed backup
Suggested Courses
Course of the Month18 days, 21 hours left to enroll

834 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