ql/src/test/queries/clientpositive/udf_trunc.q

DESCRIBE FUNCTION trunc;
DESCRIBE FUNCTION EXTENDED trunc;

--test string with 'MM' as format
EXPLAIN
SELECT
      TRUNC('2014-01-01', 'MM'),
      TRUNC('2014-01-14', 'MM'),
      TRUNC('2014-01-31', 'MM'),
      TRUNC('2014-02-02', 'MM'),
      TRUNC('2014-02-28', 'MM'),
      TRUNC('2016-02-03', 'MM'),
      TRUNC('2016-02-28', 'MM'),
      TRUNC('2016-02-29', 'MM'),
      TRUNC('2014-01-01 10:30:45', 'MM'),
      TRUNC('2014-01-14 10:30:45', 'MM'),
      TRUNC('2014-01-31 10:30:45', 'MM'),
      TRUNC('2014-02-02 10:30:45', 'MM'),
      TRUNC('2014-02-28 10:30:45', 'MM'),
      TRUNC('2016-02-03 10:30:45', 'MM'),
      TRUNC('2016-02-28 10:30:45', 'MM'),
      TRUNC('2016-02-29 10:30:45', 'MM');


SELECT
      TRUNC('2014-01-01', 'MM'),
      TRUNC('2014-01-14', 'MM'),
      TRUNC('2014-01-31', 'MM'),
      TRUNC('2014-02-02', 'MM'),
      TRUNC('2014-02-28', 'MM'),
      TRUNC('2016-02-03', 'MM'),
      TRUNC('2016-02-28', 'MM'),
      TRUNC('2016-02-29', 'MM'),
      TRUNC('2014-01-01 10:30:45', 'MM'),
      TRUNC('2014-01-14 10:30:45', 'MM'),
      TRUNC('2014-01-31 10:30:45', 'MM'),
      TRUNC('2014-02-02 10:30:45', 'MM'),
      TRUNC('2014-02-28 10:30:45', 'MM'),
      TRUNC('2016-02-03 10:30:45', 'MM'),
      TRUNC('2016-02-28 10:30:45', 'MM'),
      TRUNC('2016-02-29 10:30:45', 'MM');

--test string with 'Q' as format
EXPLAIN
SELECT
      TRUNC('2014-01-01', 'Q'),
      TRUNC('2014-01-14', 'Q'),
      TRUNC('2014-01-31', 'Q'),
      TRUNC('2014-02-02', 'Q'),
      TRUNC('2014-02-28', 'Q'),
      TRUNC('2016-02-03', 'Q'),
      TRUNC('2016-02-28', 'Q'),
      TRUNC('2016-02-29', 'Q'),
      TRUNC('2016-05-11', 'Q'),
      TRUNC('2016-07-01', 'Q'),
      TRUNC('2016-12-31', 'Q'),
      TRUNC('2014-01-01 10:30:45', 'Q'),
      TRUNC('2014-01-14 10:30:45', 'Q'),
      TRUNC('2014-01-31 10:30:45', 'Q'),
      TRUNC('2014-02-02 10:30:45', 'Q'),
      TRUNC('2014-02-28 10:30:45', 'Q'),
      TRUNC('2016-02-03 10:30:45', 'Q'),
      TRUNC('2016-02-28 10:30:45', 'Q'),
      TRUNC('2016-02-29 10:30:45', 'Q'),
      TRUNC('2016-05-11 10:30:45', 'Q'),
      TRUNC('2016-07-01 10:30:45', 'Q'),
      TRUNC('2016-12-31 10:30:45', 'Q');


