ANSI Date and Time

ANSI Time and Dates:

1)       Traditionally the Date format is ‘YY/MM/DD’.

2)     ANSI format of Date is ‘YYYY-MM-DD’.

3)     Teradata makes this option selectable using the SET SESSION DATEFORM option.

What does DATEFORM change?

1)       This controls how the date is displayed in reports etc.

2)     Importing and exporting strings. Ex: if ANSIDATE is selected and we import a character string of format ‘YYYY-MM-DD’ then it can be directly placed inside the date field. Similarly character string of format ‘YY/MM/DD’ can be directly inserted to a date field if date format is INTEGERDATE (,i.e. Teradata default). Otherwise we need to perform conversion before inserting into date field.

(Thus an imported character string representing a date will be assumed to have the default DATEFORM setting, unless otherwise indicated.)

Example:

CREATE TABLE tbla

(tbldate DATE);

SET SESSION DATEFORM=ANSIDATE;

         :
         :
USING newdate (CHAR(10))

INSERT INTO tbla (:newdate);

Here we can see that the character field of format ‘YYYY-MM-DD’ from the external file can be directly inserted to date field, because the date format is ANSIDATE .ie YYYY-MM-DD. Same thing is true in case of mload and fastload.

What DATEFORM does not change?

This does not change the way Teradata stores date data types.

Irrespective of date format, the date is always stored as integer ( 4 bytes).

How to change the DATEFORM during a session?

The SET SESSION DATEFORM command sets the default date format for your session. The options available are:

SET SESSION DATEFORM=ANSIDATE;  /*ANSI Default format*/

SET SESSION DATEFORM=INTEGERDATE;/'Teradata default*/

The SET command is an SQL command, not a BTEQ command, thus no period precedes it.

The SET SESSION DATEFORM command can be executed at any time during a session. It is switchable during a session. ( some other settings need to be done before logging in)

What are 3 different places where DATEFORM can be set?

The DATEFORM can be set at following 3 places:

1)       It could be set in DBSCONTROL record. This will become the system default. This can be done by setting register 14 in the DBACONTROL record.

MODIFY GENERAL 14=0 /* INTEGERDATE (YY/MM/DD)*/

MODIFY GENERAL 14=1 /* ANSIDATE (YYYY-MM-DD)*/

2)     It can also be set as part of defining the user. This will become default setting for the user.

CREATE USER ped........

       DATEFORM=ANSIDATE

               =INTEGERDATE

3)     It can be set during the session as shown above. This setting will be active till the user logs off.

SET SESSION DATEFORM=ANSIDATE

                    =INTEGERDATE

INTERVAL data types:

1.        Interval data types represent a displacement between two points in time.

2.      Intervals are stored internally as one or multiple numeric fields combined into a single data type.

3.      The two general interval types are:

Ø Year-Month Intervals

Ø Day-Time Intervals

YEAR-MONTH intervals include:

Ø YEAR

Ø MONTH

Ø YEAR TO MONTH

DAY –TIME intervals include:

Ø DAY

Ø HOUR

Ø MINUTE

Ø SECONDS.

Ø DAY TO HOUR

Ø DAY TO MINUTE

Ø DAY TO SECOND

Ø HOUR TO MINUTE

Ø HOUR TO SECOND

Ø MINUTE TO SECOND

4.      Year-month intervals can be combined with DATE data type.  Only “DAY” interval type from the DAY-TIME intervals can be combined with DATE datatype. Thus interval types that can be combined with DATE are DAY,MONTH,YEAR,YEAR TO MONTH

5.      Intervals may be converted from one type to another using the CAST feature of SQL, however conversions are only possible within a general category. .ie A YEAR interval may be converted to months, but it may not be converted to days or hours.

6.      Year-Month intervals sizes are in range 1-4. Default value is 2. This means MONTH interval type means MONTH(2).

Ø INTERVAL YEAR(n) -9999 to +9999 years

Ø INTERVAL MONTH(n) -9999 to +9999 months

Ø INTERVAL YEAR(n) TO MONTH -9999 to 9999 years and 0-12 months

Where n is in the range of 1-4.

All these interval data types are internally stored as SMALLINT. YEAR TO MONTH uses two small ints( one for year and other for month.)

7.       If the default size of 2 is not sufficient to hold the interval data then we should increase the size to max 4 .

8.      Intervals may be represented as literals in SQL scripts as follows.

Example:

INTERVAL -'32' YEAR

INTERVAL '9' MONTH

INTERVAL '5-08' YEAR TO MONTH

