| SQL_Format |
[This is preliminary documentation and is subject to change.]
The TOPICA Basic function SQL_Format is used create an SQL expressions for any value.
This function may be used in constructing SQL queries (for use in reports etc.).
SyntaxSQL_Format(inputExpression)
ParametersName | Type | Description |
|---|---|---|
inputExpression | Integer, Double, String, DateTime | Expression to convert to SQL constant. |
ReturnsSQL representation of parameter inputExpression.
Type: String.
ExamplesSQL_Format("mystring")Result: "'mystring'".
SQL_Format(Date(2009,5,6))
Result: "Convert(DateTime, '2009-05-06 00:00:00', 120)".
See remarks below.
RemarksExtra care should always be used when constructing SQL containing constant date/time values. Example:
SELECT * FROM MyTable WHERE MyDateField=06-05-2009
Will this query select records from June 5th or May 6th? Answer: it depends on the regional settings on the database server - the date format may be MM-DD-YYYY (U.S) or DD-MM-YYYY (european). This may cause problems, when you develop and test your application on one server, and later deploy it to some other server for production.
The proper way to handle this is to use a specific, unambiguous date format. Rewrite the query to this (if you intended to find records on May 6th):
SELECT * FROM MyTable WHERE MyDateField=Convert(DateTime, '2009-05-06 00:00:00', 120)
The TOPICA Basic function SQL_Format is a convenience function, that handles this automatically. E.g. a TOPICA Basic expression to generate above SQL query should look like:
"SELECT * FROM MyTable WHERE MyDateField=" + SQL_Format(Date(2009,5,6))