1. Data Types#

In order to use SQL to store, change, and query the data in a database, it is first necessary to possess a thorough understanding of the available data types. This chapter presents a detailed explanation of the data types supported in Altibase.

Overview#

Data Type Overview#

The following data types are supported in Altibase:

Character Data Types#

Type	Length	Size
M : Defined column length L : The length of the input string
CHAR(M)	1 ~ 32000	M + 2
VARCHAR(M)	1 ~ 32000	length + 2 where length = L if the input value is stored in a variable area length = M if the input value is stored in a fixed area
NCHAR(M)	1 ~ 16000(UTF16) 1 ~ 10666(UTF8)	M2 + 2(UTF16) M3 + 2(UTF8)
NVARCHAR(M)	1 ~ 16000(UTF16) 1 ~ 10666(UTF8)	length2 + 2(UTF16) length3 + 2(UTF8) where: length = L if the input value is stored in a variable area length = M if the input value is stored in a fixed area

NCHAR and NVARCHAR are Unicode character types. The available maximum length of a UTF16-encoded string is different from that of a UTF8-encoded string.

Numerical Data Types#

Non-native	Type	Precision	Scale	Size (bytes)	Remarks
	NUMERIC	38	0	3+((precision)+2)/2	Fixed-Point Numbers The NUMERIC data type is the same as the DECIMAL datatype.
	NUMERIC(p)	1 ~ 38	0
	NUMERIC(p,s)	1 ~ 38	-84 ~ 128
	DECIMAL	38	0
	DECIMAL(p)	1 ~ 38	0
	DECIMAL(p,s)	1 ~ 38	-84 ~ 128
	NUMBER(p)	1 ~ 38	0
	NUMBER(p,s)	1 ~ 38	-84 ~ 128
	NUMBER	38	X	3+((precision)+2)/2	* Floating-Point Numbers
	FLOAT	38	X
	FLOAT(p)	1 ~ 38	X
Native	Type	Compatible C Type		Size(bytes)	Remarks
	DOUBLE	double		8	*Floating-Point Numbers
	REAL	float		4	*Floating-Point Numbers
	BIGINT	long or long long		8	*Integer Type
	INTEGER	int		4
	SMALLINT	short		2

Example 1#

Fixed-Point Numbers Size Calculation:

( 3 + ( ( p ) + 2 ) / 2 )

- NUMERIC  
  NUMERIC(38, 0): Size = 3 + 40/2 = 23 bytes

- NUMERIC(p) / NUMERIC(p, 0)  
  NUMERIC(10): Size = 3 + 12/2 = 9 bytes

- NUMERIC(p, s)  
  NUMERIC(10, 9): Size = 3 + 12/2 = 9 bytes

- DECIMAL: the same as NUMERIC
— DECIMAL(p): the same as NUMERIC(p)
— DECIMAL(p,s): the same as NUMERIC(p,s)
— NUMBER(p): the same as NUMERIC(p)
— NUMBER(p,s): the same as NUMERIC(p,s)

Example 2#

Floating-Point Numbers Size Calculation: ( 3 + ( ( p ) + 2 ) / 2 )

- FLOAT  
  FLOAT(38): Size = 3 + 40/2 = 23 bytes

- FLOAT(p)  
  FLOAT(20): Size = 3 + 22/2 = 14 bytes

- NUMBER: the same as FLOAT

Date Data Type#

Type	Size (byte)
DATE	8

Binary Data Types#

Type	Length	Size
M : Defined column length L : The length of the input value
BLOB/CLOB		1 ~ 4294967295
BYTE	1 ~ 32000	M + 2
VARBYTE	1 ~ 32000	length + 2 where length = L if the input value is stored in a variable area length = M if the input value is stored in a fixed area
NIBBLE	1 ~ 254	M/2 + 1
BIT	1 ~ 64000	M/8 + 4
VARBIT	1 ~ 64000	length/8 + 4 where length = L if the input value is stored in a variable area length = M if the input value is stored in a fixed area

Geometry Data Type#

Type	Length	Size (byte)
GEOMETRY	8\~104857600	length + 40

The actual record size is the size of each data type as indicated above, plus the size of header information. The size of the header information varies depending on the OS.

NULL#

When a row is inserted into a table, the value of a column is set to NULL if the value for that column is not known or has not been determined yet. In other words, NULL indicates that no value exists. Therefore, NULL is not the same as 0 (zero) or blank space, and is handled differently when performing comparison operations or saving data.

If any operation other than the NVL() function or the IS NULL or IS NOT NULL conditions is performed on a NULL value, the final result of the formula containing the operation will be NULL. In other words, comparisons and operations are meaningless when performed on NULL values.

NULL can appear in columns of any data type, as long as they are not restricted by NOT NULL or PRIMARY KEY constraints.

Data Type Conversion#

The data type conversions that are possible are shown in matrix form in the following table.

When a comparison operation is to be performed on two values having the same data type, the comparison operation is performed on the values directly without any prior conversion. In contrast, when a comparison operation is to be performed on two values having different data types, the comparison is performed after one of the values is converted into the same type as the other value. Note however that when comparisons are performed, character data types are always converted into the data type of the other comparison operand, not the other way around

The following table shows convertible matrices of implicit data types (O: shows that attributes are retained even when data types are converted). For more detailed information on how to convert an existing table's data type to MODIFY, please refer to modify_column_clause statement in the SQL Reference.

After Before	char	var char	nchar	nvarchar	clob	big int	deci mal	dou ble	float	int eger	num ber	num eric	real	small int	date	blob	byte	varbyte	nibble	bit	varbit	geometry
char	o	o	o	o		o	o	o	o	o	o	o	o	o	o
varchar	o	o	o	o	o	o	o	o	o	o	o	o	o	o	o
nchar	o	o	o	o		o	o	o	o	o	o	o	o	o	o
nvarchar	o	o	o	o	o	o	o	o	o	o	o	o	o	o	o
clob					o
bigint	o	o	o	o		o	o	o	o	o	o	o	o	o
decimal	o	o	o	o		o	o	o	o	o	o	o	o	o
double	o	o	o	o		o	o	o	o	o	o	o	o	o
float	o	o	o	o		o	o	o	o	o	o	o	o	o
integer	o	o	o	o		o	o	o	o	o	o	o	o	o
number	o	o	o	o		o	o	o	o	o	o	o	o	o
numeric	o	o	o	o		o	o	o	o	o	o	o	o	o
real	o	o	o	o		o	o	o	o	o	o	o	o	o
smallint	o	o	o	o		o	o	o	o	o	o	o	o	o
date	o	o	o	o											o
blob																o
byte																o	o	o
varbyte																o	o	o
nibble																			o
bit																				o	o
varbit		o																		o	o
geometry																						o

Implicit Data Type Conversion#

If '1000' of bit type is entered into table t10 table, the conversion succeeds to integer '1000', but it is not an implicit data type conversion because the attribute of data type is changed.

iSQL> create table t10 (i1 integer);
Create success.
iSQL> insert into t10 values (bit'1000');
1 row inserted.
iSQL> select * from t10;
I1
--------------
1000
1 row selected.

Therefore, implicit data type conversion follows the rules below:

When comparing numeric or character data types or arithmetic operations, the character data types are converted to numeric data types.
When comparing the date data type with the character data type, the character data type is converted to the date data type and the comparison operation is performed.
Operation that cannot convert data types are invalidated.
The argument used in the function is converted to the data type of the argument defined in the function.
If a character data type or numeric data type that uses decimal precisions to a floating-point data type that uses binary precision is converted
When executing an INSERT or UPDATE, the data type is converted to the data type of the INSERT and UPDATE columns.

Example#

\ When comparing numeric data types, character data type '10' is converted to numeric data.

iSQL> create table emp (empno integer, name varchar(10), hire_date date);
insert into emp values (10,'altibase', '10-nov-2015');

iSQL> select name from emp where empno = '10';
NAME
--------------
altibase
1 row selected.

\ When arithmetic between numeric data type and character data type, character data type '10' is converted to numeric data type.

