CODEWITHSUNDEEP
oracle-database
ashish
ashish

Reputation: 239

subtract date based on day in oracle

Based on below query when part is not work, 

select case when to_char(sysdate,'Day')  =  'Friday'  then trunc(SYSDATE) - 3 else trunc(SYSDATE) - 2   end as tempdate from dual

any help?

Upvotes: 0

Views: 35

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

TO_CHAR puts trailing spaces. you may add a TRIM

SELECT
        CASE
            WHEN TRIM(TO_CHAR(SYSDATE,'Day')) = 'Friday' THEN trunc(SYSDATE) - 3
            ELSE trunc(SYSDATE) - 2
        END
    AS tempdate
FROM
    dual

You may also use Fm format specifier

to_char (sysdate, 'FmDay') to avoid TRIM

or to avoid NLS_ parameter issues

TO_CHAR (sysdate, 'FmDay', 'nls_date_language=english')

Upvotes: 2

Related Questions