-
编辑 删除
unix epoch time to oracle time
Our billing system date format is using unix epoch time. converison like the following:oracle time --> unix epoch timeselect (sysdate - to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) * 86400 - substr(TZ_OFFSET(sessiontimezone),1,instr(TZ_OFFSET(sessiontimezone),':')-1) * 3600 from dual;unix epoch time-->oracle timeselect from_tz(cast(to_date('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS') as timestamp),'Australia/NSW') at local + numtodsinterval(1305882706,'SECOND') from dual;you c
-
编辑 删除
Julian day
our date dimension surrogate key is built by Julian day. (Julian day: the number of days since January 1, 4712 BC. ) The following are some operations on that:1.Julian date-->normal oracle date:SQL> select date_sk,to_date(date_sk,'j') normal_date from dim_date2 where date_sk<>0 and rownum<10;DATE_SK NORMAL_DA---------- ---------2451545 01/JAN/002451546 02/JAN/002451547 03/JAN/002451548 04/JAN/002451549 05/JAN/002451550 06/JAN/002451551 07/JAN/002451552 08/JAN/002451553 09/JAN/002.
-
编辑 删除
Escape clause to avoid wildcard characters.
If the escape character precedes the character % or _ in the pattern, then Oracle interprets this character literally in the pattern rather than as a special pattern-matching charactere.g.SELECT * FROM dba_tablesWHERE table_name LIKE '%GL_%' ESCAPE ''[@more@]
-
编辑 删除
Oracle's quote operator
q'! text-to-be-quoted !'! is a signle character, called the quote delimiter. with the quote operator, apostrophes don't have to be dboule:e.g. select q'#Oracle's quote operator#' quote_test from dual;QUOTE_TEST--------------------------Oracle's quote operatorit's very helpful and convenience to use the quote, particularly when you want to update or insert a very long and big sql. e.g:DECLARE l_sql VARCHAR2(32767);BEGIN l_sql := q'! select ********** very very long sql here !'; UPDATE t
