TO_CHAR(…, ‘D’)

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;
TUESDAY 2

With decode()

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;
2

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

8 thoughts on “TO_CHAR(…, ‘D’)”

  1. So you are suggesting I apply the setting ERROR_ON_OVERLAP_TIME in the session to avoid the ambiguity of daylight saving, like they are having in the United Kingdom’s GMT at the moment?

  2. if you use timezone ‘YYYY-MM-DD HH24:MI:SS +TZH:TZM’ you do not need it. If you use Europe/London, then you may have that problem, which is however only 1 hour per year (0.01%)

  3. Pingback: Converting a date to a day of the week | Ora9.com

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>