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

By Laurent Schneider

Oracle Certified Master

8 comments

  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. 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. 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: Converting a date to a day of the week | Ora9.com

Leave a comment

Your email address will not be published.