SELECT
      TRUNC('2014-01-01', 'Q'),
      TRUNC('2014-01-14', 'Q'),
      TRUNC('2014-01-31', 'Q'),
      TRUNC('2014-02-02', 'Q'),
      TRUNC('2014-02-28', 'Q'),
      TRUNC('2016-02-03', 'Q'),
      TRUNC('2016-02-28', 'Q'),
      TRUNC('2016-02-29', 'Q'),
      TRUNC('2016-05-11', 'Q'),
      TRUNC('2016-07-01', 'Q'),
      TRUNC('2016-12-31', 'Q'),
      TRUNC('2014-01-01 10:30:45', 'Q'),
      TRUNC('2014-01-14 10:30:45', 'Q'),
      TRUNC('2014-01-31 10:30:45', 'Q'),
      TRUNC('2014-02-02 10:30:45', 'Q'),
      TRUNC('2014-02-28 10:30:45', 'Q'),
      TRUNC('2016-02-03 10:30:45', 'Q'),
      TRUNC('2016-02-28 10:30:45', 'Q'),
      TRUNC('2016-02-29 10:30:45', 'Q'),
      TRUNC('2016-05-11 10:30:45', 'Q'),
      TRUNC('2016-07-01 10:30:45', 'Q'),
      TRUNC('2016-12-31 10:30:45', 'Q');

--test string with 'YEAR' as format
EXPLAIN
SELECT
      TRUNC('2014-01-01', 'YEAR'),
      TRUNC('2014-01-14', 'YEAR'),
      TRUNC('2014-01-31', 'YEAR'),
      TRUNC('2014-02-02', 'YEAR'),
      TRUNC('2014-02-28', 'YEAR'),
      TRUNC('2016-02-03', 'YEAR'),
      TRUNC('2016-02-28', 'YEAR'),
      TRUNC('2016-02-29', 'YEAR'),
      TRUNC('2014-01-01 10:30:45', 'YEAR'),
      TRUNC('2014-01-14 10:30:45', 'YEAR'),
      TRUNC('2014-01-31 10:30:45', 'YEAR'),
      TRUNC('2014-02-02 10:30:45', 'YEAR'),
      TRUNC('2014-02-28 10:30:45', 'YEAR'),
      TRUNC('2016-02-03 10:30:45', 'YEAR'),
      TRUNC('2016-02-28 10:30:45', 'YEAR'),
      TRUNC('2016-02-29 10:30:45', 'YEAR');


SELECT
      TRUNC('2014-01-01', 'YEAR'),
      TRUNC('2014-01-14', 'YEAR'),
      TRUNC('2014-01-31', 'YEAR'),
      TRUNC('2014-02-02', 'YEAR'),
      TRUNC('2014-02-28', 'YEAR'),
      TRUNC('2016-02-03', 'YEAR'),
      TRUNC('2016-02-28', 'YEAR'),
      TRUNC('2016-02-29', 'YEAR'),
      TRUNC('2014-01-01 10:30:45', 'YEAR'),
      TRUNC('2014-01-14 10:30:45', 'YEAR'),
      TRUNC('2014-01-31 10:30:45', 'YEAR'),
      TRUNC('2014-02-02 10:30:45', 'YEAR'),
      TRUNC('2014-02-28 10:30:45', 'YEAR'),
      TRUNC('2016-02-03 10:30:45', 'YEAR'),
      TRUNC('2016-02-28 10:30:45', 'YEAR'),
      TRUNC('2016-02-29 10:30:45', 'YEAR');


--test timestamp with 'MM' as format
EXPLAIN
SELECT
      TRUNC(CAST('2014-01-01 00:00:00' AS TIMESTAMP), 'MM'),
      TRUNC(CAST('2014-01-14 00:00:00' AS TIMESTAMP), 'MM'),
      TRUNC(CAST('2014-01-31 00:00:00' AS TIMESTAMP), 'MM'),
      TRUNC(CAST('2014-02-02 00:00:00' AS TIMESTAMP), 'MM'),
      TRUNC(CAST('2014-02-28 00:00:00' AS TIMESTAMP), 'MM'),
      TRUNC(CAST('2016-02-03 00:00:00' AS TIMESTAMP), 'MM'),
      TRUNC(CAST('2016-02-28 00:00:00' AS TIMESTAMP), 'MM'),
      TRUNC(CAST('2016-02-29 00:00:00' AS TIMESTAMP), 'MM'),
      TRUNC(CAST('2014-01-01 10:30:45' AS TIMESTAMP), 'MM'),
      TRUNC(CAST('2014-01-14 10:30:45' AS TIMESTAMP), 'MM'),
      TRUNC(CAST('2014-01-31 10:30:45' AS TIMESTAMP), 'MM'),
      TRUNC(CAST('2014-02-02 10:30:45' AS TIMESTAMP), 'MM'),
      TRUNC(CAST('2014-02-28 10:30:45' AS TIMESTAMP), 'MM'),
      TRUNC(CAST('2016-02-03 10:30:45' AS TIMESTAMP), 'MM'),
      TRUNC(CAST('2016-02-28 10:30:45' AS TIMESTAMP), 'MM'),
      TRUNC(CAST('2016-02-29 10:30:45' AS TIMESTAMP), 'MM');


