The formatSQL Functions
EventHandlerBase has the following methods that return the names of server-specific functions ready to be embedded in SQL queries.
Example:
sql += "(CASE WHEN ("
+ formatSqlDaysBetween(context, currentDate,
"reserve_rs.date_start")
+ " > cancel_days)"
+ ...
| Function | Oracle | SQL Server |
|---|---|---|
| formatSqlConvertToInt(value) | TO_NUMBER(value) | CONVERT(INT,value) |
| formatSqlConvertIntToChar(value) | TO_CHAR(value) | CONVERT(CHAR, value) |
| formatSqlConvertToString(value) | TO_CHAR('value') |
CONVERT(CHAR, 'value') |
| formatSqlDateTime(isoDate, isoTime) | TO_DATE('isoDate isoTime','YYYY-MM-DD HH24:MI') | CONVERT(DATETIME,('isoDate isoTime',120) |
| formatSqlReplace0WithHuge(value) | CASE WHEN value = 0 THEN 9999999999 ELSE value END CASE | CASE value WHEN 0 THEN 9999999999 ELSE value END |
| formatSqlDummyTable() | FROM DUAL | |
| formatSqlDbCrLf() | CHR(13)||CHR(10) | CHAR(13)+CHAR(10) |
| formatSqlDbTab() | CHR(9) | CHAR(9) |
| formatSqlAddMinutes(isoTime, minutes) | isoToNative(isoTime) + minutes/60/24 | DATEADD(MI,minutes,isoToNative(isoTime)) |
| formatSqlAddMinutesToExpression(time, minutes) | time + minutes/60/24 | DATEADD(MI,minutes,time) |
| formatSqlAddDays(date, days) | isoToNative(isoDate) + days | DATEADD(dd,days,isoToNative(isoDate) |
| formatSqlAddDaysToExpression(date, days) | date + days | DATEADD(dd,days,date) |
| formatSqlDaysBetween(date1, date2) | isoToNative(isoDate2) - isoToNative(isoDate1) | DATEDIFF(dd,isoToNative(isoDate1),isoToNative(isoDate2)) |
| formatSqlHoursBetween(date1, time1, date2, time2) | TRUNC( ( formatSqlDateTime(isoDate2, isoTime2) - formatSqlDateTime(isoDate1, isoTime1) )*24 ) | DATEDIFF(hh,formatSqlDateTime(isoDate1, isoTime1),formatSqlDateTime(isoDate2, isoTime2)) |
| formatSqlMonthsBetween(date1, date2) | TRUNC(MONTHS_BETWEEN(isoToNative(isoDate2),isoToNative(isoDate1))) | DATEDIFF(mm,isoToNative(isoDate1),isoToNative(isoDate2)) |
| formatSqlWeeksBetween(date1, date2) | TRUNC( (isoToNative(isoDate2) - isoToNative(isoDate1))/7 ) | DATEDIFF(wk,isoToNative(isoDate1),isoToNative(isoDate2)) |
| formatSqlAddOneMonth() | ADD_MONTHS(SYSDATE,1) | DATEADD(mm,1, GETDATE()) |
| formatSqlConcat() | || | + |
| formatSqlIsNull(value) | NVL(value) | ISNULL(value) |