• Login

DataSoft Corporation

Working with Literal Values

String Values

String constants may be expressed in SQL statements by surrounding the given string of characters with single quotes. Single quotes contained within the string itself require a preceding single quote as an escape character.

Examples

In the first example, the apostrophe or single quote contained within the string must be escaped by another single quote.

SELECT * FROM t1 WHERE c1 = ‘Roberta’s Restaurant’
SELECT STREET FROM address WHERE city LIKE ‘san’

Date Values

Date constants may be expressed in SQL statements as a character string or embedded in a vendor string. The first case is treated as data type SQL_CHAR and the vendor string representation is treated as a value of type SQL_DATE. This becomes important when conversions are attempted.

Pervasive PSQL partially supports extended SQL grammar, as outlined in this function.

Pervasive PSQL supports the following date literal format: 'YYYY-MM-DD'.

Dates may be in the range of year 0 to 9999.

Examples

The next two statements return all the classes whose start date is after 1995-06-05.

SELECT * FROM Class WHERE Start_Date > '1995-06-05'
SELECT * FROM Class WHERE Start_Date > {d '1995-06-05'}

Time Values

Pervasive PSQL supports the following time literal format: 'HH:MM:SS'.

Time constants may be expressed in SQL statements as a character string or embedded in a vendor string. The character string representation is treated as a string of type SQL_CHAR and the vendor string representation as a value of type SQL_TIME.

Pervasive PSQL partially supports extended SQL grammar, as outlined in this function.

Examples

The following two statements retrieve records from the class table where the start time for the classes is 14:00:00.

SELECT * FROM Class WHERE Start_time = '14:00:00'
SELECT * FROM Class WHERE Start_time = {t '14:00:00'}

Timestamp Values

Timestamp constants may be expressed in SQL statements as a character string or embedded in a vendor string. Pervasive PSQL treats the character string representation as a string of type SQL_CHAR and the vendor string representation as a value of type SQL_TIMESTAMP.

Pervasive PSQL partially supports extended ODBC SQL grammar, as outlined in this function.

Pervasive PSQL supports the following timestamp literal format: 'YYYY-MM-DD HH:MM:SS.MMM'

Examples

The next two statements retrieve records from the Billing table where the start day and time for the log is 1996-03-28 at 17:40:49.

SELECT * FROM Billing WHERE log = '1996-03-28 17:40:49'
SELECT * FROM Billing WHERE log = {ts '1996-03-28 17:40:49'}

Date Arithmetic

Pervasive PSQL supports adding or subtracting an integer from a date where the integer is the number of days to add or subtract, and the date is embedded in a vendor string. (This is equivalent to executing a convert on the date).

Pervasive PSQL also supports subtracting one date from another to yield a number of days.

Examples

SELECT * FROM person P, Class C WHERE p.Date_Of_Birth < ' 1973-09-05' AND c.Start_date >{d '1995-05-08'} + 30
Thursday, 18 April 2019 Posted in Characteristics