SELECT
      TRUNC(CAST('2014-01-01 00:00:00' AS TIMESTAMP), 'MM'),
      TRUNC(CAST('2014-01-14 00:00:00' AS TIMESTAMP), 'MM'),
      TRUNC(CAST('2014-01-31 00:00:00' AS TIMESTAMP), 'MM'),
      TRUNC(CAST('2014-02-02 00:00:00' AS TIMESTAMP), 'MM'),
      TRUNC(CAST('2014-02-28 00:00:00' AS TIMESTAMP), 'MM'),
      TRUNC(CAST('2016-02-03 00:00:00' AS TIMESTAMP), 'MM'),
      TRUNC(CAST('2016-02-28 00:00:00' AS TIMESTAMP), 'MM'),
      TRUNC(CAST('2016-02-29 00:00:00' AS TIMESTAMP), 'MM'),
      TRUNC(CAST('2014-01-01 10:30:45' AS TIMESTAMP), 'MM'),
      TRUNC(CAST('2014-01-14 10:30:45' AS TIMESTAMP), 'MM'),
      TRUNC(CAST('2014-01-31 10:30:45' AS TIMESTAMP), 'MM'),
      TRUNC(CAST('2014-02-02 10:30:45' AS TIMESTAMP), 'MM'),
      TRUNC(CAST('2014-02-28 10:30:45' AS TIMESTAMP), 'MM'),
      TRUNC(CAST('2016-02-03 10:30:45' AS TIMESTAMP), 'MM'),
      TRUNC(CAST('2016-02-28 10:30:45' AS TIMESTAMP), 'MM'),
      TRUNC(CAST('2016-02-29 10:30:45' AS TIMESTAMP), 'MM');

--test timestamp with 'Q' as format
EXPLAIN
SELECT
      TRUNC(CAST('2014-01-01 00:00:00' AS TIMESTAMP), 'Q'),
      TRUNC(CAST('2014-01-14 00:00:00' AS TIMESTAMP), 'Q'),
      TRUNC(CAST('2014-01-31 00:00:00' AS TIMESTAMP), 'Q'),
      TRUNC(CAST('2014-02-02 00:00:00' AS TIMESTAMP), 'Q'),
      TRUNC(CAST('2014-02-28 00:00:00' AS TIMESTAMP), 'Q'),
      TRUNC(CAST('2016-02-03 00:00:00' AS TIMESTAMP), 'Q'),
      TRUNC(CAST('2016-02-28 00:00:00' AS TIMESTAMP), 'Q'),
      TRUNC(CAST('2016-02-29 00:00:00' AS TIMESTAMP), 'Q'),
      TRUNC(CAST('2014-01-01 10:30:45' AS TIMESTAMP), 'Q'),
      TRUNC(CAST('2014-01-14 10:30:45' AS TIMESTAMP), 'Q'),
      TRUNC(CAST('2014-01-31 10:30:45' AS TIMESTAMP), 'Q'),
      TRUNC(CAST('2014-02-02 10:30:45' AS TIMESTAMP), 'Q'),
      TRUNC(CAST('2014-02-28 10:30:45' AS TIMESTAMP), 'Q'),
      TRUNC(CAST('2016-02-03 10:30:45' AS TIMESTAMP), 'Q'),
      TRUNC(CAST('2016-02-28 10:30:45' AS TIMESTAMP), 'Q'),
      TRUNC(CAST('2016-02-29 10:30:45' AS TIMESTAMP), 'Q'),
      TRUNC(CAST('2016-05-11 10:30:45' AS TIMESTAMP), 'Q'),
      TRUNC(CAST('2016-07-01 10:30:45' AS TIMESTAMP), 'Q'),
      TRUNC(CAST('2016-12-31 10:30:45' AS TIMESTAMP), 'Q');