iSQL> select empno + '10' from emp;
EMPNO+'10'
-------------------------
20
1 row selected.

\ When comparing a date date type with a character data type, the character data type '10-nov-2015' is converted to a date data type.

iSQL> select hire_date from emp where hire_date = '10-nov-2015';
HIRE_DATE
---------------
10-NOV-2015
1 row selected.

\ When arithmetic between numeric data type and character type is performed, binary data type cannot be converted to numeric data type and operation is invalidated.

iSQL> select empno + cast(12345 as nibble(6)) from emp;
[ERR-2100C : Conversion not applicable.
0001 : select EMPNO + CAST(12345 as NIBBLE(6)) from EMP
             ^                               ^
]

\ When the function SUM receives the character data type '10' as an argument, it is converted.

iSQL> select sum('10') from dual;
SUM('10')
--------------
10
1 row selected.

\ When the character data type '12.123456789' is converted to a floating-point numeric data type, the number of significant digits becomes float (11), which causes a loss of value.

iSQL> select float'12.123456789' from dual;
FLOAT'12.123456789'
----------------------
12.1234568
1 row selected.

\<Query> The value of numeric data to be inserted is converted according to the data type of the column to be inserted and the value is INSERT.

iSQL>  create table t1 ( i1 char(10), i2 integer, i3 double);
Create success.
iSQL>  insert into t1 values (integer'1020', char'1928', float'123.1234');
1 row inserted.
iSQL>     select * from t1;
I1          I2          I3
---------------------------------------------------
1020        1928        123.1234
1 row selected.

Explicit Data Type Conversion#

Data type conversion can be explicitly performed using SQL conversion functions or by type-casting, as shown below. The SQL functions that are used to explicitly convert a value from one data type to another are explained in the SQL Reference.

Syntax#

datatype 'string or constant literal '

Description#

Explicitly converts a numeric value from on data type to another. In the following example, the number 157.27 is converted to the characters "157.27".

CHAR '157.27'

Using the Character Strings#

