SQL_DateInterval

[This is preliminary documentation and is subject to change.]

The TOPICA Basic function SQL_DateInterval is used to generate logical SQL Expressions to check if the value of a date/time field is within a supplied date interval.

SQL_DateInterval takes care of converting date values to SQL in a culture-neutral manner. That is, it is guranteed to work no matter what regional settings, (particularly date format) is used on the servers (web server as well as the database server).

If you do NOT use TOPICA Basic functions to convert date/time values to SQL strings, it is very easy to get this wrong. You may for example end up with some code that works well in your development environment, but produces wrong results when moved to an othe´r environment with different regional settings.

SQL_DateInterval compares only the data values, ignoring any time component.

SQL_DateInterval also handles NULL values.

The value in the database field may be NULL. In this case the field is considered as within the interval.

One or both of the date values in the interval may be NULL. A NULL is handled as "no limitation", an open interval.

Syntax

Copy

SQL_DateInterval(fieldname, datefrom, dateto)

Result type: String.

Parameters

Name	Type	Description
fieldname	String	String identifying the database field to test (the database field must be of type DateTime).

Returns

SQL comparison expression.

Type: String.

Examples

TOPICA Basic expression:

Copy

SQL_DateInterval("MyField", Date(2009,3,1), Date(2009,3,5))

Result: "(MyField IS NULL) OR ((Convert(DateTime, '2009-03-01 00:00:00', 120)<=MyField) AND (MyField<Convert(DateTime, '2009-03-06 00:00:00', 120)))"

Note that the "dateto" parameter is this example is March 5th 2009, and the generated SQL contains MyField<Convert(DateTime, '2009-03-06 00:00:00', 120), i.e. the value in the database is checked against 0:00 hours, March 6th 2009. This ensures that any time of day on the 5th will be included.