SELECT
      TRUNC(CAST('2014-01-01 00:00:00' AS TIMESTAMP), 'Q'),
      TRUNC(CAST('2014-01-14 00:00:00' AS TIMESTAMP), 'Q'),
      TRUNC(CAST('2014-01-31 00:00:00' AS TIMESTAMP), 'Q'),
      TRUNC(CAST('2014-02-02 00:00:00' AS TIMESTAMP), 'Q'),
      TRUNC(CAST('2014-02-28 00:00:00' AS TIMESTAMP), 'Q'),
      TRUNC(CAST('2016-02-03 00:00:00' AS TIMESTAMP), 'Q'),
      TRUNC(CAST('2016-02-28 00:00:00' AS TIMESTAMP), 'Q'),
      TRUNC(CAST('2016-02-29 00:00:00' AS TIMESTAMP), 'Q'),
      TRUNC(CAST('2014-01-01 10:30:45' AS TIMESTAMP), 'Q'),
      TRUNC(CAST('2014-01-14 10:30:45' AS TIMESTAMP), 'Q'),
      TRUNC(CAST('2014-01-31 10:30:45' AS TIMESTAMP), 'Q'),
      TRUNC(CAST('2014-02-02 10:30:45' AS TIMESTAMP), 'Q'),
      TRUNC(CAST('2014-02-28 10:30:45' AS TIMESTAMP), 'Q'),
      TRUNC(CAST('2016-02-03 10:30:45' AS TIMESTAMP), 'Q'),
      TRUNC(CAST('2016-02-28 10:30:45' AS TIMESTAMP), 'Q'),
      TRUNC(CAST('2016-02-29 10:30:45' AS TIMESTAMP), 'Q'),
      TRUNC(CAST('2016-05-11 10:30:45' AS TIMESTAMP), 'Q'),
      TRUNC(CAST('2016-07-01 10:30:45' AS TIMESTAMP), 'Q'),
      TRUNC(CAST('2016-12-31 10:30:45' AS TIMESTAMP), 'Q');

--test timestamp with 'YEAR' as format
EXPLAIN
SELECT
      TRUNC(CAST('2014-01-01 00:00:00' AS TIMESTAMP), 'YEAR'),
      TRUNC(CAST('2014-01-14 00:00:00' AS TIMESTAMP), 'YEAR'),
      TRUNC(CAST('2014-01-31 00:00:00' AS TIMESTAMP), 'YEAR'),
      TRUNC(CAST('2014-02-02 00:00:00' AS TIMESTAMP), 'YEAR'),
      TRUNC(CAST('2014-02-28 00:00:00' AS TIMESTAMP), 'YEAR'),
      TRUNC(CAST('2016-02-03 00:00:00' AS TIMESTAMP), 'YEAR'),
      TRUNC(CAST('2016-02-28 00:00:00' AS TIMESTAMP), 'YEAR'),
      TRUNC(CAST('2016-02-29 00:00:00' AS TIMESTAMP), 'YEAR'),
      TRUNC(CAST('2014-01-01 10:30:45' AS TIMESTAMP), 'YEAR'),
      TRUNC(CAST('2014-01-14 10:30:45' AS TIMESTAMP), 'YEAR'),
      TRUNC(CAST('2014-01-31 10:30:45' AS TIMESTAMP), 'YEAR'),
      TRUNC(CAST('2014-02-02 10:30:45' AS TIMESTAMP), 'YEAR'),
      TRUNC(CAST('2014-02-28 10:30:45' AS TIMESTAMP), 'YEAR'),
      TRUNC(CAST('2016-02-03 10:30:45' AS TIMESTAMP), 'YEAR'),
      TRUNC(CAST('2016-02-28 10:30:45' AS TIMESTAMP), 'YEAR'),
      TRUNC(CAST('2016-02-29 10:30:45' AS TIMESTAMP), 'YEAR');