Single quotation mark should be used when displaying character strings in a SQL query. Since the single quotation mark becomes an escape letter when displaying a single quotation mark ('), the single quotation mark should be used in font.

Example#

SELECT * FROM EMPLOYEE WHERE NAME = ’KIM’;
INSERT INTO EMPLOYEE VALUES (‘GILDONG’’’);//Insert the value GILDONG'
SELECT * FROM REMOTE_TABLE(link1, ‘SELECT * FROM EMPLOYEE WHERE NAME=‘’KIM‘’’; //''are not the double quotation mark, it is two single quotation marks

FIXED/VARIABLE Option#

FIXED or VARIABLE specifies where the data in a column will be stored.

When an entire record is stored in a contiguous space, this is called a 'FIXED' area. When one of the columns is stored in a separated space, rather than being stored in the fixed area contiguous with the rest of the record, this column is said to be stored in a 'VARIABLE' area.

When a column is stored in a variable area, the header information for the column, such as the length of the data and the pointer to the actual data, is stored in the fixed area, whereas the data for that column are stored in the variable area.

When a table is created in a disk tablespace, whether the user specifies FIXED or VARIABLE is ignored, and all columns in the table are treated as FIXED. However, when a table is created in a memory table space, the user-specified value is used.

However, the exception to this is that all LOB data type columns are always treated as VARIABLE, and the data can thus be stored in a fixed or variable area depending on the value specified using the IN ROW clause.

The following data types can be specified as VARIABLE: CHAR, VARCHAR, NCHAR, NVARCHAR, BYTE, VARBATE, NIBBLE, BIT, VARBIT, BLOB, and CLOB

IN ROW Clause#

This clause pertains only to column data that are to be stored in a variable area. If the FIXED and IN ROW clause are both specified when a table is created, the IN ROW clause is ignored. When data are entered into a VARIABLE column, if the length of the data is less than or equal to the value specified using the IN ROW clause, the data will be stored in the fixed area, whereas if the data length is greater than the value specified using the IN ROW clause, the data will be stored in the variable area.

Here, "data length" does not mean the length of the input data, but the length of the data to be stored in memory or on disk, which will be somewhat larger. For example, when a column is defined as "VARCHAR(400) in row 200", data will be inserted into the fixed area if the length of the data that is input is smaller than or equal to 198, because 2 additional bytes are required when storing the data.

The default size of lob data stored in the fixed area can be specified using the MEMORY_LOB_COLUMN_IN_ROW_SIZE property for memory tables and the DISK_LOB_COLUMN_IN_ROW_SIZE for disk tables. Additionally, the default size for columns containing other types of data with the VARIABLE option can be specified using the MEMORY_VARIABLE_COLUMN_IN_ROW_SIZE property.

Setting these properties eliminates the need to use the IN ROW clause repeatedly for each column when creating a table. For more detailed information about these properties, please refer to chapter 2.

Character Data Types#

Character data types are used to store character (alphanumeric) data, meaning words or free-form text, in either the database character set or the national character set.

In Altibase, character data typers comprise the following types:

CHAR
VARCHAR
NCHAR
NVARCHAR

CHAR#

Syntax Diagram#

Syntax#

CHAR [(size)] [ FIXED | VARIABLE [IN ROW size] ]

Description#

This is a character data type that has a fixed length equal to the specified size. If an input value is shorter than the specified size, the remaining area is filled with blank spaces.

The default size of CHAR column is 1byte, and its maximum length is 32000 bytes.

For more detailed information about the FIXED and VARIABLE clauses, please refer to the "FIXED/VARIABLE OPTIONS" and "IN ROW clauses" section above.

VARCHAR#

Syntax Diagram#

Syntax#

VARCHAR [(size)] [ FIXED | VARIABLE [IN ROW size] ]

Description#

This is a character data type for storing alphanumeric data that vary in length within a specified size.

The default size of VARCHAR column is 1byte, and its maximum length is 32000 bytes.

VARCHAR is a variable length data type; that is, when the length of input data is shorter than the specified column size, only the data that were actually inserted are stored. In contrast, for the CHAR data type, if the length of input data is shorter than the column length, the remaining space in the column is padded with blank spaces. For example, if a column is defined as CHAR(10) and the word “magic” is to be stored, it will be stored as “magic_____”, where “_” represents a blank space.

For more detailed information about the FIXED and VARIABLE clauses, please refer to the "FIXED/VARIABLE OPTIONS" and "IN ROW Clauses" section above.

NCHAR#

Syntax Diagram#

Syntax#

NCHAR [(size)] [ FIXED | VARIABLE [IN ROW size] ]

Description#

This is a character data type having a specified fixed length. If an input value is shorter than the specified size, the remainder is filled with blank spaces.

If the national character set is UTF16, the size of one character in an NCHAR column is fixed at 2 bytes, that is, it does not vary in length. In contrast, if the national character set is UTF8, the size of one character in an NCHAR column is not fixed; rather, it varies from 1 to 3 bytes.

The maximum size is 16000 bytes if the national character set is UTF16.

For more detailed information about the FIXED and VARIABLE clauses, please refer to the "FIXED/VARIABLE OPTIONS" and "IN ROW Clauses" section above.

NVARCHAR#

Syntax Diagram#

Syntax#

NVARCHAR [(size)] [ FIXED | VARIABLE [IN ROW size] ]

Description#

This is a character data type for storing Unicode alphanumeric data that vary in length within a specified size.

If the national character set is UTF16, the size of one character in an NVARCHAR column is fixed at 2 bytes, that is, it does not vary in length. In contrast, if the national character set is UTF8, the size of one character in an NVARCHAR column is not fixed; rather, it varies from 1 to 3 bytes.

In other aspects, the NVARCHAR type is the same as the VARCHAR type, so for more detailed information please refer to the description of the VARCHAR type.

For more detailed information about the FIXED and VARIABLE clauses, please refer to the "FIXED/VARIABLE OPTIONS" and "IN ROW Clauses" section above.

Numeric Data Types#

Numeric data types are used to store zero as well as positive and negative numbers having fixed values. Altibase supports the following numeric types:

BIGINT
DECIMAL
DOUBLE
FLOAT
INTEGER
NUMBER
NUMERIC
REAL
SMALLINT

BIGINT#

Syntax Diagram#

Syntax#

BIGINT

Description#

This is an 8-byte integer data type.

It is equivalent to the “long” (on 64-bit systems) and “long long” (on 32-bit systems) types in the C language.

Range: -2⁶³ + 1(-9223372036854775807) \~ 2⁶³ – 1(9223372036854775807)

DECIMAL#

Syntax Diagram#

Syntax#

DECIMAL [(precision[, scale])]

Description#

This data type is the same as the NUMERIC type.

DOUBLE#

Syntax Diagram#

Syntax#

DOUBLE

Description#

This is an 8-byte floating-point numeric data type.

It is the same as the “double” type in the C language.

FLOAT#

Syntax Diagram#

Syntax#

FLOAT [(precision)]

Description#

This is a floating-point numeric data type that can store a value ranging from -1E+120 to 1E+120.

Precision is the number of significant digits, that is, the number of digits used to express the mantissa of the floating-point number.

Precision can range from 1 to 38. If it is not expressly specified, the default precision is 38.

INTEGER#

Syntax Diagram#

Syntax#

INTEGER

Description#

This is an integer data type that is 4 bytes in size.

It is the same as the “int” data type in the C language.

It can have an integer value ranging from -2,147,483,647 to 2,147,483,647.

NUMBER#

Syntax Diagram#

Syntax#

NUMBER [(precision, scale)]

Description#

This is an alias of the NUMERIC data type. However, when precision and scale are not defined, they are the same as for the FLOAT data type. The FLOAT data type is rounded to the 39th digit.

NUMERIC#

Syntax Diagram#

Syntax#

NUMERIC [(precision, scale)]

Description#

NUMERIC is a fixed decimal data type that can contain a total number of significant digits up to the value specified using precision and a number of digits to the right of the decimal place up to the value specified using scale. In contrast to the FLOAT data type, which is a floating-point numerical data type used for representing real numbers, when both precision and scale are omitted from a NUMERIC data type declaration, precision defaults to 38 and scale to 0, i.e.

Precision can be specified within the range from 1 to 38.
Scale can be specified within the range from -84 to 128.
If precision is omitted, the default is 38.
If scale is omitted, the default is 0.

The following shows the respective values that would result when the input value 1234567.89 is converted to the NUMERIC types defined as shown.

NUMERIC=> 1234568
NUMERIC(9)=> 1234568
NUMERIC(9, 2)=> 1234567.89
NUMERIC(9, 1)=> 1234567.9
NUMERIC(6)=> Precision exceeded
NUMERIC(7, -2)=> 1234500
NUMERIC(7, 2)=> Precision exceeded

REAL#

Syntax Diagram#

Syntax#

REAL

Description#

The data type is used to store 4-byte floating-point numeric values.

It is the same as the “float” type in the C language.

SMALLINT#

Syntax Diagram#

Syntax#

SMALLINT

Description#

This data type is used to store 2-byte integer values.

It is the same as the "short" type in the C language.

It can be used to store integers ranging from -2¹⁵ + 1(-32,767)에서 2¹⁵ - 1(32,767) inclusive

Number Format Model#

When data are converted using typecasting functions such as TO_CHAR or TO_NUMBER, numeric data can be specified in the following formats. A number format model consists of one or more elements that represent a number. In this section, each of these elements will be explained with reference to examples showing the related number formats.

, (Comma)#

Descriptions#

Outputs a comma at the specified position.

Commas can be used multiple times.

Restriction#

A comma cannot be placed at the end of a number, to the right of a decimal point, or at the very beginning of a number.

Example#

iSQL> SELECT TO_CHAR (1234, '99,99') FROM dual;
TO_CHAR (1234, '99,99')  
---------------------------
 12,34           
1 row selected.

iSQL> SELECT TO_NUMBER ( '12,34', '99,99') FROM dual;
TO_NUMBER ( '12,34', '99,99') 
--------------------------------
1234        
1 row selected.

. (Decimal Point)#

Description#

Adds a decimal point at the specified position

Restriction#

Only one decimal point can be used within a number

Example#

iSQL> SELECT TO_CHAR (1.234, '99.999') FROM dual;
TO_CHAR (1.234, '99.999')  
-----------------------------
  1.234          
1 row selected.

iSQL> SELECT TO_NUMBER ( '1.234', '99.999') FROM dual;
TO_NUMBER ( '1.234', '99.999') 
---------------------------------
1.234       
1 row selected.

\$#

Description#

Prepends the $ sign to a number

Example#

iSQL> SELECT TO_CHAR (123, '$9999') FROM dual;
TO_CHAR (123, '$9999')  
--------------------------
  $123           
1 row selected.

iSQL> SELECT TO_NUMBER ( '$0123', '09$99') FROM dual;
TO_NUMBER ( '$0123', '09$99') 
--------------------------------
123         
1 row selected.

0(Numeral 0)#

Description#

If the number of significant digits to be output exceeds the number of digits in the number that is input, 0's (zeroes) are prepended to the number before it is returned. In all other aspects, this element is the same as the “9” element, which is described below.

Example#

iSQL> SELECT TO_CHAR (123, '0999') FROM dual;
TO_CHAR (123, '0999')  
-------------------------
 0123

9(Numeral 9)#

Description#

Uses the numeral 9 to indicate the number of digits to output. If the number of 9's is greater than the number of digits in the number that is input, the space to the left of the number is padded with blank spaces before the number is output. If the number of 9's to the left of the decimal point is less than the number of digits to the left of the decimal point in the input number, the pound sign (“#”) is repeatedly output. The number of pound signs that are output is the number of characters in the user-defined format plus one (a sign character). A decimal point placed in between 9's separates the integer and fractional parts of a number

When there are digits to the right of the decimal point in the first argument, i.e. when the input number has a fractional part, but the user-defined format either has no fractional part or has a fractional part with a smaller number of decimal places than the input number, the input number is rounded off to the number of decimal places in the user-defined format

Example#

iSQL> SELECT TO_CHAR (123, '99999') FROM dual;
TO_CHAR (123, '99999')  
--------------------------
   123

iSQL> SELECT TO_CHAR (123.55, '999') FROM dual;
TO_CHAR (123.55, '999')  
---------------------------
 124             
1 row selected.

iSQL> SELECT TO_CHAR (123.4567, '999999') FROM dual;
TO_CHAR (123.4567, '999999')  
--------------------------------
    123          
1 row selected.

iSQL> SELECT TO_CHAR (1234.578, '9999.99') FROM dual;
TO_CHAR (1234.578, '9999.99')  
---------------------------------
 1234.58         
1 row selected.

iSQL> SELECT TO_CHAR (1234.578, '999.99999') FROM dual;
TO_CHAR (1234.578, '999.99999')  
-----------------------------------
##########       
1 row selected.

iSQL> SELECT TO_NUMBER ( '123', '99999') FROM dual;
TO_NUMBER ( '123', '99999') 
------------------------------
123         
1 row selected.
iSQL> SELECT TO_NUMBER ( '1234.58', '9999.99') FROM dual;
TO_NUMBER ( '1234.58', '9999.99') 
------------------------------------
1234.58     
1 row selected.

FM#

Description#

Removes spaces or zeroes from the left part of the output string.

Example#

iSQL> select to_char(00123.100,'99999.999') from dual;
TO_CHAR(00123.100,'99999.999')
----------------------------------
   123.100
1 row selected.
iSQL> select to_char(00123.100,'FM99999.999') from dual;
TO_CHAR(00123.100,'FM99999.999')
------------------------------------
123.100
1 row selected.

B#

Description#

0's (zeroes) in the integer part of the fixed-point number are replaced with blank spaces.

Example#

iSQL> SELECT TO_CHAR (0.4, 'B9') FROM T1;
TO_CHAR (0.4, 'B9')  
-----------------------

1 row selected.

C#

Description#

Returns the ISO currency symbol(the value set for the NLS_ISO_CURRENCY property) in the specified position.

Example#

iSQL> SELECT TO_CHAR (4000, 'C9999') FROM dual;
TO_CHAR (4000, 'C9999')
---------------------------
 KRW4000
1 row selected.

D#

Description#

Returns the decimal character(the value set for the NLS_NUMERIC_CHARACTER property) in the specified position. The default value is a period(.).

Restriction#

Only one decimal character can be included when specifying a number format model.

Example#

iSQL> SELECT TO_CHAR (24.06, '99D99') FROM dual;
TO_CHAR (24.06, '99D99')
----------------------------
 24.06
1 row selected.
iSQL> SELECT TO_CHAR (206, '999D99') FROM dual;
TO_CHAR (206, '999D99')
---------------------------
 206.00
1 row selected.

EEEE#

Description#

Displays the input number in exponential notation

Restriction#

EEEE should always be at the right end. It is possible to be at the left rather than S, PR, or MI. It cannot be used with commas. In addition, it cannot be used in the TO_NUMBER function.

Example#

iSQL> SELECT TO_CHAR (1234, '9.9EEEE') FROM dual;
TO_CHAR (1234, '9.9EEEE')  
-----------------------------
  1.2E+03        
1 row selected.

G#

Description#

Returns the group separator(the value set for the NLS_NUMERIC_CHARACTER property) in the specified position. Multiple group separators can be specified for a number format model.

Restriction#

In a number format model, a group separator cannot appear to the right of a decimal character or a period.

Example#

iSQL> SELECT TO_CHAR (2534.3, '999G999D99') FROM dual;
TO_CHAR (2534.3, '999G999D99')
----------------------------------
   2,534.30
1 row selected.

L#

Description#

Returns the local currency symbol(the value set for the NLS_CURRENCY property) in the specified position.

Example#

iSQL> SELECT TO_CHAR (4000, 'L9999') FROM dual;
TO_CHAR (4000, 'L9999')
---------------------------
 ?4000
1 row selected.

MI#

Description#

When MI is used at the rightmost place in the number format, if the input value is negative, the minus (-) sign is output at the end of the number, rather than at the beginning. If the input value is positive, a blank space is output instead of the minus sign.

Restrictions#

MI must always be at the rightmost place in the number format. It cannot be used together with S or PR.

Example#

iSQL> SELECT TO_CHAR (-123, '999MI') FROM dual;
TO_CHAR (-123, '999MI')  
---------------------------
123-             
1 row selected.

iSQL> SELECT TO_NUMBER ( '123-', '999MI') FROM dual;
TO_NUMBER ( '123-', '999MI') 
-------------------------------
-123        
1 row selected.

PR#

Description#

When PR is used at the rightmost place in the number format, if the input value is negative, the value is output in the form of “”, rather than using the minus (“-”) sign.

Restriction#

PR must always be at the rightmost place in the number format. It cannot be used together with S or MI.

Example#

iSQL> SELECT TO_CHAR (-123, '999PR') FROM dual;
TO_CHAR (-123, '999PR')  
---------------------------
<123>            
1 row selected.

iSQL> SELECT TO_NUMBER ( '<123>', '999PR') FROM dual;
TO_NUMBER ( '<123>', '999PR') 
--------------------------------
-123        
1 row selected.

RN#

Description#

Converts an input number to Roman numerals. The valid input range is from 1 to 3,999. If the lower-case letters “rn” are used in the number format, lower-case Roman numerals are output.

Restriction#

RN cannot be used with any other number format elements or with the TO_NUMBER function.

Example#

iSQL> SELECT TO_CHAR (14, 'RN') FROM dual;
TO_CHAR (14, 'RN')  
----------------------
XIV              
1 row selected.

S#

Description#

When S is placed at the beginning or end of the number format, a plus (“+”) or minus (“-”) sign is output at the same position, corresponding to the sign of the input number.

Restriction#

S can be placed at the beginning or end of the number format. It cannot be used with MI or PR.

Example#

iSQL> SELECT TO_CHAR (123, 'S999.99') FROM dual;
TO_CHAR (123, 'S999.99')  
----------------------------
+123.00          
1 row selected.

iSQL> SELECT TO_CHAR (-123, '999.99S') FROM dual;
TO_CHAR (-123, '999.99S')  
-----------------------------
123.00-          
1 row selected.

iSQL> SELECT TO_NUMBER ( '+123', 'S999.99') FROM dual;
TO_NUMBER ( '+123', 'S999.99') 
---------------------------------
123         
1 row selected.

iSQL> SELECT TO_NUMBER ( '123.00-', '999.99S') FROM dual;
TO_NUMBER ( '123.00-', '999.99S') 
------------------------------------
-123        
1 row selected.

V#

Description#

The input number is multiplied by 10 to the power of the number of 9's after V. The number of 9's before V represents the number of significant digits to return from the input number.

Restriction#

V cannot be used with a decimal point, and cannot be used with the TO_NUMBER function.

Example#

iSQL> SELECT TO_CHAR (12, '99V99') FROM dual;
TO_CHAR (12, '99V99')  
-------------------------
 1200            
1 row selected.

iSQL> SELECT TO_CHAR (1200, '99V99') FROM dual;
TO_CHAR (1200, '99V99')  
---------------------------
######           
1 row selected.

iSQL> SELECT TO_CHAR (-123.456, '999V999EEEEMI') from dual;
TO_CHAR (-123.456, '999V999EEEEMI')  
---------------------------------------
 1235E+02-        
1 row selected.

XXXX#

Description#

Converts the input number to a hexadecimal number. If the input number is not an integer, it is rounded off before being converted to a hexadecimal number. Specifying “xxxx” in lower-case returns the letters in the hexadecimal number in lower-case.

Restriction#

XXXX cannot be used with other number format elements. The number to be converted must be greater than 0 (zero).

Example#

iSQL> SELECT TO_CHAR (123, 'XXXX') FROM dual;
TO_CHAR (123, 'XXXX')  
-------------------------
7B               
1 row selected.

iSQL> SELECT TO_NUMBER ('ABC', 'XXXX') FROM dual;
TO_NUMBER ('ABC', 'XXXX') 
----------------------------
2748        
1 row selected.

Date Data Type#

The DATE type is used to store date and time information

DATE#

Syntax Diagram#

Syntax#

DATE

Description#

This data type is used to store date values in 8 bytes.

The range of dates that can be stored depends on the system. Typically, the dates that can be stored range from 0001/01/01 - 9999/12/31.

The date value can be displayed in various formats using a date format string.

The Datetime Format Model#

The date data type is managed as numerical data d it is internally managed by the database. However, it can be displayed with character strings with the conversion function TO_CHAR or TO_DATE. The user should specify a character string of the date data type in accordance with a report format when using conversion functions.

The date data types are comprised of the following basic elements.

AM, PM
SCC, CC
D, DD, DDD, DAY,DY
HH, HH12, HH24
MM, MON, MONTH
MI
Q
SS, SSSSS, SSSSSS, SSSSSSSS, FF[1..6]
WW, WW2, W, IW
Y,YYY
SYYYY, YYYY, YYY, YY, Y, RR, RRRR
IYYY, IYY, IY, I

Along with these basic elements, the datetime format model also consists of the following punctuation marks and special characters:

Hyphen (-)
Slash (/)
Comma (,)
Period (.)
Colon (:)
Single Quotation (‘)

The meaning and use of each of these basic elements will be explained below with reference to examples.

AM, PM#

Description#

Returns either “AM” or “PM” depending on whether the input time is before or after noon. (AM or PM)

Example#

iSQL> SELECT TO_CHAR ( TO_DATE( '13', 'HH' ), 'AM' ) FROM dual;
TO_CHAR ( TO_DATE( '13', 'HH' ), 'AM' )  
-------------------------------------------
PM      
1 row selected.

iSQL> SELECT TO_DATE('1980-12-28 PM', 'YYYY-MM-DD AM') FROM dual;
TO_DATE('1980-12-28 PM', 'YYYY-MM-DD AM' 
-------------------------------------------
1980/12/28 12:00:00  
1 row selected.

SCC#

Description#

Represents a century

If the last 2 digits of an input 4-digit year are within the range from 01 to 99, the sum of 1 plus the first 2 digits of the 4-digit year is returned.
If the last 2 digits of an input 4-digit year are 00, the first 2 digits of the 4-digit year are returned unchanged.

BC is preceded by a year with a minus sign (-).

A year of 0000 is one year B.C.E and -0001 is a two year B.C.E.
0000 to -0099 are in -1 century and are represented by -01.

SCC cannot be used as an argument for the TO_DATE function.

Example#

iSQL> SELECT TO_CHAR ( '28-DEC-1980', 'SCC' ) FROM dual;
TO_CHAR ( '28-DEC-1980', 'SCC' )  
------------------------------------
 20 
1 row selected.

iSQL> SELECT TO_CHAR ( DATE'01-JAN-0001' - 1, 'SCC' ) FROM dual;
TO_CHAR ( DATE'01-JAN-0001' - 1, 'SCC' )  
--------------------------------------------
-01 
1 row selected.

CC#

Description#

Represents a century

If the last 2 digits of an input 4-digit year are within the range from 01 to 99, the sum of 1 plus the first 2 digits of the 4-digit year is returned.
If the last 2 digits of an input 4-digit year are 00, the first 2 digits of the 4-digit year are returned unchanged.

CC cannot be used as an argument for the TO_DATE function.

Example#

iSQL> SELECT TO_CHAR ( '28-DEC-1980', 'CC' ) FROM dual;
TO_CHAR ( '28-DEC-1980', 'CC' )  
-----------------------------------
20      
1 row selected.

D#

Description#

Returns the day of the week, represented by a number from 1 to 7. Sunday is represented by the number 1.

D cannot be used as an argument for the TO_DATE function.

Example#

iSQL> SELECT TO_CHAR ( '28-DEC-1980', 'D' ) FROM dual;
TO_CHAR ( '28-DEC-1980', 'D' )  
----------------------------------
1    
1 row selected.

DAY#

Description#

Returns the day of the week in upper-case letters in English (SUNDAY, MONDAY,…).

DAY cannot be used with the TO_DATE function.

Example#

iSQL> SELECT TO_CHAR ( '28-DEC-1980', 'DAY' ) FROM dual;
TO_CHAR ( '28-DEC-1980', 'DAY' )  
------------------------------------
SUNDAY     
1 row selected.

DD#

Description#

Returns the day of the month, represented by a number from 1 to 31.

Example#

iSQL> SELECT TO_CHAR ( '28-DEC-1980', 'DD' ) FROM dual;
TO_CHAR ( '28-DEC-1980', 'DD' )  
-----------------------------------
28      
1 row selected.

iSQL> SELECT TO_DATE( '1980-12-28', 'YYYY-MM-DD') FROM dual;
TO_DATE( '1980-12-28', 'YYYY-MM-DD') 
---------------------------------------
1980/12/28 00:00:00  
1 row selected.

DDD#

Description#

Returns the day of the year, represented by a number from 1 to 366.

DDD cannot be used with the TO_DATE function.

Example#

iSQL> SELECT TO_CHAR ( '28-DEC-1980', 'DDD' ) FROM dual;
TO_CHAR ( '28-DEC-1980', 'DDD' )  
------------------------------------
363        
1 row selected.

DY#

Description#

Returns the day of the week in abbreviated form (SUN, MON, TUE, …).

DY cannot be used with the TO_DATE function.

Example#

iSQL> SELECT TO_CHAR ( '28-DEC-1980', 'DY' ) FROM dual;
TO_CHAR ( '28-DEC-1980', 'DY' )  
-----------------------------------
SUN     
1 row selected.

FF [1..6]#

Description#

A number from 1 to 6 after FF is used to represent the number of microseconds (0~999999). The FF returns the same result as FF6.

FF cannot be used with the TO_DATE function.

Example#

iSQL> SELECT TO_CHAR ( SYSDATE, 'FF5' ) FROM dual;
TO_CHAR ( SYSDATE, 'FF5' )  
------------------------------
34528      
1 row selected.

HH, HH24#

Description#

Returns the hour of the day in 24-hour format (i.e. returns a number from 0 to 23)

Example#

iSQL> SELECT TO_CHAR ( TO_DATE( '2008-12-28 17:30:29', 'YYYY-MM-DD HH:MI:SS' ), 'HH' ) FROM dual;
TO_CHAR ( TO_DATE( '2008-12-28 17:30:29'  
--------------------------------------------
17      
1 row selected.

iSQL> SELECT TO_CHAR ( TO_DATE( '2008-12-28 17:30:29', 'YYYY-MM-DD HH24:MI:SS' ), 'YYYY-MM-DD HH24:MI:SS' ) FROM dual;
TO_CHAR ( TO_DATE( '2008-12-28 17:30:29',
------------------------------------------
2008-12-28 17:30:29
1 row selected.

HH12#

Description#

Returns the hour of the day in 12-hour format (i.e. returns a number from 1 to 12).

Example#

iSQL> SELECT TO_CHAR ( TO_DATE( '2008-12-28 17:30:29', 'YYYY-MM-DD HH:MI:SS' ), 'HH12' ) FROM dual;
TO_CHAR ( TO_DATE( '2008-12-28 17:30:29',
---------------------------------------------
05
1 row selected.

iSQL> SELECT TO_CHAR( TO_DATE ( '08-12-28 05:30:29', 'RR-MM-DD HH12:MI:SS' ), 'RR-MM-DD HH12:MI:SS') FROM dual;
TO_CHAR( TO_DATE ( '08-12-28 05:30:29', 'R
--------------------------------------------
08-12-28 05:30:29
1 row selected.

MI#

Description#

Returns a number ranging from 0 to 59, indicating the minutes portion of the input date.

Example#

iSQL> SELECT TO_CHAR ( TO_DATE( '1980-12-28 17:30:29', 'YYYY-MM-DD HH:MI:SS' ), 'HH' ) FROM dual;
TO_CHAR ( TO_DATE( '1980-12-28 17:30:29'  
--------------------------------------------
17      
1 row selected.

iSQL> SELECT TO_DATE ( '05-12-28 14:30:29', 'RR-MM-DD HH:MI:SS' ) FROM dual;
TO_DATE ( '05-12-28 14:30:29', 'RR-MM-DD 
-------------------------------------------
2005/12/28 14:30:29  
1 row selected.

MM#

Description#

Returns a number ranging from 01 to 12, indicating the month of the input date.

Example#

iSQL> SELECT TO_CHAR ( TO_DATE( '1980-12-28 17:30:29', 'YYYY-MM-DD HH:MI:SS' ), 'HH' ) FROM dual;
TO_CHAR ( TO_DATE( '1980-12-28 17:30:29'  
--------------------------------------------
17      
1 row selected.

iSQL> SELECT TO_DATE ( '05-12-28 14:30:29', 'RR-MM-DD HH:MI:SS' ) FROM dual;
TO_DATE ( '05-12-28 14:30:29', 'RR-MM-DD 
-------------------------------------------
2005/12/28 14:30:29  
1 row selected.

MON#

Description#

Returns the name of the month in upper case in abbreviated form. ( JAN, FEB, MAR, …)

Example#

SQL> SELECT TO_CHAR (TO_DATE ('1995-12-05', 'YYYY-MM-DD'), 'MON') FROM dual;
TO_
---
DEC

MONTH#

Description#

Returns the name of the month in upper case. (JANUARY, FEBRUARY, … )

Example#

iSQL> SELECT TO_CHAR ( '28-DEC-1980', 'Month' ) FROM dual;
TO_CHAR ( '28-DEC-1980', 'Month' )  
--------------------------------------
December         
1 row selected.

iSQL> SELECT TO_DATE ( '05-APRIL-28 14:30:29', 'RR-MONTH-DD HH:MI:SS' ) FROM dual;
TO_DATE ( '05-APRIL-28 14:30:29', 'RR-MO 
-------------------------------------------
2005/04/28 14:30:29  
1 row selected.

Q#

Description#

Returns a number ranging from 1 to 4, indicating the quarter of the year of the input date.

This element cannot be used with the TO_DATE function.

Example#

iSQL> SELECT TO_CHAR ( '28-DEC-1980', 'Q' ) FROM dual;
TO_CHAR ( '28-DEC-1980', 'Q' )  
----------------------------------
4    
1 row selected.

RM#

Description#

Returns the month of the input date in Roman numerals (I, II, III, IV... ).

Example#

iSQL> SELECT TO_CHAR ( '28-DEC-1980', 'RM' ) FROM dual;
TO_CHAR ( '28-DEC-1980', 'RM' )  
-----------------------------------
XII     
1 row selected.

iSQL> SELECT TO_DATE ('28-V-1980', 'DD-RM-YYYY') FROM dual;
TO_DATE ('28-V-1980', 'DD-RM-YYYY') 
--------------------------------------
1980/05/28 00:00:00  
1 row selected.

RR#

Description#

Returns the year of the input date as a 2-digit integer. When the year portion of the input date has 2 digits, if it is less than 50, 2000 is added to it (i.e. the 21st Century is assumed), whereas if it is greater than or equal to 50, 1900 is added to it before it is displayed. Therefore, the range of years that can be displayed is between 1950 – 2049.

Example#

iSQL> SELECT TO_CHAR ( '28-DEC-80', 'RR' ) FROM dual;
TO_CHAR ( '28-DEC-80', 'RR' )  
---------------------------------
80      
1 row selected.

iSQL> SELECT TO_DATE ( '28-DEC-80', 'DD-MON-RR' ) FROM dual;
TO_DATE ( '28-DEC-80', 'DD-MON-RR' ) 
---------------------------------------
1980/12/28 00:00:00  
1 row selected.

RRRR#

Description#

Year (0 - 9999)

Returns the year of the input date as a 4-digit integer. When the year portion of the input date has 2 digits, if it is less than 50, 2000 is added to it (i.e. the 21st Century is assumed), whereas if it is greater than or equal to 50 and less than 100, 1900 is added to it before it is displayed. When the year portion of the input date has 4 digits, it is output without change.

Example#

iSQL> SELECT TO_CHAR ( '28-DEC-1980', 'RRRR' ) FROM dual;
TO_CHAR ( '28-DEC-1980', 'RRRR' )  
-------------------------------------
1980          
1 row selected.

iSQL> SELECT TO_CHAR ( '28-DEC-1980', 'DD-MON-RRRR' ) FROM dual;
TO_CHAR ( '28-DEC-1980', 'DD-MON-RRRR' )  
--------------------------------------------
28-DEC-1980                        
1 row selected.

SS#

Description#

Returns a number ranging from 0 to 59, indicating the seconds' portion of the input date.

Example#

iSQL> SELECT TO_CHAR ( TO_DATE( '1980-12-28 17:30:29', 'YYYY-MM-DD HH:MI:SS' ), 'HH' ) FROM dual;
TO_CHAR ( TO_DATE( '1980-12-28 17:30:29'  
--------------------------------------------
17      
1 row selected.

iSQL> SELECT TO_DATE ( '05-12-28 14:30:29', 'RR-MM-DD HH:MI:SS' ) FROM dual;
TO_DATE ( '05-12-28 14:30:29', 'RR-MM-DD 
-------------------------------------------
2005/12/28 14:30:29  
1 row selected.

SSSSS#

Description#

Returns a number ranging from 0 to 86399, indicating the number of seconds that have passed since midnight.

Example#

iSQL> SELECT TO_CHAR ( TO_DATE( '1980-12-28 17:30:29', 'YYYY-MM-DD HH24:MI:SS' ), 'SSSSS' ) FROM dual;
TO_CHAR ( TO_DATE( '1980-12-28 17:30:29'  
--------------------------------------------
62940            
1 row selected.

iSQL> SELECT TO_DATE('1980-12-28 12345', 'YYYY-MM-DD SSSSS') FROM dual;
TO_DATE('1980-12-28 12345', 'YYYY-MM-DD  
-------------------------------------------
1980/12/28 03:25:45  
1 row selected.

SSSSSS#

Description#

Returns the fractional part of a second. (0 ~ 999999)

Example#

iSQL> SELECT TO_CHAR (SYSDATE, 'SSSSSS') FROM dual;
TO_CHAR (SYSDATE, 'SSSSSS')  
-------------------------------
490927              
1 row selected.  

iSQL> SELECT TO_CHAR ( TO_DATE('1980-12-28 123456', 'YYYY-MM-DD SSSSSS'), 'SSSSSS' ) FROM dual;
TO_CHAR ( TO_DATE('1980-12-28 123456', '  
--------------------------------------------
123456              
1 row selected.

SSSSSSSS#

Description#

Returns the integer and fractional parts of the number of seconds in the input date, expressed as an 8-digit integer ranging from 0 to 59999999. The first two digits indicate the number of seconds, and the remaining 6 digits represent the fractional part of the second.

Example#

iSQL> SELECT TO_CHAR (SYSDATE, 'SSSSSSSS') FROM dual;
TO_CHAR (SYSDATE, 'SSSSSSSS')  
---------------------------------
48987403                  
1 row selected.  

iSQL> SELECT TO_DATE ( '12.345678', 'SS.SSSSSS') FROM dual;
TO_DATE ( '12.345678', 'SS.SSSSSS') 
--------------------------------------
2005/12/01 00:00:12  
1 row selected.

iSQL> SELECT TO_CHAR( TO_DATE( '12.345678', 'SS.SSSSSS'), 'SSSSSS') FROM dual;
TO_CHAR( TO_DATE( '12.345678', 'SS.SSSSS  
--------------------------------------------
345678              
1 row selected.

WW#

Description#

Returns a number ranging from 1 to 54, indicating the week of the year. The period from January 1 to the first Saturday is considered the first week of the year.

This element cannot be used with the TO_DATE function.

Example#

iSQL> SELECT TO_CHAR ( '28-DEC-1980', 'WW' ) FROM dual;
TO_CHAR ( '28-DEC-1980', 'WW' )  
-----------------------------------
53      
1 row selected.

WW2#

Description#

This date type indicates the week of the year regardless of day of the week. The first week begins with the 1st of January distinguished by a 7-days unit (1st to 53th week).

This element cannot be used within the TO_DATE function.

Example#

iSQL> SELECT TO_CHAR ( '28-DEC-1980', 'WW2' ) FROM dual;
TO_CHAR ( '28-DEC-1980', 'WW2' )  
-----------------------------------
52      
1 row selected.

W#

Description#

Returns a number ranging from 1 to 6, indicating the week of the month. The period from the first day of the month to the first Saturday is considered the first week of the year.

This element cannot be used with the TO_DATE function.

Example#

iSQL> SELECT TO_CHAR ( '28-DEC-1980', 'W' ) FROM dual;
TO_CHAR ( '28-DEC-1980', 'W' )  
----------------------------------
5    
1 row selected.

IW#

Description#

This displays which week of the year it is (1\~52 or 1\~53) depending on ISO 8601. The start of the week is Monday, and the first week includes the first Thursday (January, 4th) of the year.

This element cannot be used with the TO_DATE function.

12.31. 2012 (Mon) ~ 01.06. 2013 (Sun): 1st week in 2013
12.29. 2014 (Mon) ~ 01.04. 2015 (Sun): 1st week in 2015
12.28. 2015 (Mon) ~ 01.03. 2016 (Sun): 53th week in 2015
12.26. 2016 (Mon) ~ 01.01. 2017 (Sun): 52th week in 2016

Example#

iSQL> SELECT TO_CHAR ( '28-DEC-1980', 'IW' ) FROM dual;
TO_CHAR ( '28-DEC-1980', 'IW' )  
-----------------------------------
52       
1 row selected.

Y,YYY#

Description#

Returns the year of the input date. A comma can be inserted at any place within a number representing the year, including the very beginning or end.

This element cannot be used with the TO_DATE function.

Example#

iSQL> SELECT TO_CHAR ( '28-DEC-1980', 'Y,YYY' ) FROM dual;
TO_CHAR ( '28-DEC-1980', 'Y,YYY' )  
--------------------------------------
1,980            
1 row selected.

SYYYY#

Description#

Returns the four digits of the year. BC is marked with a minus sign '-". (-9999 ~ 9999)

If the year is 0000, one year B.C.E, -0001 represents two years B.C.E.

Example#

iSQL> SELECT TO_CHAR ( '28-DEC-1980', 'SYYYY' ) FROM dual;
TO_CHAR ( '28-DEC-1980', 'SYYYY' )  
--------------------------------------
 1980     
1 row selected.

iSQL> SELECT TO_CHAR ( DATE'01-JAN-0000' - 1, 'SYYYY-MM-DD' ) FROM dual;
TO_CHAR ( DATE'01-JAN-0000' - 1, 'SYYYY-MM  
----------------------------------------------
-0001-12-31 
1 row selected.

YYYY#

Description#

Handles a positvie four-digit number ranging form 0 - 9999 as the year.

Example#

iSQL> SELECT TO_CHAR ( '28-DEC-1980', 'YYYY' ) FROM dual;
TO_CHAR ( '28-DEC-1980', 'YYYY' )  
-------------------------------------
1980          
1 row selected.

iSQL> SELECT TO_DATE ( '28-DEC-1980', 'DD-MON-YYYY' ) FROM dual;
TO_DATE ( '28-DEC-1980', 'DD-MON-YYYY' ) 
-------------------------------------------
1980/12/28 00:00:00  
1 row selected.

YY#

Description#

Returns the last two digits of the year. The 21st Century is assumed, so 2000 is added to it to obtain the actual year, which can range from 2000 to 2099.

Example 1#

iSQL> SELECT TO_CHAR ( '28-DEC-1980', 'YY' ) FROM dual;
TO_CHAR ( '28-DEC-1980', 'YY' )  
-----------------------------------
80      
1 row selected.

iSQL> SELECT TO_DATE ( '28-DEC-80', 'DD-MON-YY' ) FROM dual;
TO_DATE ( '28-DEC-80', 'DD-MON-YY' ) 
---------------------------------------
2080/12/28 00:00:00  
1 row selected.

Example 2#

iSQL> CREATE TABLE timetbl(i1 INTEGER, t1 DATE, etc VARCHAR(10));
Create success.

iSQL> INSERT INTO timetbl VALUES (1, SYSDATE, 'Start');
1 row inserted.

iSQL> INSERT INTO timetbl VALUES (2, TO_DATE('2003-02-20 12:15:50', 'YYYY-MM-DD HH:MI:SS'), 'The end');
1 row inserted.

iSQL> SELECT TO_CHAR(T1, 'YYYY YY MM MON Mon mon DD HH MI SS SSSSSS D DDD') Date_format FROM timetbl WHERE I1 = 2;
DATE_FORMAT                                         
------------------------------------------------
2003 03 02 FEB Feb feb 20 12 15 50 000000 5 051     
1 row selected.

RR, RRRR, YY, YYYY Comparison#

Obtains the actual year, which can range from 2000 to 2099.

[YYYY]: The number is treated as a year, without change.

‘23-FEB-5’ = February 23, 0005

‘23-FEB-05’ = February 23, 0005

‘23-FEB-2005’= February 23, 2005

‘23-FEB-95’ = February 23, 0095

[YY]: 2000 is added to YY to obtain the year.

‘23-FEB-5’ = February 23, 2005

‘23-FEB-05’ = February 23, 2005

‘23-FEB-2005’= Error

‘23-FEB-95’ = February 23, 2095

‘23-FEB-05’ = February 23, 2005

‘23-FEB-2005’= Error

‘23-FEB-95’ = February 23, 2095

[RRRR]: A number greater than 100 is taken as the year without change. If the input number has one or two digits, if it is < 50, 2000 is added to it, and if it is >= 50 and < 100, 1900 is added to it before it is output.

‘23-FEB-5’: February 23, 2005

‘23-FEB-05’: February 23, 2005

‘23-FEB-2005’: February 23, 2005

‘23-FEB-95’: February 23, 1995

‘23-FEB-100’: February 23, 0100

‘23-FEB-0005’: February 23, 0005

[RR]: If the input number is < 50, 2000 is added to it, whereas if the input number is >= 50 and < 100, 1900 is added to it before it is output.

‘23-FEB-5’: February 23, 2005

‘23-FEB-05’: February 23, 2005

‘23-FEB-2005’: Error

‘23-FEB-95’: February 23, 1995

YYY#

Description#

The last 3 digits of the year. As the 21st Century is assumed, 2000 is added to it to obtain the actual year, which can range from 2000 to 2099.

Y#

Description#

The final digit of the year. As the 21st Century is assumed, 2000 is added to it to obtain the actual year, which can range from 2000 to 2099.

IYYY, IYY, IY, I#

Description#

Represents a year according to the ISO 8601 standard. The start of the week is Monday, and the first week includes the first Thursday of the year.

IYYYY returns the ISO standard year.

IYY returns the last three digits of the ISO standard year.

IY returns the last two digits of the ISO standard year.

I returns the last 1 digit of the ISO standard year.

This cannot be used with thee TO_DATE function.

2012.12.31 (MONDAY) ~ 2013.01.06 (SUNDAY) : 2013
2014.12.29 (MONDAY) ~ 2015.01.04 (SUNDAY) : 2015
2015.12.28 (MONDAY) ~ 2016.01.03 (SUNDAY) : 2015
2016.12.26 (MONDAY) ~ 2017.01.01 (SUNDAY) : 2016

Example#

iSQL> SELECT TO_CHAR ( '1-JAN-2017', 'IYYY IYY IY I' ) FROM dual;
TO_CHAR('1-JAN-2017','IYYY IYY IY I')        
--------------------------------------------
2016 016 16 6                             
1 row selected

Binary Types#

Large and unstructured data such as text, images, video, and spatial data can be stored as binary data. Altibase supports the following binary types:

BYTE
VARBYTE
NIBBLE
BIT
VARBIT

BYTE#

Syntax Diagram#

Syntax#

BYTE [(size)] [[FIXED |] VARIABLE ( IN ROW size ) ]

Description#

The BYTE is a binary data type containing a specified length. The right space of inserted data is filled with '0' if a character string with a shorter length, which is shorter than the specified the length, is inserted.

The default size of a BYTE column is 1 byte. The maximum length of a BYTE column is 32000 bytes. The data can be expressed in hexadecimal format using a combination of alphabet and numeric characters, such as '0FAE13.' The allowable alphanumeric characters are 0 (zero) to 9 and A to F.

The specified size should be adjusted when executing data retrieval or entering the BYTE column. Two characters can be inserted in a byte; for example, if BYTE(3) is defined, values ranging from ‘000000’ to ‘FFFFFF’ can be inserted.

In the case of inserting the lower case letters from 'a' from 'f', they are stored as being converted.

For more detailed information about the FIXED and VARIABLE clauses, please refer to the "FIXED/VARIABLE OPTIONS" and "IN ROW Clauses" sections above.

Example#

iSQL> CREATE TABLE T1 (I1 BYTE(1), I2 BYTE(5));
Create success.
iSQL> INSERT INTO T1 VALUES (BYTE'11', BYTE'0011');
1 row inserted.
iSQL> SELECT TO_CHAR(I1), TO_CHAR(I2) FROM T1;
TO_CHAR(I1)  TO_CHAR(I2)  
-----------------------------
11  0011000000  
1 row selected.

VARBYTE#

Syntax Diagram#

Syntax#

VARBYTE [(size)] [[FIXED |] VARIABLE ( IN ROW size ) ]

Description#

This is a binary data type containing a variable length. The default size of a VARBYTE column is one byte, and its maximum size is 32000 bytes.

The data can be expressed through a hexadecimal format with combination of alphabets and numbers such as ‘0FAE13’. The allocable alphabet and numeric characters range from 0 to 9 and A to F. The specified size should be adjusted when executing data retrieval or entering the BYTE column.

Two characters can be inserted in a byte; for example, if BYTE(3) is defined, values ranging from ‘000000’ to ‘FFFFFF’ can be inserted. In the case of inserting the lower case letters from 'a' from 'f', they are stored as being converted.

For more detailed information about the FIXED and VARIABLE clauses, please refer to the "FIXED/VARIABLE OPTIONS" and "IN ROW Clauses" sections above.

Example#

iSQL> CREATE TABLE T1 (I1 VARBYTE(1), I2 VARBYTE(5) );
Create success.
iSQL> INSERT INTO T1 VALUES (VARBYTE'11', VARBYTE'0011');
1 row inserted.
iSQL> SELECT TO_CHAR(I1), TO_CHAR(I2) FROM T1;
TO_CHAR(I1)  TO_CHAR(I2)  
-----------------------------
11  0011        
1 row selected.

NIBBLE#

Syntax Diagram#

Syntax#

NIBBLE [(size)] [[FIXED |] VARIABLE ( IN ROW size ) ]

Description#

NIBBLE column is that of a single character, and the maximum size is 254nibbles.

The data can be expressed in hexadecimal format using a combination of alphabet and numeric characters. The allowable alphanumeric characters are 0 (zero) to 9 and A to F. Unlike the BYTE type, only one character can be entered into one nibble.

For example, for NIBBLE (6), '000000' to 'FFFFFF' can be inserted.

When the lower case letters 'a' through 'f' are input, they are converted into upper-case letters.

For more detailed information about the FIXED and VARIABLE clauses, please refer to the "FIXED/VARIABLE OPTIONS" and "IN ROW Clauses" sections above.

BIT#

Syntax Diagram#

Syntax#

BIT [(size)] [[FIXED |] VARIABLE ( IN ROW size ) ]

Description#

This is a binary data type that has a fixed length and consists only of 0's and 1's.

The default size of a BIT column is one bit. Its maximum size is 64000 bits.

If an attempt is made to input a string that is longer than the specified length, an 'Invalid data type length' error will be raised. If a string shorter than the specified length is input, the space to the right of the input data is populated with 0's. If a value other than 0 or 1 is input, an 'Invalid literal' error is raised.

For more detailed information about the FIXED and VARIABLE clauses, please refer to the "FIXED/VARIABLE OPTIONS" and "IN ROW Clauses" sections above.

Example#

iSQL> CREATE TABLE T1 ( I1 BIT(1), I2 BIT(5) );
Create success.
iSQL> INSERT INTO T1 VALUES ( BIT'1', BIT'011' );
1 row inserted.
iSQL> SELECT TO_CHAR(I1), TO_CHAR(I2) FROM T1;
TO_CHAR(I1)  TO_CHAR(I2)
-----------------------------
1  01100
1 row selected.
iSQL> INSERT INTO T1 VALUES ( BIT'1111', BIT'011' );
[ERR-2100D : Invalid data type length]
iSQL> INSERT INTO T1 VALUES ( BIT'1', BIT'1234' );
[ERR-21011 : Invalid literal]

VARBIT#

Syntax Diagram#

Syntax#

VARBIT [(size)] [[FIXED |] VARIABLE ( IN ROW size ) ]

Description#

This is a binary data type that has a variable length and consists only of 0's and 1's.

The default size of a BIT column is one bit. Its maximum size is 64000 bits. If an attempt is made to input a string that is longer than the specified length, an “Invalid data type length” error will be raised.

For more detailed information about the FIXED and VARIABLE clauses, please refer to the "FIXED/VARIABLE OPTIONS" and "IN ROW Clauses" sections above.

Example#

iSQL> CREATE TABLE T1 ( I1 VARBIT(1), I2 VARBIT(5) );
Create success.
iSQL> INSERT INTO T1 VALUES ( VARBIT'1', VARBIT'011' );
1 row inserted.
iSQL> SELECT TO_CHAR(I1), TO_CHAR(I2) FROM T1;
TO_CHAR(I1)  TO_CHAR(I2)
-----------------------------
1  011
1 row selected.
iSQL> INSERT INTO T1 VALUES ( VARBIT'1111', VARBIT'011' );
[ERR-2100D : Invalid data type length]
iSQL> INSERT INTO T1 VALUES ( VARBIT'1', VARBIT'1234' );
[ERR-21011 : Invalid literal]

LOB Data Type#

Overview#

The LOB (which stands for Large Object) data type is for holding large amounts of data. Up to 4GB-1byte can be stored in one column of LOB data. Unlike other data types, the length of a LOB column does not need to be specified when a table is created. Additionally, more than one LOB type column can be defined in a table.

The LOB data type is divided into the Binary Large Object (BLOB) type, which is for holding binary data such as image and video files, and the Character Large Object (CLOB) type, which is for holding string data.

The Features of LOB#

The LOB data type provided with Altibase has the following features:

Data Storage Functions
Partial Read
Disk LOB Partitioning

Features of LOB#

The LOB data type provided with Altibase has the following features:

Data Storage Functions
Partial Read
Disk LOB Partitioning

Partial Read#

It is possible to read only a desired portion of LOB data. A specific amount of data, offset a specific distance from the beginning of the file, can be read using the SQLGetLob function in Altibase ODBC.

Disk LOB Partitioning#

Disk LOB data can be stored in a disk tablespace other than the one in which the table is stored. This tablespace can be configured in a method similar to partitioning.

Storing LOB Columns#

In most cases, LOB data are stored in a variable area, away from the rest of the record. However, if a small amount of data is stored in the LOB column, the column can be stored in an area that is contiguous with the rest of the record (i.e. in the fixed area) using the 'in row' option. Note that this is possible for memory tables only; regardless of their size, LOB data in disk tables are always stored in a separate, variable area.

Because the amount of LOB column data that is stored in the variable area is typically very large, storing it in the same tablespace as the rest of the record has a negative impact on the efficiency of usage of space.

In a disk table, LOB column data can be stored in a tablespace other than the one containing the table to which the LOB column belongs. However, in a memory table, LOB column data cannot be stored separately, and thus are stored in the same tablespace as the table.

BLOB#

Syntax Diagram#

Syntax#

BLOB [ VARIABLE ( IN ROW size ) ]

Description#

BLOB is a binary data type that can vary in length up to 4GB-1byte and is intended for use in storing large amounts of binary data.

For more detailed information about the FIXED and VARIABLE clauses, please refer to the "FIXED/VARIABLE OPTIONS" and "IN ROW Clauses" sections above.

CLOB#

Syntax Diagram#

Syntax#

CLOB [ VARIABLE ( IN ROW size ) ]

Description#

CLOB is a character data type that can vary in length up to 4GB-1byte and is intended for use in storing large amounts of character data.

For more detailed information about the FIXED and VARIABLE clauses, please refer to the "FIXED/VARIABLE OPTIONS" and "IN ROW Clauses" sections above.

Restriction#

LOB type columns can't be used with cursors.
LOB type columns can't be used in volatile tables or disk temporary tablespaces.
LOB columns associated with tables in discarded tablespaces cannot be accessed.
LOB type columns cannot be used for partitioning conditions, because in order to partition a column it must be possible to perform comparisons on the data in the column.
Indexes cannot be created for LOB columns.
It is possible to define a NOT NULL constraint for a LOB type column. However, when an insert attempt is made, a constraint violation error may be raised while the Altibase server is internally processing the data. Therefore, it is recommended that the NOT NULL constraint not be used with LOB type columns.

Spatial Types#

The only geometry data type that is defined and supported for use with SQL in Altibase is the GEOMETRY data type. The Geometry data type consists of the following seven subtypes:

Point
LineString
Polygon
GeomCollection
MultiPolygon
MultiLineString
MultiPoint

For more detailed information about the geometry datatype, please refer to the Spatial SQL Refernce.