Home > PL/SQL > TO_CHAR(…, ‘D’)

TO_CHAR(…, ‘D’)

Tuesday, 23 August, 2011 Leave a comment Go to comments


to_char(datetime-or-interval, 'format-string')
to_char(datetime-or-interval, 'format-string', 'nlsparam')

to_char(number,'format-string', 'nlsparam')

How do I get than MONDAY=1, TUESDAY=2, WEDNESDAY=3 … ?
With to_char()

alter session set nls_territory=germany;
select to_char(sysdate,’DAY D’) from dual;

With decode()

The abbrivation of a day’s name (such as SUN, MON … can be retrieved with the format specifier DY. As to_char is sensitive to the actual nls setting, I use nls_date_language=english to make sure that the english abbreviation is returned, regardless of the nls setting.

select decode(to_char(sysdate, ‘FMDAY’, ‘NLS_DATE_LANGUAGE=american’),’MONDAY’, ’1′, ‘TUESDAY’, ’2′, ‘…’)) from dual;

With mod()
As a reference, I take monday Jan 1st, 1000.

select mod(trunc(sysdate)-date ’1000-01-01′,7)+1 from dual;
  • bonus )))
  • How do I trunc date to current monday?

    trunc(date, ‘D’) or here is with my solution with 1000-01-01:

    select trunc((sysdate-date ’1000-01-01′)/7)*7+date ’1000-01-01′ from dual;

    thanks to jan-marcel idea, I found that one
    trunc(date,’IW’) for current monday and date-trunc(date,’IW’)+1 for day number

    Spelling a number

    select to_char(to_date('429','J'),'Jsp') from dual;
    Four Hundred Twenty-Nine


    Categories: PL/SQL Tags: , ,
    1. Tuesday, 23 August, 2011 at 12:10 pm

      what about this ?

      SQL> select to_char(sysdate,'DAY','NLS_DATE_LANGUAGE=''NUMERIC DATE LANGUAGE''') from dual;

      • Tuesday, 23 August, 2011 at 1:38 pm

        thanks that’s more graceful solution than with decode, but – can I be sure that ’MONDAY’ returns ’1′ with any NLS?

    2. Wednesday, 24 August, 2011 at 12:47 am

      Yes, just start ORACLE_HOME/bin/lbuilder and see how it is defined. 🙂

    1. Friday, 3 March, 2017 at 4:21 pm

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out /  Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out /  Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out /  Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out /  Change )

    Connecting to %s

    %d bloggers like this: