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
Hello,
Recently I been trying out TIMESTAMP for my benefit in integrating it with HTML DB. I made an example up that might be of interest for you and anyone else to review.
http://www.shellprompt.net/pls/htmldb/f?p=108:1
Please tell me what you think!
(Email details on the Introduction page.)
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?
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.
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%)
Pingback: Laurent Schneider » Blog Archive » how to get monday=1, tuesday=2…
Pingback: Tony’s Oracle Tips
Pingback: Converting a date to a day of the week | Ora9.com
Pingback: TO_CHAR(…, ‘D’) « Baurdotnet’s Weblog