As we have already shown above, SQL requires that values be expressed in a particular way. For example, character strings must be delimited by single quote 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.
Below are some examples of how to format input data for most of the data types that solidDB® supports. We will show this in the form of a simple SQL script that you can execute if you wish. Note that in this script, 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 we did 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' );
As you can see, timestamp values are entered in order from the “most significant” digit to the “least significant” digit. Similarly, date and time values are also entered from the most significant digit to the least significant digit. And all 3 of these data types (timestamp, date, time) use punctuation to separate individual fields.
The reason for requiring particular formats is that some of the other possible formats are ambiguous. For example, to someone in the U.S., “07-04-1776” is July 4, 1776, since Americans usually write dates in the “mm-dd-yyyy” (or “mm/dd/yyyy”) format. But to a person from Europe, this date is obviously April 7, not July 4th, since most Europeans write dates in the “dd-mm-yyyy” format. Although it may seem that the problem of having too many formats is not well solved by adding still another format, there are some advantages to SQL’s approach of using a format that starts with the most significant digit and moves steadily toward the least significant digit. First, it means that all three data types (date, time, and timestamp) follow the same rule. Second, the date format and the time format are both perfect subsets of the timestamp format. Third, although it is yet another format to memorize, the rule is reasonably simple and is consistent with the way that “western” languages write numbers (most significant digit is furthest to the left). Finally, by being 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.