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. Colin Sheppard

    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. Laurent Schneider

    there are no difference between round(ts,’HH’) and round(ts,’HH24′). Check my post abound boundaries, to see a supplementary advantage of using time zone.

  3. Laurent Schneider

    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%)

  4. Pingback: Laurent Schneider » Blog Archive » how to get monday=1, tuesday=2…

  5. Pingback: Tony’s Oracle Tips

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

  7. Pingback: TO_CHAR(ā€¦, ā€˜Dā€™) « Baurdotnet’s Weblog

Comments are closed.