SQL Basics: Constants

In some SQL statements a numeric, character, or date data value must be expressed in text form. For example, in this INSERT statement, which adds a salesperson to the database:

I INSERT INTO SALESREPS (EMPL_NUM, NAME, QUOTA, HIRE_DATE, SALES)

VALUES (115, ‘Dennis Irving’, 175000.00,  ’21-JUN-90′, 0.00)

the value for each column in the newly inserted row is specified in the VALUES clause. Constant data values are also used in expressions, such as in this SELECT statement:

SELECT CITY

FROM OFFICES

WHERE TARGET > (1.1 * SALES) + 10000.00

The ANSI/ISO SQL standard specifies the format of numeric and string constants, or literals, which represent specific data values. These conventions are followed by most SQL implementations.

1. Numeric Constants

Integer and decimal constants (also called exact numeric literals) are written as ordinary decimal numbers in SQL statements, with an optional leading plus or minus sign.

21   -375    2000.00     +497500.8778

You must not put a comma between the digits of a numeric constant, and not all SQL dialects allow the leading plus sign, so it’s best to avoid it. For money data, most SQL implementations simply use integer or decimal constants, although some allow the constant to be specified with a currency symbol:

$0.75    $5000.00       $-567.89

Floating point constants (also called approximate numeric literals) are specified using the E notation commonly found in programming languages such as C and FORTRAN.

Here are some valid SQL floating point constants:

1.5E3    -3.14159E1     2.5E-7     0.783926E21

The E is read “times ten to the power of,” so the first constant becomes “1.5 times ten to the third power,” or 1500.

2. String Constants

The ANSI/ISO standard specifies that SQL constants for character data be enclosed in single quotes (‘. . .’), as in these examples:

‘Jones, John J.’ ‘New York’ ‘Western’

If a single quote is to be included in the constant text, it is written within the constant as two consecutive single quote characters. Thus, this constant value:

‘I can”t’

becomes the seven-character string “I can’t”.

Some SQL implementations, such as SQL Server and Informix, accept string constants enclosed in double quotes (“. . .”):

 “Jones, John J.” “New York” “Western”

Unfortunately, the double quotes can pose portability problems with other SQL products. The SQL2 standard provides the additional capability to specify string constants from a specific national character set (for example, French or German) or from a user-defined character set. The user-defined character set capabilities have typically not been implemented in mainstream SQL products.

3. Date and Time Constants

In SQL products that support date/time data, constant values for dates, times, and time intervals are specified as string constants. The format of these constants varies from one DBMS to the next. Even more variation is introduced by the differences in the way dates and times are written in different countries.

DB2 supports several different international formats for date, time, and timestamp constants, as shown in Table 5-5. The choice of format is made when the DBMS is installed. DB2 also supports durations specified as special constants, as in this example:

HIRE_DATE + 30 DAYS

Note that a duration can’t be stored in the database, however, because DB2 doesn’t have an explicit DURATION data type.

SQL Server also supports date/time data and accepts a variety of different formats for date and time constants. The DBMS automatically accepts all of the alternate formats, and you can intermix them if you like. Here are some examples of legal SQL Server date constants:

March 15, 1990 Mar 15 1990          3/15/1990    3-15-90     1990 MAR 15

and here are some legal time constants:

15:30:25      3:30:25 PM 3:30:25 pm 3 PM

Oracle dates and times are also written as string constants, using this format:

15-MAR-90

You can also use Oracle’s built-in TO_DATE() function to convert date constants written in other formats, as in this example:

 SELECT NAME, AGE

FROM SALESREPS

WHERE HIRE_DATE = TO_DATE(‘JUN 14 1989’, ‘MON DD YYYY’)

The SQL2 standard specifies a format for date and time constants, based on the ISO format in Table 5-5, except that time constants are written with colons instead of periods separating the hours, minutes, and seconds.

4. Symbolic Constants

In addition to user-supplied constants, the SQL language includes special symbolic constants that return data values maintained by the DBMS itself. For example, in some DBMS brands, the symbolic constant CURRENT_DATE yields the value of the current date and can be used in queries such as the following, which lists the salespeople whose hire date is still in the future:

 SELECT NAME, HIRE_DATE

FROM SALESREPS

WHERE HIRE_DATE > CURRENT_DATE

The SQL1 standard specified only a single symbolic constant (the USER constant described in Chapter 15), but most SQL products provide many more. Generally, a symbolic constant can appear in a SQL statement anywhere that an ordinary constant of the same data type could appear. The SQL2 standard adopted the most useful symbolic constants from current SQL implementations and provides for CURRENT_ DATE, CURRENT_TIME, and CURRENT_TIMESTAMP (note the underscores) as well as USER, SESSION_USER, and SYSTEM_USER.

Some SQL products, including SQL Server, provide access to system values through built-in functions rather than symbolic constants. The SQL Server version of the preceding query is:

 SELECT NAME, HIRE_DATE

FROM SALESREPS

WHERE HIRE_DATE > GETDATE()

Built-in functions are described later in this chapter, in the section “Built-In Functions.”

Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.

Leave a Reply

Your email address will not be published. Required fields are marked *