Posts Tagged ‘to_char’
TO_CHAR(…, ‘D’)
Tuesday, 23 August, 2011
4 comments
to_char(nchar-clob-or-nclob) to_char(datetime-or-interval) to_char(datetime-or-interval, 'format-string') to_char(datetime-or-interval, 'format-string', 'nlsparam') to_char(number) to_char(number,'format-string') to_char(number,'format-string', 'nlsparam')
How do I get than MONDAY=1, TUESDAY=2, WEDNESDAY=3 … ?
solution1
With to_char()
alter session set nls_territory=germany; select to_char(sysdate,’DAY D’) from dual; TUESDAY 2
solution2
With decode()
The abbrivation of a day’s name (such as SUN, MON … can be retrieved with the format specifier DY. As to_char is sensitive to the actual nls setting, I use nls_date_language=english to make sure that the english abbreviation is returned, regardless of the nls setting.
select decode(to_char(sysdate, ‘FMDAY’, ‘NLS_DATE_LANGUAGE=american’),’MONDAY’, ’1′, ‘TUESDAY’, ’2′, ‘…’)) from dual;
solution3
With mod()
As a reference, I take monday Jan 1st, 1000.
select mod(trunc(sysdate)-date ’1000-01-01′,7)+1 from dual;
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
Spelling a number
select to_char(to_date('429','J'),'Jsp') from dual; TO_CHAR(TO_DATE('429','J ------------------------ Four Hundred Twenty-Nine
Recent Comments