Note that interval literal is specified as word “INTERVAL” followed by the value in quotes and then followed by the interval type.

For YEAR TO MONTH don’t forget to use the “–“

Additional rules for using interval literals are:

Ø Interval if negative must have negative sign outside the quotes.

Ø The plus sign '+' is the default, represented by a blank.

Ø Use of an explicit '+' generates an error.

Ø Size (n) is never specified with literals - the size is implicit in the interval.

Combining DATE with intervals:

1.        Date data types may be combined with certain interval types(YEAR,MONTH,YEAR TO MONTH and DAY) to form a different date. Only these 4 interval types can be used with Dates.

Date+INTERVAL = DATE.

2.      Intervals may be combined with certain interval types to form a different iinterval.

INTERVAL + INTERVAL = INTERVAL.

3.      Dates may be subtracted from other dates to yield some interval of time between them.

DATE  - DATE = interval.

4.      Intervals may also have mathematical operations performed on them such as division and multiplication. This will yield a new interval.

INTERVAL */ n = INTERVAL

5.      Examples:

SELECT CURRENT_DATE + INTERVAL '3' YEAR;

       2001-11-06

SELECT CURRENT_DATE + INTERVAL - '3' YEAR;

       1995-11-06

(Note the negative literal is specified inside quotes and the sign is outside the quotes).

SELECT (INTERVAL '5-10' YEAR TO MONTH)+  (INTERVAL '2-03' YEAR TO MONTH);

       8-01

When multiple intervals are used in same query then it’s advised to enclose them in brackets.

Working with Date literals:

1.        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'

2.      Just as with date data type, date literal we can added with YEAR, MONTH, YEAR TO MONTH and DAY interval types.

SELECT DATE '1999-07-15'+ INTERVAL '2-03' YEAR TO MONTH;

        2001-10-15

SELECT (990715(DATE)) - INTERVAL '2' YEAR;

1997-07-15 /*here we converted integer to DATE*/

3.      Note that when adding a month literal to a date, the same day of the new month will be the reported result. However not all days exist in all months. The operation will fail if the same day does not exist in the new month.

SELECT (DATE '1998-12-29'+ INTERVAL '2' MONTH);

***Failure 2665 invalid date.

(Note: There is no Feb. 29 in the year 1999)

The appropriate mechanism for handling this situation is the ADD_MONTHS function. This function allows any day of any month to be mapped to a day of the new month. The ADD_MONTHS function takes into account that the month of February has no day 30, 31 or even 29 in some years. The ADD_MONTHS function should be used when each day must have a mapping in the new month.

SELECT ADD_MONTHS(DATE '1998-12-29',2);

ADD_MONTHS(1998-12-29, 2)

               1999-02-28

Date Subtraction:

1.        Subtraction of Dates leads to integer which is equal to number of days in between the two dates.

Ex: SELECT DATE '1999-03-01'- DATE '1999-01-01';

                        59

This gives the number of days in between two days. Note that this output is of data type INTEGER.

2.      If we need to have result in DAY interval type then we have to write as follows:

Ex: SELECT (DATE '1999-03-01' - DATE '1999-01-01') DAY;

                           59

This result is an INTERVAL DAY (2) data type.

3.      Examples:

