首页 > 代码库 > Test

Test

2024-07-05 23:30:44   227人阅读

SELECT DATE;
    Date
--------
14/05/24
 
SELECT CURRENT_DATE;
    Date
--------
14/05/24
 
 
SET SESSION DATEFORM=ANSIDATE;       /*ANSI default format (YYYY-MM-DD) */

SET SESSION DATEFORM=INTEGERDATE; /*Teradata default format (YY/MM/DD) */
 
 
INTERVAL -‘32‘ YEAR               (-32 years) [Must use explicit -]
INTERVAL ‘9‘ MONTH                (+9 months) [The default is +]
INTERVAL ‘5-08‘ YEAR TO MONTH     (+5 years, 8 months) [You can‘t use explicit +]
 
INTERVAL - ‘365‘ DAY               (-365 days)
 
 
SELECT DATE + INTERVAL - ‘32‘ YEAR ;
 
(Date+-32)
----------
  82/05/24
 
 
SELECT DATE + INTERVAL ‘5-08‘ YEAR TO MONTH;
 
(Date+ 5-08)
------------
    20/01/24
 
 
 
 
Date Literals
 

Date literals must always be represented using the ANSI standard date format, regardless of the DATEFORM being used by the session.

The ANSI standard Date format is: DATE ‘YYYY-MM-DD‘

You may add the DATE literal to the to any of the following intervals:
 
 
DATE(literal)  +/-  YEAR / YEAR TO MONTH / MONTH / DAY  
 
SELECT DATE ‘2014-05-24‘ + INTERVAL ‘2-05‘ YEAR TO MONTH ;
 
(2014-05-24+ 2-05)
------------------
          16/10/24
 
 
SELECT ADD_MONTHS (DATE ‘2005-12-29‘,2 );
 
ADD_MONTHS(2005-12-29, 2)
-------------------------
               2006-02-28
 
 
SELECT ADD_MONTHS (DATE ‘2003-12-31‘, 2 );
 
 
 
Date Subtraction Yields Days
 
SELECT DATE ‘2007-03-01‘ - DATE ‘2004-01-01‘ ;
 
(2007-03-01-2004-01-01)
-----------------------
                   1155
 
 
SELECT (DATE ‘2007-03-01‘ - DATE ‘2004-01-01‘ ) DAY;
 
 *** Failure 7453 INTERVAL field overflow.
 
This is because the default field length for day is 2 digits and 1155 is 4 digits. In the next example, we submit the query with the output defined as DAY(4)which generates the correct answer.
 
 
SELECT (DATE ‘2007-03-01‘ - DATE ‘2004-01-01‘ ) DAY(4);
 
(2007-03-01 - 2004-01-01) DAY
-----------------------------
                         1155
 
 
 
SELECT (DATE ‘1999-03-01‘ - DATE ‘1899-01-01‘ ) YEAR TO MONTH ;
 
*** Failure 7453 INTERVAL field overflow.
 
 
 
SELECT (DATE ‘1999-03-01‘ - DATE ‘1899-01-01‘ ) YEAR(3) TO MONTH;
 
(1999-03-01 - 1899-01-01) YEAR TO MONTH
---------------------------------------
                                 100-02
 
 
 
SELECT CAST ((DATE‘2004-03-01‘ - DATE‘2004-01-01‘ ) AS INTERVAL MONTH);
 
(2004-03-01-2004-01-01)
-----------------------
                     60
 
SELECT CAST ((INTERVAL ‘3-04‘ YEAR TO MONTH) AS INTERVAL MONTH);
  
 3-04
-----
   40
 
 
Extracting From Date
 
SELECT EXTRACT (YEAR FROM DATE ‘2004-12-15‘)
 
SELECT EXTRACT (YEAR FROM (DATE ‘2004-12-15‘ + INTERVAL ‘20‘ DAY)) ;
 
SELECT EXTRACT (MONTH FROM (DATE ‘2004-12-15‘ + INTERVAL ‘20‘ DAY));
 
SELECT EXTRACT (DAY FROM (DATE ‘2004-12-15‘ + INTERVAL ‘20‘ DAY)) ;
 
 
SELECT INTERVAL ‘03 05:23‘ DAY TO MINUTE + INTERVAL ‘02 03:23‘ DAY TO MINUTE ;
 
SELECT CAST ((TIME ‘09:30:22‘ - INTERVAL ‘01:20:10.45‘ HOUR TO SECOND) AS TIME( 2));
 
SELECT TIME ‘09:30:22‘ + INTERVAL ‘01:20‘ HOUR TO MINUTE ;
 
SELECT (TIME ‘10:35:40‘- TIME ‘08:00:00‘ ) HOUR;
 
 
SELECT CAST ((INTERVAL ‘3:45‘ HOUR TO  MINUTE) AS INTERVAL HOUR);
 
 
 
 
 
 
 
 
 
 
 
 
 
 


联系
我们