• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1533
  • Last Modified:

SQL Sysdate Decode Statement EASY POINTS

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
daveleblanc
Asked:
daveleblanc
  • 2
1 Solution
 
pennnnCommented:
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
 
NievergeltSenior SW DevCommented:
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
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
Mark GeerlingsDatabase AdministratorCommented:
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
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

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

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