Ex: SELECT (DATE '1999-03-01' - DATE '1996-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.

This can be fixed by changing the output data type to DAY(4) . 4 is the maximum length.

Ex: SELECT (DATE '1999-03-01' - DATE '1996-01-01') DAY(4);

                          1155

Result is an INTERVAL DAY (4) data type.

Ex: SELECT DATE '1999-03-01' - DATE '1996-01-01';

This would give 1155 value , but it would be of data type interval.

Ex: SELECT DATE '1999-03-01' - DATE '1898-01-01';

                      36948

Result is an INTEGER representing number of days. It cannot be represented as INTERVAL because the maximum field length for the days interval is 4 digits, and this number is 5 digits.

Ex: SELECT (DATE '1999-03-01'- DATE '1899-01-01') YEAR TO MONTH;

***Failure 7453 Interval field overflow

This fails because the default precision is 2 and this answer requires 3

Ex: SELECT (DATE '1999-03-01' - DATE '1899-01-01') YEAR(3) TO MONTH;

                                     100-02

Ex: SELECT (DATE '1999-03-01' - DATE '1899-01-01') MONTH(4);

                               1202

VIMP: Intervals should have the word “INTERVAL” associated with them

Year-Month Interval Conversions and Calculations

1.        You can CAST any of these types AS any of these types:

CAST

YEAR YEAR TO MONTH MONTH

AS

YEAR YEAR TO MONTH MONTH

2.      Within the year-month category an interval may be recast as any other interval.

3.      Mathematical operations such as division and multiplication may be used with interval data types to calculate fractional portions of intervals or to apply a factor or multiple intervals to a date

Example:

Ex: SELECT CAST ((DATE'1999-03-01'- DATE'1999-01-01') AS INTERVAL MONTH);

                    59

The result is an integer CAST as months.

Ex: SELECT (DATE '1999-03-01' - DATE '1999-01-01') MONTH;

                               2

The result is an interval of months.

SELECT CAST ((INTERVAL '3-04' YEAR TO MONTH) AS INTERVAL MONTH);

  40

3 years+4 months = 40 months.

SELECT CAST ((INTERVAL '3-07' YEAR TO MONTH) AS INTERVAL YEAR);

   3

As we are converting to interval YEAR, 7 months get truncated.

 SELECT INTERVAL '10-02' YEAR TO MONTH/3;

Gives 3-04

10 years 2 months = 122 months

 122 months/3 = 40.67 months
40.67 months rounds to 40 months
40 months = 3 years 4 months

Extracting From Date (EXTRACT function):

1.        Extracting of year, month and day from a date data type has been a feature of Teradata since V2R2.

2.      Now, we may combine a date with an interval and extract the needed portion from the result

3.      Syntax:

EXTRACT YEAR/MONTH/DAY from Date;

SELECT EXTRACT (MONTH FROM (DATE '1999-12-15' + INTERVAL '20' DAY));

Ans : 1

SELECT EXTRACT (DAY FROM (DATE '1999-12-15'+ INTERVAL '20' DAY));

Ans: 4

ANSI time:

1.        TIME is a new data type. This came in to picture since V2R3.

2.      Prior to this, keyword TIME was selectable and returned an integer representing the time of day in format HHMMSS. This was not a data type and had no real clock intelligence for purposes of calculations.

3.      However the TIME datatype has an intelligent clock in its implementation, just as DATE has calendar intelligence.

4.      It is a single column which in reality carries three different fields of information.

5.      TIME data type supports precision in seconds up to six places.

TIME(n) - When n=0-6 (default is 6) HH:MM:SS.nnnnnn
HH - stored as byteint (1 byte)
MM - stored as byteint (1 byte)
SS - stored as dec(8,6)(4 bytes)

6.      Time may be represented as a literal. As with dates, the character string must conform properly to the TIME data type, and the exact degree of precision will be determined by the parser as it scans the literal.

So if we use the literal TIME ’22:32:12.970000’ will be determined to be a TIME(6) data type.

7.       Irrespective of precision, TIME data type requires total 6 bytes of storage.

8.      IF we want to convert time data type to characters then the number of characters needed depend upon the precision used:

TIME (0) - 11:37:58                  CHAR(8)

TIME (6) - 11:37:58.213000     CHAR(15)

Time subtraction:

1.        Time subtraction will produce a time interval result as specified in the SQL statement.

2.      If a result is to be represented in hours, any minutes or seconds in the remainder will be omitted, as no attempt at rounding takes place

3.      With dates when we do not specify there output interval type, output is a integer. However with Time subtraction if no time interval type is specified for the result, an error will be reported.

4.      Time  subtraction can result in output intervals HOUR,HOUR TO MINUTE,HOUR TO SECOND,MINUTE,MINUTE TO SECOND,SECOND.

Ex: SELECT (TIME '10:35:40'- TIME '08:00:00') HOUR;

Ans: 2

Ex: SELECT (TIME '10:35:40'- TIME '08:00:00') MINUTE;

***Failure 7453 interval field overflow.

Ex: SELECT (TIME '10:35:40'-TIME '08:00:00') MINUTE(3);

155

Ex: SELECT (TIME '23:59:59.99'- CURRENT_TIME) HOUR;

6

Ex: SELECT (TIME '23:59:59.99'- CURRENT_TIME) HOUR TO MINUTE;

6:24

Note the use of brackets.

Simple DAY-TIME interval:

1.        Simple day-time intervals are those with only one unit of measurement.'

2.      Complex data types have two or more units of measure.

3.      All simple day-time intervals (DAY, HOUR, MINUTE, SECOND) support up to four digits for representing the unit, either plus or minus.

Additionally, the SECOND interval may have up to six places of precision.

4.      You cannot convert Year/Month intervals to Day/Time intervals.

5.      Day-time interval syntax:

INTERVAL DAY(n)      (-9999 to +9999 days)
INTERVAL HOUR(n)     (-9999 to +9999 hours)
INTERVAL MINUTE(n)   (-9999 to +9999 minutes)
INTERVAL SECOND(n,m) (9999.999999 seconds)
INTERVAL DAY(n)TO HOUR(n)
INTERVAL DAY(n) TO MINUTE(n)
INTERVAL DAY(n) TO SECOND(n,m)
INTERVAL HOUR(n) TO MINUTE(n)
INTERVAL HOUR(n) TO SECOND(n,m)
INTERVAL MINUTE(n) TO SECOND(n,m)

Example:

INTERVAL DAY(n)    (-9999 to +9999 days, where n=1-4)
INTERVAL '320' DAY             (320 days)
INTERVAL -'9' HOUR             (-9 hours)
INTERVAL '700' MINUTE          (700 minutes)
INTERVAL '1375.904508' SECOND (1375.904508 seconds)

All Day-time intervals are stored as SMALLINT with range (-9999 to 9999) and seconds part have datatype of dec(8,6) with range -9999.999999 to 9999.999999.

Complex DAY-TIME interval:

Complex day-time intervals are combinations of two or more simple day-time intervals. Their representation as character strings is a bit more complex because of the varieties of syntax possible. The parser will scan a complex day-time literal to see if it conforms to the expected syntax, then assign a working interval data type to it. Otherwise an error is generated.

INTERVAL '25 09' DAY TO HOUR
INTERVAL -'9 13:36' DAY TO MINUTE
INTERVAL '25 13:36:15.5' DAY TO SECOND
INTERVAL '13:36' HOUR TO MINUTE
INTERVAL '13:36:15.5' HOUR TO SECOND
INTERVAL '36:15.5' MINUTE TO SECOND

Combining Time intervals with Time literal.

1.        Time literals may be combined with time intervals to produce a new time.

2.      The resulting time will become the same time data type as the starting time, regardless of the interval type.

If changing the resulting time to a different time data type is desired, the result must be CAST to the new time data type.

3.      TIME data type can be combined with HOUR/HOUR TO MINUTE/HOUT TO SECOND/MINUTE/MINUTE TO SECOND/SECOND.( and not with DAY/MONTH/YEAR /YEAR TO MONTH)

Ex: SELECT TIME '09:30:22'  - INTERVAL '01:20:00' HOUR TO SECOND;

08:10:22

SELECT TIME '09:30:22.45'  - INTERVAL '01:20:10' HOUR TO SECOND;

  08:10:12.45

(result will have same time datatype. Ie precision 2)

SELECT TIME '09:30:22'  - INTERVAL '01:20:10.45' HOUR TO SECOND;

            08:10:11

Result datatype and precision does not depend upon the interval time.

SELECT CAST((TIME '09:30:22' - INTERVAL '01:20:10.45' HOUR TO SECOND)AS TIME(2));

08:10:11.55

We can cast if we want to change to a different time data type.

CASTING Day-Time interval conversions and calculations:

Conversion between different types of day-time interval types is easily accomplished using the CAST feature of SQL.

Conversions which include a remainder which is not supported by the conversion interval type will be dropped, not rounded. Thus, converting 47 hours into days will yield only one day.

Attention must be paid to the appropriate number of digits specified in the conversion interval type.

You can CAST any of the values in the left column AS any of the values in the right column.

CAST

DAY DAY TO HOUR DAY TO MINUTE DAY TO SECOND HOUR HOUR TO MINUTE HOUR TO SECOND MINUTE MINUTE TO SECOND SECOND

AS

DAY DAY TO HOUR DAY TO MINUTE DAY TO SECOND HOUR HOUR TO MINUTE HOUR TO SECOND MINUTE MINUTE TO SECOND SECOND

SELECT CAST ((INTERVAL '3:45' HOUR TO  MINUTE) AS INTERVAL HOUR);

Ans: 3

SELECT CAST ((INTERAL '3:45' HOUR TO MINUTE) AS INTERVAL MINUTE);

Ans: ***Failure 7453 Interval field overflow.

SELECT CAST ((INTERVAL '3:45' HOUR TO MINUTE) AS INTERVAL MINUTE(3));

Ans: 225

EXRACT function for time:

The number of hours, minutes or seconds may be extracted from a TIME data type by using the EXTRACT feature of SQL. These units may be extracted either from a time data type or from a calculation resulting in a time data type.

EXTRACT (HOUR(MINUTE(SECOND FROM  TIME)