/*==========================================================================*/
/* Instant/Interval Test Cases for the Oracle DBMS                          */
/*==========================================================================*/

/*--------------------------------------------------------------------------*/
/* Define some variables for use in the instant/interval test cases         */
/*--------------------------------------------------------------------------*/

DEFINE D1 = "TO_DATE('1997-01-01 12:34:56', 'YYYY-MM-DD HH24:MI:SS')";
DEFINE D2 = "TO_DATE('1997-12-12 05:06:07', 'YYYY-MM-DD HH24:MI:SS')";
DEFINE D3 = "TO_DATE('1997-06-06 11:10:09', 'YYYY-MM-DD HH24:MI:SS')";
DEFINE D4 = "TO_DATE('1997-07-07 10:09:08', 'YYYY-MM-DD HH24:MI:SS')";
/* DEFINE J1 = "(TO_NUMBER(SUBSTR('1 23:45:56', 1, LENGTH('1 23:45:56') - 9))*/
/*  + TO_NUMBER(SUBSTR('1 23:45:56', LENGTH('1 23:45:56') - 7, 2)) / 24      */
/*  + TO_NUMBER(SUBSTR('1 23:45:56', LENGTH('1 23:45:56') - 4, 2)) / 1440    */
/*  + TO_NUMBER(SUBSTR('1 23:45:56', LENGTH('1 23:45:56') - 1, 2)) / 86400)";*/
DEFINE J1 = "TO_NUMBER('1.99023')";
/* DEFINE J2 = "(TO_NUMBER(SUBSTR('8 08:08:08', 1, LENGTH('8 08:08:08') - 9))*/
/*  + TO_NUMBER(SUBSTR('0 08:08:08', LENGTH('8 08:08:08') - 7, 2)) / 24      */
/*  + TO_NUMBER(SUBSTR('8 08:08:08', LENGTH('8 08:08:08') - 4, 2)) / 1440    */
/*  + TO_NUMBER(SUBSTR('8 08:08:08', LENGTH('8 08:08:08') - 1, 2)) / 86400)";*/
DEFINE J2 = "TO_NUMBER('8.33898')";
/* DEFINE J3 = "(TO_NUMBER(SUBSTR('5 05:05:05', 1, LENGTH('5 05:05:05') - 9))*/
/*  + TO_NUMBER(SUBSTR('5 05:05:05', LENGTH('5 05:05:05') - 7, 2)) / 24      */
/*  + TO_NUMBER(SUBSTR('5 05:05:05', LENGTH('5 05:05:05') - 4, 2)) / 1440    */
/*  + TO_NUMBER(SUBSTR('5 05:05:05', LENGTH('5 05:05:05') - 1, 2)) / 86400)";*/
DEFINE J3 = "TO_NUMBER('5.21186')";

/*--------------------------------------------------------------------------*/

/*** Literals: ***/

/* DATE '1997-01-01' */
SELECT TO_DATE('1997-01-01', 'YYYY-MM-DD') AS RESULT FROM DUAL;

/* TIME '12:34:56' */
SELECT TO_DATE('12:34:56', 'HH24:MI:SS') AS RESULT FROM DUAL;

/* TIMESTAMP '1997-01-01 12:34:56' */
SELECT TO_DATE('1997-01-01 12:34:56', 'YYYY-MM-DD HH24:MI:SS') AS RESULT
    FROM DUAL;

/* INTERVAL '1 23:45:12' DAY TO SECOND */
SELECT TO_NUMBER(SUBSTR('1 23:45:12', 1, LENGTH('1 23:45:12') - 9)) 
	+ TO_NUMBER(SUBSTR('1 23:45:12', LENGTH('1 23:45:12') - 7, 2)) / 24
	+ TO_NUMBER(SUBSTR('1 23:45:12', LENGTH('1 23:45:12') - 4, 2)) / 1440 
	+ TO_NUMBER(SUBSTR('1 23:45:12', LENGTH('1 23:45:12') - 1, 2)) / 86400
    AS RESULT FROM DUAL;

/*** Predicates: ***/

/* d1 = d2 */
SELECT 'TRUE' AS TRUE FROM DUAL WHERE &D1 = &D2;

/* d1 < d2 */
SELECT 'TRUE' AS TRUE FROM DUAL WHERE &D1 < &D2;

/* d1 <> d2 */
SELECT 'TRUE' AS TRUE FROM DUAL WHERE &D1 <> &D2;

/* d1 BETWEEN d2 AND d3 */
SELECT 'TRUE' AS TRUE FROM DUAL WHERE &D1 BETWEEN &D2 AND &D3;

/* i1 = i2 */
SELECT 'TRUE' AS TRUE FROM DUAL WHERE &J1 = &J2;

/* i1 < i2 */
SELECT 'TRUE' AS TRUE FROM DUAL WHERE &J1 < &J2;

/* i1 <> i2 */
SELECT 'TRUE' AS TRUE FROM DUAL WHERE &J1 <> &J2;

/* i1 BETWEEN i2 AND i3 */
SELECT 'TRUE' AS TRUE FROM DUAL WHERE &J1 BETWEEN &J2 AND &J3;