SELECT
      TRUNC(CAST('2014-01-01 00:00:00' AS TIMESTAMP), 'YEAR'),
      TRUNC(CAST('2014-01-14 00:00:00' AS TIMESTAMP), 'YEAR'),
      TRUNC(CAST('2014-01-31 00:00:00' AS TIMESTAMP), 'YEAR'),
      TRUNC(CAST('2014-02-02 00:00:00' AS TIMESTAMP), 'YEAR'),
      TRUNC(CAST('2014-02-28 00:00:00' AS TIMESTAMP), 'YEAR'),
      TRUNC(CAST('2016-02-03 00:00:00' AS TIMESTAMP), 'YEAR'),
      TRUNC(CAST('2016-02-28 00:00:00' AS TIMESTAMP), 'YEAR'),
      TRUNC(CAST('2016-02-29 00:00:00' AS TIMESTAMP), 'YEAR'),
      TRUNC(CAST('2014-01-01 10:30:45' AS TIMESTAMP), 'YEAR'),
      TRUNC(CAST('2014-01-14 10:30:45' AS TIMESTAMP), 'YEAR'),
      TRUNC(CAST('2014-01-31 10:30:45' AS TIMESTAMP), 'YEAR'),
      TRUNC(CAST('2014-02-02 10:30:45' AS TIMESTAMP), 'YEAR'),
      TRUNC(CAST('2014-02-28 10:30:45' AS TIMESTAMP), 'YEAR'),
      TRUNC(CAST('2016-02-03 10:30:45' AS TIMESTAMP), 'YEAR'),
      TRUNC(CAST('2016-02-28 10:30:45' AS TIMESTAMP), 'YEAR'),
      TRUNC(CAST('2016-02-29 10:30:45' AS TIMESTAMP), 'YEAR');

--test date with 'MM' as format
EXPLAIN
SELECT
      TRUNC(CAST('2014-01-01' AS DATE), 'MM'),
      TRUNC(CAST('2014-01-14' AS DATE), 'MM'),
      TRUNC(CAST('2014-01-31' AS DATE), 'MM'),
      TRUNC(CAST('2014-02-02' AS DATE), 'MM'),
      TRUNC(CAST('2014-02-28' AS DATE), 'MM'),
      TRUNC(CAST('2016-02-03' AS DATE), 'MM'),
      TRUNC(CAST('2016-02-28' AS DATE), 'MM'),
      TRUNC(CAST('2016-02-29' AS DATE), 'MM');


SELECT
      TRUNC(CAST('2014-01-01' AS DATE), 'MM'),
      TRUNC(CAST('2014-01-14' AS DATE), 'MM'),
      TRUNC(CAST('2014-01-31' AS DATE), 'MM'),
      TRUNC(CAST('2014-02-02' AS DATE), 'MM'),
      TRUNC(CAST('2014-02-28' AS DATE), 'MM'),
      TRUNC(CAST('2016-02-03' AS DATE), 'MM'),
      TRUNC(CAST('2016-02-28' AS DATE), 'MM'),
      TRUNC(CAST('2016-02-29' AS DATE), 'MM');

--test date with 'Q' as format
EXPLAIN
SELECT
      TRUNC(CAST('2014-01-01' AS DATE), 'Q'),
      TRUNC(CAST('2014-01-14' AS DATE), 'Q'),
      TRUNC(CAST('2014-01-31' AS DATE), 'Q'),
      TRUNC(CAST('2014-02-02' AS DATE), 'Q'),
      TRUNC(CAST('2014-02-28' AS DATE), 'Q'),
      TRUNC(CAST('2016-02-03' AS DATE), 'Q'),
      TRUNC(CAST('2016-02-28' AS DATE), 'Q'),
      TRUNC(CAST('2016-02-29' AS DATE), 'Q'),
      TRUNC(CAST('2016-05-11' AS DATE), 'Q'),
      TRUNC(CAST('2016-07-01' AS DATE), 'Q'),
      TRUNC(CAST('2016-12-31' AS DATE), 'Q');


SELECT
      TRUNC(CAST('2014-01-01' AS DATE), 'Q'),
      TRUNC(CAST('2014-01-14' AS DATE), 'Q'),
      TRUNC(CAST('2014-01-31' AS DATE), 'Q'),
      TRUNC(CAST('2014-02-02' AS DATE), 'Q'),
      TRUNC(CAST('2014-02-28' AS DATE), 'Q'),
      TRUNC(CAST('2016-02-03' AS DATE), 'Q'),
      TRUNC(CAST('2016-02-28' AS DATE), 'Q'),
      TRUNC(CAST('2016-02-29' AS DATE), 'Q'),
      TRUNC(CAST('2016-05-11' AS DATE), 'Q'),
      TRUNC(CAST('2016-07-01' AS DATE), 'Q'),
      TRUNC(CAST('2016-12-31' AS DATE), 'Q');

