I have three little Oracle statements that I us quite often. I realise I use these statements in most programmes I write. But at the same time, I often forget the exact syntax. Therefore this entry in this blog. It will act as a look-up whenever I once again forgot the precise syntax.
The first retrieves the hour from a datefield. It looks like:
select to_number(substr(numtodsinterval( sysdate - trunc(sysdate), 'day' ),12,2)) as hour from dual;
The second one provides a row number to each row. This row number is reset at 1 whenever one field changes value.
select tel1, tel2, ROW_NUMBER () OVER (PARTITION BY tel1 ORDER BY tel2) AS RN from (select 1 as tel1, 1 as tel2 from dual union select 1 as tel1, 2 as tel2 from dual union select 2 as tel1, 1 as tel2 from dual union select 2 as tel1, 2 as tel2 from dual)A;
A third handy Oracle statement is generating a number that is recognised in Excel as a date. Excel starts its date range by stating 01 January 1900 being equal to 1, 02 January 1900 being equal to 2, and so forth. This goes on and 42602 equals 20 August 2016. The statement is:
select to_date(sysdate) - to_date('19000101','yyyymmdd')+2 as datum from dual;
Why this “2” in this statement? This happens as Excel knows 29 Feb 1900 that never existed. Therefore 59 is 28 Feb 1900, 60 is 29 Feb 1900 and 61 is 1 March 1900. Excel thus assumes 1900 is a leap year, whereas this is never included in the Georgian calendar. This is a well-known issue with Excel. It is a bug that is maintained as to support backward compatability with Lotus 123.