SQL requires that values are expressed in a particular way. For example, character strings must be delimited by single quotation marks.
Other values also must be formatted properly. The exact format required depends upon the data type. Several data types other than character data types also require single quotation marks to delimit the values that you enter.
The following script examples show how to format input data for most of the data types that solidDB supports.
In these scripts, many commands are split across multiple lines. This is quite legal in SQL. It is one of the reasons that most SQL interpreters expect a semicolon to separate each SQL statement, even though the ANSI Standard for SQL does not actually require a semicolon at the end of each statement.
CREATE TABLE one_of_almost_everything ( int_col INTEGER, float_col FLOAT, string_col CHAR(20), wide_string_col WCHAR(20), -- "wide" means wide chars, for example, unicode. varchar_col VARCHAR, -- Note that you do not have to specify width. date_col DATE, time_col TIME, timestamp_col TIMESTAMP ); INSERT INTO one_of_almost_everything ( int_col, float_col, string_col, wide_string_col, varchar_col, date_col, time_col, timestamp_col ) VALUES ( 1, 2.0, 'three', 'four', 'five point zero zero zero zero zero zero zero zero zero zero ...', '2002-12-31', '11:59:00', '1999-12-31 23:59:59.00000' );
Date, time, and timestamp values are entered in order from the most significant digit to the least significant digit, and use punctuation to separate the individual fields.
The reason for requiring particular formats is that some formats are ambiguous. For example, in the United States, (where the usual date format is mm-dd-yyyy), 07-04-1776 is July 4, 1776. However, in Europe, (where the usual data format is dd-mm-yyyy), 07-04-1776 is April 7 1776.
Using a format that starts with the most significant digit and moves steadily toward the least significant digit has a number of advantages:
▪ All three data types (date, time, and timestamp) follow the same rule.
▪ The date format and the time format are both perfect subsets of the timestamp format.
▪ The rule is reasonably simple and is consistent with the way that western languages write numbers (most significant digit is furthest to the left).
▪ The format is obviously incompatible with the existing formats, there is no chance that a person will accidentally write one date (for example, 07-04-1776) and have it interpreted by the machine as another date.