Like the DATE data type, the TIME data type is stored internally with hours, minutes, seconds, and optionally, with milliseconds in the ‘HH:MM:SS’ (length 8), and ‘HH:MM:SS.MMMMMM’ (up to length 15) format. A time column must have a valid time value and can not be blank. Valid values are from ‘00:00:000000′ to ‘23:59:59.999999′. If the column is defined as allowing NULLs, then NULL should be used for a missing time.

 

Example:    COL1 TIME

Sample value is: ‘17:39.45′

 

Notice that the keyword, TIME, is a complete definition of the data type and has no value defined in parentheses.
See Sample Data Type Values.

TIME WITHOUT TIME ZONE is the same as TIME. In this course, we use TIME.

With DATE, you no longer have to worry about one date field being defined as YYMMDD and another as MMDDYY or MM/DD/YY, and whether they are stored in binary, packed decimal, display, or alphanumeric format. All SQL DATEs are stored the same way internally, so that any DATE column can be compared to any other DATE column.

SQL DATEs can be entered or displayed in several formats, but it is stores internally so that it includes the century, year, month, and day such as ‘CCYY-MM-DD’ (with a length of 10).

A DATE column must have a valid date and can not be blank. If the columns are defined as allowing nulls, then NULL should be used for a missing date. Valid values are from ‘0001-01-01′ to ‘9999-12-31′.

 

Example:    COL1 DATE

 

Notice that the keyword, DATE, is a complete definition of the data type and has no value defined in parentheses.
See Sample Data Type Values.

Sample value is: ‘2056-02-20′

Date/Time data is coded by the RDBMS vendors as follows:

Date, time, and timestamp data is represented by the following:

> The SQL standard requires that the date, time, and timestamp are maintained internally based on Universal Coordinated Time (UCT) which used to be called Greenwich Mean Time (GMT). Then an offset is applied for your location. For example, New York has an offset of -5, Dallas has -6, Denver has -7, and Los Angeles has -8.
These change for daylight savings time in the U.S.
> The date is based on the rules of the Gregorian calendar. A hyphen (-) separates the parts of the date. The standard SQL date has the keyword DATE ahead of the date literal, such as DATE ‘2056-02-20′.
> The time is based on a 24-hour clock, just like military time, so 17:00 is the same as 5:00 PM. A colon (:) separates the parts of time such as TIME ‘17:30:45′.
> A timestamp is a combination of date and time such as TIMESTAMP ‘2056-02-20 17:30:45′.
> A space is used to separate date and time when both are present.
> You can compare two DATE, TIME, or TIMESTAMP values if they are of the same format.
> SQL can not handle dates that are Before the Common Era (BCE) or Before Christ (BC).

> Access surrounds date-time literals with the pound sign (#), so that the standard SQL date
DATE ‘2056-02-20′ is the same as the Microsoft Access’ #2056-02-20#.
> DB2 & SQL Server omit the key word DATE, TIME, and TIMESTAMP ahead of the literal.
> The external representation may be set to ISO, USA, EUR, or JIS. We use ISO in our examples.

In DB2 the LONG VARGRAPHIC data type can have 0 to 16,357 characters of 16-bit Double Byte Character Set data in variable length format for 32K pages, or 2,028 DBCS characters for 4K pages of storage. With DBCS, each 2 bytes of data represents a graphic character. This data type is also rarely used. Consult your RDBMS systems manual for further details.

Example:    COL1 LONG VARGRAPHIC(1000)

COL1 has up to 1000 bytes of up to 500 variable double-byte characters.

In COBOL:  05  COL1-GROUP.
               49 COL1-LENGTH   PIC S9(9) COMP.
               49 COL1          PIC G(1000) DISPLAY-1.

In COBOL, G(1000) means 2,000 bytes of 1,000 DBCS characters.

In DB2 the VARGRAPHIC data type can have 0 to 127 characters of 16-bit Double Byte Character Set data in variable length format. With DBCS, each two bytes of data represents a graphic character. This data type is also rarely used. Consult your RDBMS systems manual for further details.

Example:    COL1 VARGRAPHIC(50)

COL1 has up to 100 bytes of up to 50 variable double-byte characters.

In COBOL:  05  COL1-GROUP.
               49 COL1-LENGTH   PIC S9(4) COMP.
               49 COL1          PIC G(50) DISPLAY-1.

In COBOL, G(50) means 100 bytes of 50 DBCS characters.

Oracle’s VARCHAR2(length) has a maximum length of 2000.

In DB2 the GRAPHIC data type can have up to 127 characters of 16-bit Double Byte Character Set data. With DBCS, each 2 bytes of data represents a graphic character. This data type is rarely used. Consult your RDBMS systems manual for further details.

 

Example:    COL1 GRAPHIC(50)

COL1 has 100 bytes of 50 double byte characters.

Binary Large OBject (BLOB) data is coded by the RDBMS vendors as follows:

Binary Large Object Data is represented by the following:

> Unlike a CLOB, which stores a long character string, a BLOB stores a long bit string. These two data types are not interchangable.

> BLOBs are used to store large multimedia data such as digitized pictures, medical photos, 3D graphics, videos, audios, audio/visual movies, data recordings, etc.

> BLOBs can be either fixed in length or variable in length.

 
> A BLOB can not be used as a PRIMARY KEY, FOREIGN KEY, or UNIQUE predicate.
    A BLOB can not be used in any comparison other than equal or not equal.
    You also can not use BLOBs with DISTINCT, GROUP BY, or ORDER BY.

> RDBMS systems don’t try to interpret BLOBs, that is left to you and your application.

> SQL:1999 introduced BLOBs as a data type.

Computer systems use internal registers to performs math functions.
These registers are typically 32-bit, 64-bit, 128-bit, or 256-bit in length.
DOUBLE PRECISION gives a double-precision floating-point number (the size of two registers).

> The actual precision of DOUBLE PRECISION depends on the RDBMS vendor’s implementation.

> Since it is a floating-point number expressed in scientific notation, it is an approximate number and may not be accurate to the last digit.

> It can be negative, zero, or positive.

> If you have a choice between exact (DECIMAL or NUMERIC) or approximate (FLOAT, REAL, or DOUBLE PRECISION), use the exact data type because you get completely accurate numbers and it processes faster.

> SQL:2003 introduced REAL, DOUBLE PRECISION, and FLOAT(precisionbits) data types.
> Approximate data type should be used only for very extreme ranges of numeric values.