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
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
Posted by Laurent Schneider at 7/26/2005 10:43:00 AM
Permalink : TO_CHAR(..., 'D')
Post to :
del.icio.us -
furl -
digg -
reddit -
co.mments
4 Comments:
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.)
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.
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?
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%)
Post a Comment
<< Home