ADD_MONTHS function (extension)
Syntax
ADD_MONTHS ( date_expression, integer_expression )
Description
The scalar function ADD_MONTHS adds to the date value specified by the date_expression, the given number of months specified by integer_expression and returns the resultant date value.
Example
SELECT *
FROM customer
WHERE ADD_MONTHS (start_date, 6) > SYSDATE ;
Notes
- The first argument must be of DATE type.
- The second argument to the function must be of numeric type.
- The result is of type DATE.
- If any of the arguments evaluate to null, the result is null.
CURDATE function (ODBC compatible)
Syntax
CURDATE ()
Description
CURDATE returns the current date as a DATE value. This function takes no arguments.
c-treeSQL statements can refer to CURDATE anywhere they can refer to a DATE expression. For example,
INSERT INTO objects (object_owner, object_id, create_date)
VALUES (USER, 1001, CURDATE()) ;
CURRENT_DATE SQL function
Syntax
CURRENT_DATE
Description
CURRENT_DATE returns the current date as a DATE value. A FairCom DB SQL statement can refer to CURRENT_DATE anywhere you can refer to a DATE expression.
Example
INSERT INTO objects(object_owner, object_id,create_date)
VALUES(user, 1002, CURRENT_DATE) ;
Note: SQL-99 compatible
CURTIME function (ODBC compatible)
Syntax
CURTIME ()
Description
CURTIME returns the current time as a TIME value. This function takes no arguments.
c-treeSQL statements can refer to CURTIME anywhere they can refer to a TIME expression. For example,
INSERT INTO objects (object_owner, object_id, create_time)
VALUES (USER, 1001, CURTIME()) ;
CURRENT_TIMESTAMP function
Syntax
CURRENT_TIMESTAMP
Description
CURRENT_TIMESTAMP is a synonymous replacement for SYSTIMESTAMP.
DATEADD function
Syntax
DATEADD( interval, integer_exp, date_time_exp )
Description
This scalar function is the same as the scalar function TIMESTAMPADD. Refer to TIMESTAMPADD for usage.
DATEDIFF function
Syntax
DATEDIFF( interval, date_time_exp1, date_time_exp2 )
Description
This scalar function is the same as the scalar function TIMESTAMPDIFF. Refer to TIMESTAMPDIFF for usage.
DAYNAME function (ODBC compatible)
Syntax
DAYNAME ( date_expression )
Description
Returns a character string containing the name of the day (for example, Sunday, through Saturday) for the day portion of date_expression. The argument date_expression can be the name of a column, the result of another scalar function, or a date or timestamp literal.
Example
SELECT *
FROM orders
WHERE order_no = 342 and DAYNAME(order_date)='SATURDAY';
ORDER_NO ORDER_DATE REFERENCE CUST_NO
342 08/10/1991 tdfg/101 10001
1 record selected
DAYOFMONTH function (ODBC compatible)
Syntax
DAYOFMONTH ( date_expression )
Description
The scalar function DAYOFMONTH returns the day of the month in the argument as a short integer value in the range of 1 - 31.
Example
SELECT *
FROM orders
WHERE DAYOFMONTH (order_date) = 14 ;
Notes
- The argument to the function must be of type DATE.
- The argument must be specified in the format MM/DD/YYYY.
- The result is of type SHORT.
- If the argument expression evaluates to null, the result is null.
DAYOFWEEK function (ODBC compatible)
Syntax
DAYOFWEEK ( date_expression )
Description
The scalar function DAYOFWEEK returns the day of the week in the argument as a short integer value in the range of 1 - 7.
Example
SELECT *
FROM orders
WHERE DAYOFWEEK (order_date) = 2 ;
Notes
- The argument to the function must be of type DATE.
- The argument must be specified in the format MM/DD/YYYY.
- The result is of type SHORT.
- If the argument expression evaluates to null, the result is null.
DAYOFYEAR function (ODBC compatible)
Syntax
DAYOFYEAR ( date_expression )
Description
The scalar function DAYOFYEAR returns the day of the year in the argument as a short integer value in the range of 1 - 366.
Example
SELECT *
FROM orders
WHERE DAYOFYEAR (order_date) = 300 ;
Notes
- The argument to the function must be of type DATE.
- The argument must be specified in the format MM/DD/YYYY.
- The result is of type SHORT.
- If the argument expression evaluates to null, the result is null.
EXTRACT SQL Function
Syntax
EXTRACT(date_time_field FROM date_time_expression)
date_time_field ::
| SECOND
| MINUTE
| HOUR
| DAY
| MONTH
| YEAR
Description
The scalar function EXTRACT returns the a date and time field from a date and time expression.
Example
SELECT SYSDATE() ,EXTRACT(MONTH FROM SYSDATE()) 'month'
FROM admin.syacalctable;
SYSDATE MONTH
---------- -------
11/21/2004 11
Details
The date_time_expression can evaluate to a TIME, DATE or TIMESTAMP data type.
The date_time_field and date_time_expression must be compatible.
date_time_expression is a TIME, DATE or TIMESTAMP from which EXTRACT returns the specified date_time_field.
date_time_field keywords that specify the field to be extracted from the date_time_expression.
- SECOND specifies that second part has to be extracted from the date_time_expression.
- MINUTE specifies that minute part has to be extracted from the date_time_expression.
- HOUR specifies that hour part has to be extracted from the date_time_expression.
- DAY specifies that day part has to be extracted from the date_time_expression.
- MONTH specifies that month part has to be extracted from the date_time_expression.
- YEAR specifies that year part has to be extracted from the date_time_expression.
Note: SQL-99 and ODBC compatible
GETDATE function (ODBC compatible)
Syntax
GETDATE( )
Description
Synonymous with NOW().
HOUR function (ODBC compatible)
Syntax
HOUR ( time_expression )
Description
The scalar function HOUR returns the hour in the argument as a short integer value in the range of 0 - 23.
Example
SELECT *
FROM arrivals
WHERE HOUR (in_time) < 12 ;
Notes
- The argument to the function must be of type TIME.
- The argument must be specified in the format hh:mi:ss.
- The result is of type SHORT.
- If the argument expression evaluates to null, the result is null.
LAST_DAY function (extension)
Syntax
LAST_DAY ( date_expression )
Description
The scalar function LAST_DAY returns the date corresponding to the last day of the month containing the argument date.
Example
SELECT *
FROM orders
WHERE LAST_DAY (order_date) + 1 = '08/01/1991' ;
Notes
- The argument to the function must be of type DATE.
- The result is of type DATE.
- If the argument expression evaluates to null, the result is null.
LOCALTIME SQL Function
Syntax
LOCALTIME([time_precision])
Description
The scalar function LOCALTIME returns current time as TIME value. This function takes an optional argument which decides the milliseconds precision in the result. The optional argument is an integer ranging between 0 and 3.
FairCom DB SQL statements can refer to LOCALTIME anywhere they can refer to a TIME expression.
Example
SELECT LOCALTIME(3) 'LOCALTIME' FROM admin.syscalctable;
LOCALTIME
-----------
12:34:000
1 record selected
Details
The time_precision value is always set as three irrespective of what the user has specified and milliseconds is always returned as ‘000’.
LOCALTIMESTAMP SQL Function
Syntax
LOCALTIMESTAMP( [timestamp_precision] )
Description
The scalar function LOCALTIMESTAMP returns current date and time as TIMESTAMP value. This function takes an optional argument which decides the milliseconds precision in the result. The optional argument timestamp_precision is an integer ranging between 0 and 9.
FairCom DB SQL statements can refer to LOCALTIMESTAMP anywhere they can refer to a TIMESTAMP expression.
Example
SELECT LOCALTIMESTAMP(3) 'localtimestamp' FROM
admin.syscalctable ;
LOCALTIMESTAMP
--------------
12/05/2004 12:25:000
1 record selected
Details
The timestamp_precision value is always set as 3 irrespective of what the user has specified and milliseconds is always returned as ‘000’.
MINUTE function (ODBC compatible)
Syntax
MINUTE ( time_expression )
Description
The scalar function MINUTE returns the minute value in the argument as a short integer in the range of 0 - 59.
Example
SELECT *
FROM arrivals
WHERE MINUTE (in_time) > 10 ;
Notes
- The argument to the function must be of type TIME.
- The argument must be specified in the format HH:MI:SS.
- The result is of type SHORT.
- If the argument expression evaluates to null, the result is null.
MONTHNAME function (ODBC compatible)
Syntax
MONTHNAME ( date_expression )
Description
Returns a character string containing the name of the month (for example, January, through December) for the month portion of date_expression. Argument date_expression can be name of a column, the result of another scalar function, or a date or timestamp literal.
Example
SELECT *
FROM orders
WHERE order_no =346 and MONTHNAME(order_date)='JUNE';
ORDER NO ORDER DATE REFERENCE CUST NO
346 06/01/1991 87/rd 10002
1 record selected
MONTH function (ODBC compatible)
Syntax
MONTH ( date_expression )
Description
The scalar function MONTH returns the month in the year specified by the argument as a short integer value in the range of 1 - 12.
Example
SELECT *
FROM orders
WHERE MONTH (order_date) = 6 ;
Notes
- The argument to the function must be of type DATE.
- The argument must be specified in the format MM/DD/YYYY.
- The result is of type SHORT.
- If the argument expression evaluates to null, the result is null.
MONTHS_BETWEEN function (extension)
Syntax
MONTHS_BETWEEN ( date_expression, date_expression )
Description
The scalar function MONTHS_BETWEEN computes the number of months between two date values corresponding to the first and second arguments.
Example
SELECT MONTHS_BETWEEN (SYSDATE, order_date)
FROM orders
WHERE order_no = 1002 ;
Notes
- The first and the second arguments to the function must be of type DATE.
- The result is of type INTEGER.
- The result is negative if the date corresponding to the second argument is greater than that corresponding to the first argument.
- If any of the arguments expression evaluates to null, the result is null.
NEXT_DAY function (extension)
Syntax
NEXT_DAY ( date_expression, day_of_week )
Description
The scalar function NEXT_DAY returns the minimum date that is greater than the date corresponding to the first argument for which the day of the week is same as that specified by the second argument.
Example
SELECT NEXT_DAY (order_date, 'MONDAY')
FROM orders ;
Notes
- The first argument to the function must be of type DATE.
- The second argument to the function must be of type NCHAR or NVARCHAR for Unicode builds and type CHAR or VARCHAR for ANSI builds. The result of the second argument must be a valid day of week (‘SUNDAY’, ‘MONDAY’ etc.)
- The result is of type DATE.
- If any of the argument expressions evaluates to null, the result is null.
NOW function (ODBC compatible)
Syntax
NOW ( )
Description
NOW returns the current date and time as a TIMESTAMP value. This function takes no arguments.
QUARTER function (ODBC compatible)
Syntax
QUARTER ( time_expression )
Description
The scalar function QUARTER returns the quarter in the year specified by the argument as a short integer value in the range of 1 - 4.
Example
SELECT *
FROM orders
WHERE QUARTER (order_date) = 3 ;
Notes
- The argument to the function must be of type DATE.
- The argument must be specified in the format MM/DD/YYYY.
- The result is of type SHORT.
- If the argument expression evaluates to null, the result is null.
SECOND function (ODBC compatible)
Syntax
SECOND ( time_expression )
Description
The scalar function SECOND returns the seconds in the argument as a short integer value in the range of 0 - 59.
Example
SELECT *
FROM arrivals
WHERE SECOND (in_time) <= 40 ;
Notes
- The argument to the function must be of type TIME.
- The argument must be specified in the format HH:MI:SS.
- The result is of type SHORT.
- If the argument expression evaluates to null, the result is null.
SYSDATE function (extension)
Syntax
SYSDATE [ ( ) ]
Description
SYSDATE returns the current date as a DATE value. This function takes no arguments, and the trailing parentheses are optional.
FairCom DB SQL statements can refer to SYSDATE anywhere they can refer to a DATE expression. For example,
INSERT INTO objects (object_owner, object_id, create_date)
VALUES (USER, 1001, SYSDATE) ;
SYSTIME function (extension)
Syntax
SYSTIME [ ( ) ]
Description
SYSTIME returns the current time as a TIME value. This function takes no arguments, and the trailing parentheses are optional.
FairCom DB SQL statements can refer to SYSTIME anywhere they can refer to a TIME expression. For example,
INSERT INTO objects (object_owner, object_id, create_time)
VALUES (USER, 1001, SYSTIME) ;
SYSTIMESTAMP function (extension)
Syntax
SYSTIMESTAMP [ ( ) ]
Description
SYSTIMESTAMP returns the current date and time as a TIMESTAMP value. This function takes no arguments, and the trailing parentheses are optional.
The following c-treeSQL example shows the different formats for SYSDATE, SYSTIME, and SYSTIMESTAMP:
SELECT SYSDATE FROM test;
SYSDATE
-------
09/13/1994
1 record selected
SELECT SYSTIME FROM test;
SYSTIME
-------
14:44:07:000
1 record selected
SELECT SYSTIMESTAMP FROM test;
SYSTIMESTAMP
------------
1994-09-13 14:44:15:000
1 record selected
TIMESTAMPADD function (ODBC compatible)
Syntax
TIMESTAMPADD(interval, integer_exp, date_time_exp)
interval::
SQL_TSI_FRAC_SECOND
| SQL_TSI_SECOND
| SQL_TSI_MINUTE
| SQL_TSI_HOUR
| SQL_TSI_DAY
| SQL_TSI_DAYOFYEAR
| SQL_TSI_WEEK
| SQL_TSI_MONTH
| SQL_TSI_QUARTER
| SQL_TSI_YEAR
| MILLISECOND
| SECOND
| MINUTE
| HOUR
| DAY
| DAYOFYEAR
| WEEK
| MONTH
| QUARTER
| YEAR
Description
Returns the timestamp calculated by adding integer_exp intervals of type interval to timestamp_exp.
Arguments
- interval - Keywords that specify the interval to add to timestamp_exp. The SQL_TSI_FRAC_SECOND keyword specifies fractional seconds as milliseconds.
Keywords such as SECOND and HOUR are functionally equivalent to the keywords with the same name but having prefix SQL_TSI, i.e., the keywords such as SQL_TSI_SECOND and SQL_TSI_HOUR. The keyword MILLISECOND is the same as SQL_TSI_FRAC_SECOND and this interval means milliseconds. The keyword DAYOFYEAR or SQL_TSI_DAYOFYEAR indicates an interval value which ranges from 1 to 366 (See scalar function DAYOFYEAR()).
- integer_exp - The number of interval values to add to timestamp_exp. integer_exp can be any expression that evaluates to an integer data type.
- date_time_exp - A date-time expression from which TIMESTAMPADD calculates the return value. If date_time_exp is a date value and interval specifies fractional seconds, seconds, minutes, or hours, the time portion of timestamp_exp is set to 0 before calculating the resulting timestamp.
Example
The following example displays the current system time and uses the TIMESTAMPADD scalar function to add 8 hours to it.
> select systime, timestampadd(sql_tsi_hour, 8, systime) from syscalctable;
15:03:57:000 06/08/1999 23:03:57:000
------------ -----------------------
15:03:57:000, 1999-06-08 23:03:57:000
TIMESTAMPDIFF function (ODBC compatible)
Syntax
TIMESTAMPDIFF(interval, date_time_exp1, date_time_exp2)
interval::
SQL_TSI_FRAC_SECOND
| SQL_TSI_SECOND
| SQL_TSI_MINUTE
| SQL_TSI_HOUR
| SQL_TSI_DAY
| SQL_TSI_WEEK
| SQL_TSI_MONTH
| SQL_TSI_QUARTER
| SQL_TSI_YEAR
Description
Returns an integer representing the number of intervals by which date_time_exp2 is greater than date_time_exp1.
Arguments
- interval - Keywords that specify the interval in which to express the difference between the two date-time arguments. The SQL_TSI_FRAC_SECOND keyword specifies fractional seconds in milliseconds.
- date_time_exp1 - A date-time expression which TIMESTAMPDIFF subtracts from date_time_exp2.
- date_time_exp2 - A date-time expression from which TIMESTAMPDIFF subtracts date_time_exp1.
Example
The following example displays difference in seconds between the current system time and one day later.
> select timestampdiff(sql_tsi_second, sysdate, sysdate + 1) from syscalctable;
86400
-----
86400
Notes
If either date-time expression is a time value and interval specifies days, weeks, months, quarters, or years, the date portion of that expression is set to the current date before calculating the difference between the expressions.
If either date-time expression is a date value and interval specifies fractional seconds, seconds, minutes, or hours, the time portion of that expression is set to 0 before calculating the difference between the expressions.
WEEK function (ODBC compatible)
Syntax
WEEK ( time_expression )
Description
The scalar function WEEK returns the week of the year as a short integer value (range 1 - 53).
Example
SELECT *
FROM orders
WHERE WEEK (order_date) = 5 ;
Notes
- The argument to the function must be of type DATE.
- The argument must be specified in the format MM/DD/YYYY.
- The result is of type SHORT.
- If the argument expression evaluates to null, the result is null.
YEAR function (ODBC compatible)
Syntax
YEAR ( date_expression )
Description
The scalar function YEAR returns the year as a short integer value in the range of 0 - 9999.
Example
SELECT *
FROM orders
WHERE YEAR (order_date) = 1992 ;
Notes
- The argument to the function must be of type DATE.
- The argument must be specified in the format MM/DD/YYYY.
- The result is of type SHORT.
- If the argument expression evaluates to null, the result is null.