Month: July 2005

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 […]

if I create a function for a materialized view with query rewrite or for a function based index, I must create hte deterministic. f(x) = x*2 is deterministic. for a give x, f(x) will always be the same, f(5) will be always 10; always. f(y) = sysdate+y is non-deterministic. For a given y, the return […]

I just come back from holiday, I am quite busy at the moment. Here is a tiny function to get the age trunc((to_char(sysdate,’YYYYMMDD’)-to_char(birthdate,’YYYYMMDD’))/10000) it is much safer than add_months, because add_months do some conversion at the end of the month, and I would never accept to wait until Feb 29th, 2008 (28-2-1990 + 18*12 months) […]