--test date with 'YEAR' as format
EXPLAIN
SELECT
      TRUNC(CAST('2014-01-01' AS DATE), 'YEAR'),
      TRUNC(CAST('2014-01-14' AS DATE), 'YEAR'),
      TRUNC(CAST('2014-01-31' AS DATE), 'YEAR'),
      TRUNC(CAST('2014-02-02' AS DATE), 'YEAR'),
      TRUNC(CAST('2014-02-28' AS DATE), 'YEAR'),
      TRUNC(CAST('2016-02-03' AS DATE), 'YEAR'),
      TRUNC(CAST('2016-02-28' AS DATE), 'YEAR'),
      TRUNC(CAST('2016-02-29' AS DATE), 'YEAR');


SELECT
      TRUNC(CAST('2014-01-01' AS DATE), 'YEAR'),
      TRUNC(CAST('2014-01-14' AS DATE), 'YEAR'),
      TRUNC(CAST('2014-01-31' AS DATE), 'YEAR'),
      TRUNC(CAST('2014-02-02' AS DATE), 'YEAR'),
      TRUNC(CAST('2014-02-28' AS DATE), 'YEAR'),
      TRUNC(CAST('2016-02-03' AS DATE), 'YEAR'),
      TRUNC(CAST('2016-02-28' AS DATE), 'YEAR'),
      TRUNC(CAST('2016-02-29' AS DATE), 'YEAR');


--test misc with 'MM' as format
EXPLAIN
SELECT
      TRUNC('2014-01-34', 'MM'),
      TRUNC(CAST(null AS STRING), 'MM'),
      TRUNC(CAST(null AS DATE), 'MM'),
      TRUNC(CAST(null AS TIMESTAMP), 'MM'),
      TRUNC('2014-01-01', 'M'),
      TRUNC('2014-01-01', CAST(null AS STRING));

SELECT
      TRUNC('2014-01-34', 'MM'),
      TRUNC(CAST(null AS STRING), 'MM'),
      TRUNC(CAST(null AS DATE), 'MM'),
      TRUNC(CAST(null AS TIMESTAMP), 'MM'),
      TRUNC('2014-01-01', 'M'),
      TRUNC('2014-01-01', CAST(null AS STRING));


--test misc with 'Q' as format
EXPLAIN
SELECT
      TRUNC('2014-01-34', 'Q'),
      TRUNC(CAST(null AS STRING), 'Q'),
      TRUNC(CAST(null AS DATE), 'Q'),
      TRUNC(CAST(null AS TIMESTAMP), 'Q'),
      TRUNC('2014-01-01', 'Q'),
      TRUNC('2014-01-01', CAST(null AS STRING));

SELECT
      TRUNC('2014-01-34', 'Q'),
      TRUNC(CAST(null AS STRING), 'Q'),
      TRUNC(CAST(null AS DATE), 'Q'),
      TRUNC(CAST(null AS TIMESTAMP), 'Q'),
      TRUNC('2014-01-01', 'Q'),
      TRUNC('2014-01-01', CAST(null AS STRING));


--test misc with 'YEAR' as format
EXPLAIN
SELECT
      TRUNC('2014-01-34', 'YEAR'),
      TRUNC(CAST(null AS STRING), 'YEAR'),
      TRUNC(CAST(null AS DATE), 'YEAR'),
      TRUNC(CAST(null AS TIMESTAMP), 'YEAR'),
      TRUNC('2014-01-01', 'M'),
      TRUNC('2014-01-01', CAST(null AS STRING));

SELECT
      TRUNC('2014-01-34', 'YEAR'),
      TRUNC(CAST(null AS STRING), 'YEAR'),
      TRUNC(CAST(null AS DATE), 'YEAR'),
      TRUNC(CAST(null AS TIMESTAMP), 'YEAR'),
      TRUNC('2014-01-01', 'M'),
      TRUNC('2014-01-01', CAST(null AS STRING));