/* d IS NULL */
SELECT 'TRUE' AS TRUE FROM DUAL WHERE &D1 IS NULL;

/* i IS NULL */
SELECT 'TRUE' AS TRUE FROM DUAL WHERE &J1 IS NULL;

/* (d1, i2) OVERLAPS (d3, d4) */
SELECT 'TRUE' AS TRUE FROM DUAL WHERE &D1 <= &D4 AND &D3 <= (&D1 + &J2);
	
/*** Datetime Constructors: ***/

/* d + i */
SELECT &D1 + &J1 AS RESULT FROM DUAL;

/* i + d */
SELECT &J1 + &D1 AS RESULT FROM DUAL;

/* d - i */
SELECT &D1 - &J1 AS RESULT FROM DUAL;

/* CURRENT_DATE */
SELECT TRUNC(SYSDATE) AS CURRENT_DATE FROM DUAL;

/* CURRENT_TIME */
SELECT TO_DATE(TO_CHAR(SYSDATE, 'HH24:MI:SS'), 'HH24:MI:SS') AS CURRENT_TIME
    FROM DUAL;

/* CURRENT_TIMESTAMP */
SELECT SYSDATE AS CURRENT_TIMESTAMP FROM DUAL;

/*** Interval Constructors: ***/

/* i1 + i2 */
SELECT &J1 + &J2 AS RESULT FROM DUAL;

/* i1 - i2 */
SELECT &J1 - &J2 AS RESULT FROM DUAL;

/* d1 - d2 qual */
SELECT &D1 - &D2 AS RESULT FROM DUAL;

/* d1 - d2 INTERVAL MONTH */
SELECT MONTHS_BETWEEN(&D1, &D2) FROM DUAL;

/* i * n */
SELECT &J1 * 3 AS RESULT FROM DUAL;

/* n * i */
SELECT 3 * &J1 AS RESULT FROM DUAL;

/* i1 / i2 */
SELECT &J1 / &J2 AS RESULT FROM DUAL;

/* + i */
SELECT +&J1 AS RESULT FROM DUAL;

/* - i */
SELECT -&J1 AS RESULT FROM DUAL;

/*** Other Operators: ***/

/* CAST(d AS DATE) */
SELECT TRUNC(&D1) AS RESULT FROM DUAL;

/* CAST (d AS TIME) */
SELECT TO_DATE(TO_CHAR(&D1, 'HH24:MI:SS'), 'HH24:MI:SS') AS RESULT FROM DUAL;

/* CAST(d AS TIMESTAMP) (where d is a DATE) */
SELECT TRUNC(&D1) AS RESULT FROM DUAL;

/* CAST(d AS TIMESTAMP) (where d is a TIME) */
SELECT TRUNC(SYSDATE) + (&D1 - TRUNC(&D1)) AS RESULT FROM DUAL;

/* CAST(i AS INTERVAL DAY TO SECOND) */
SELECT &J1 AS RESULT FROM DUAL;

/* CAST(d AS CHAR) */
SELECT TO_CHAR(&D1, 'YYYY-MM-DD HH24:MI:SS') AS RESULT FROM DUAL;

/* CAST(i AS DATE) */
SELECT TRUNC(&J1, 0) || ' ' || TO_CHAR(&J1 + TO_DATE(1, 'J'), 'HH24:MI:SS')
    AS RESULT FROM DUAL;

/* CAST(i AS INTEGER) (where i is a DAY) */
SELECT TRUNC(&J1, 0) AS RESULT FROM DUAL;

/* CAST(i AS INTEGER) (where i is a HOUR) */
SELECT TRUNC(&J1 * 24, 0) AS RESULT FROM DUAL;

/* CAST(i AS INTEGER) (where i is a MINUTE) */
SELECT TRUNC(&J1 * 1440, 0) AS RESULT FROM DUAL;

/* CAST(i AS INTEGER) (where i is a SECOND) */
SELECT TRUNC(&J1 * 86400, 0) AS RESULT FROM DUAL;

/* EXTRACT(DAY from d) */
SELECT TRUNC(&D1, 'DD') - TRUNC(&D1, 'MM') + 1 AS RESULT FROM DUAL;

/* EXTRACT(DAY from i) */
SELECT TRUNC(&J1, 0) AS RESULT FROM DUAL;

/* EXTRACT(HOUR from i) */
SELECT TRUNC(&J1 * 24, 0) - (TRUNC(&J1, 0) * 24) AS RESULT FROM DUAL;

/*** Other Operators: ***/

/* Convert d to Julian day */
SELECT TO_CHAR(&D1, 'J') AS RESULT FROM DUAL;

/* Convert Julian day n to DATE */
SELECT &J1 + TO_DATE(1, 'J') AS RESULT FROM DUAL;

/* Pick the earliest date */
SELECT LEAST(&D1, &D2, &D3) AS RESULT FROM DUAL;

/* Pick the latest date */
SELECT GREATEST(&D1, &D2, &D3) AS RESULT FROM DUAL;

/* Pick the last day of the month */
SELECT LAST_DAY(&D1) AS RESULT FROM DUAL;

/* Get the next day of the week */
SELECT NEXT_DAY(&D1, 'Monday') AS RESULT FROM DUAL;

/*==========================================================================*/
