A number of language features, such as outer joins and scalar function calls, are commonly implemented by database management systems. The syntax for these features is often DBMS-specific, even when a standard syntax has been defined. The .NET support for escape sequences contain standard syntaxes for the following language features:
•Date, time, and timestamp literals
•Scalar functions such as numeric, string, and data type conversion functions
•Outer joins
The escape sequence used by .NET is:
{extension}
The escape sequence is recognized and parsed by the ADO.NET Data Provider, which replaces the escape sequences with data store-specific grammar.
Date, Time, and Timestamp Escape Sequences
The escape sequence for date, time, and timestamp literals is:
{literal-type 'value'}
where literal-type is one of the following:
literal-type
Description
Value Format
d
Date
yyyy-mm-dd
t
Time
hh:mm:ss [1]
ts
Timestamp
yyyy-mm-dd hh:mm:ss[.f...]
Note: If you receive an error while running a query in Visual Studio to insert data into the Date field of a table, ensure that your system's Date format is set to yyyy-mm-dd. If not, change it to yyyy-mm-dd.
Example
UPDATE Orders SET OpenDate={d '1997-01-29'} WHERE OrderID=1023
Scalar Functions
You can use scalar functions in SQL statements with the following syntax:
{fn scalar-function}
where scalar-function is a scalar function supported by the ADO.NET Data Provider.
ASCII BIT_LENGTH CHAR CHAR_LENGTH CHARACTER_LENGTH CONCAT LCASE or LOWER LEFT LENGTH LOCATE LTRIM OCTET_LENGTH POSITION REPLACE REPLICATE RIGHT RTRIM SPACE STUFF SUBSTRING UCASE or UPPER
ABS ACOS ASIN ATAN ATAN2 CEILING COS COT DEGREES EXP FLOOR LOG LOG10 MOD PI POWER RADIANS RAND ROUND SIGN SIN SQRT TAN TRUNCATE
CURDATE CURRENT_DATE CURTIME CURRENT_TIME CURRENT_TIMESTAMP DAYNAME DAYOFMONTH DAYOFYEAR EXTRACT HOUR MINUTE MONTH MONTHNAME NOW QUARTER SECOND TIMESTAMPADD TIMESTAMPDIFF WEEK YEAR
DATABASE USER
Outer Join Escape Sequences
.NET supports the SQL92 left, right, and full outer join syntax. The escape sequence for outer joins is:
{oj outer-join}
where outer-join is:
table-reference {LEFT | RIGHT | FULL} OUTER JOIN
{table-reference | outer-join} ON search-condition
where:
table-reference is a table name and search-condition is the join condition you want to use for the tables.