QODBC Time and Date Functions

CURDATE() – Returns the current date as a date value.

Example:

SELECT {fn CURDATE()} AS "CurDate" FROM Company

Returns:

CurDate

2004-10-01

CURTIME() – Returns the local time as a time value.

Example:

SELECT {fn CURTIME()} AS "CurTime" FROM Company

Returns:

CurTime

11:14:20

DAYNAME(date_exp) – Returns a character string containing the data source-specific name of the day (for example, Sunday, through Saturday or Sun. through Sat. for a data source that uses English) for the day portion of date_exp.

Example:

SELECT {fn DAYNAME({fn CURDATE()})} AS "CurDayName" FROM Company

Returns:

CurDayName

Friday

DAYOFMONTH(date_exp) – Returns the day of the month in date_exp as an integer value in the range of 1-31.

Example:

SELECT {fn DAYOFMONTH({fn CURDATE()})} AS "CurDayOfMonth" FROM Company

Returns:

CurDayOfMonth

1

DAYOFWEEK(date_exp) – Returns the day to the week in date_exp as an integer value in the range of 1-7, where 1 represents Sunday.

Example:

SELECT {fn DAYOFWEEK({fn CURDATE()})} AS "CurDayOfWeek" FROM Company

Returns:

CurDayOfWeek

6

DAYOFYEAR(date_exp) – Returns the day of the year in date_exp as an integer value in the range of 1-366.

Example:

SELECT {fn DAYOFYEAR({fn CURDATE()})} AS "CurDayOfYear" FROM Company

Returns:

CurDayOfYear

275

HOUR(time_exp) – Returns the hour in time_exp as an integer value in the range of 0-23.

Example:

SELECT {fn HOUR({fn CURTIME()})} AS "CurHour" FROM Company

Returns:

CurHour

15

MINUTE(time_exp) – Returns the minute in time_exp as an integer value in the range of 0-59.

Example:

SELECT {fn MINUTE({fn CURTIME()})} AS "CurMinute" FROM Company

Returns:

CurMinute

14

MONTH(date_exp) – Returns the month in date_exp as an integer value in the range of 1-12.

Example:

SELECT {fn MONTH({fn CURDATE()})} AS "CurMonth" FROM Company

Returns:

CurMonth

10

MONTHNAME(date_exp) – Returns a character string containing the data source-specific name of the month (for example, January through December or Jan. through Dec. for a data source that uses English) for the month portion of date_exp.

Example:

SELECT {fn MONTHNAME({fn CURDATE()})} AS "CurMonthName” FROM Company

Returns:

CurMonthName

October

NOW() – Returns the current date and time as a timestamp value.

Example:

SELECT {fn NOW()} AS "Now" FROM Company

Returns:

Now

2004-10-01 15:16:51.000

QUARTER(date_exp) – Returns the quarter in the date_exp as an integer value in the range of 1-4, where 1 represents January 1 through March 31.

Example:

SELECT {fn QUARTER({fn CURDATE()})} AS "CurQuarter" FROM Company

Returns:

CurQuarter

4

SECOND(time_exp) – Returns the second in time_exp as an integer value in the range of 0-59.

Example:

SELECT {fn SECOND({fn CURTIME()})} AS "CurSecond" FROM Company

Returns:

CurSecond

33

TIMESTAMPADD(interval, integer_exp, timestamp_exp) – Returns the timestamp calculated by adding integer_exp intervals of type interval to timestamp_exp. Valid values of interval are the following keywords: SQL_TSI_FRAC_SECOND, SQL_TSI_HOUR, SQL_TSI_SECOND, SQL_TSI_DAY, SQL_TSI_MINUTE, SQL_TSI_WEEK, SQL_TSI_MONTH, SQL_TSI_QUARTER, SQL_TSI_YEAR where fractional seconds are expressed in billionths of a second.

Notes:

If timestamp_exp is a time value and interval specifies days, weeks, months, quarters, or years, the date portion of timestamp_exp is set to the current date before calculating the resulting timestamp.

If timestamp_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:

SELECT Name, {fn TIMESTAMPADD(SQL_TSI_YEAR, 1, HiredDate)} AS "Anniversary" FROM Employee

Returns:

Name	Anniversary
Dan T. Miller	2007-11-01 00:00:00.000
Elizabeth N. Mason	2008-03-15 00:00:00.000
Gregg O. Schneider	2008-06-15 00:00:00.000

TIMESTAMPDIFF(interval, timestamp_exp1, timestamp_exp2) – Returns the integer number of intervals of type interval by which timestamp_exp2 is greater than timestamp_exp1. Valid values of interval are the following keywords: SQL_TSI_FRAC_SECOND, SQL_TSI_HOUR, SQL_TSI_SECOND, SQL_TSI_DAY, SQL_TSI_MINUTE, SQL_TSI_WEEK, SQL_TSI_MONTH, SQL_TSI_QUARTER, SQL_TSI_YEAR where fractional seconds are expressed in billionths of a second.

Note:

If either timestamp expression is a time value and interval specifies days, weeks, months, quarters, or years, the date portion of that timestamp is set to the current date before calculating the difference between the timestamps.

If either timestamp expression is a date value and interval specifies fractional seconds, seconds, minutes, or hours, the time portion of that timestamp is set to 0 before calculating the difference between the timestamps.

Example:

SELECT Name, {fn TIMESTAMPDIFF(SQL_TSI_YEAR, {fn CURDATE()}, HiredDate)} AS "YearsWorked” FROM Employee

Returns:

Name	YearsWorked
Dan T. Miller	2
Elizabeth N. Mason	3
Gregg O. Schneider	3

WEEK(date_exp) – Returns the week of the year in date_exp as an integer value in the range of 1-53.
Example:

SELECT {fn WEEK({fn CURDATE()})} AS "CurWeek" FROM Company

Returns:

CurWeek

40

YEAR(date_exp) – Returns the year in date_exp as an integer value.

Example:

SELECT {fn YEAR({fn CURDATE()})} AS "CurYear" FROM Company

Returns:

CurYear

2004

For more functions click here.

Examples:

If you need to convert a Timestamp to a Date, or a Date to a Timestamp, you the following CONVERT function:

SELECT {fn CONVERT({ts '2005-09-23 00:00:00.000'}, SQL_TYPE_DATE)} as ToDate,
{fn CONVERT(TxnDate, SQL_TIMESTAMP)} as ToTimeStamp, RefNumber FROM Invoice

To convert today's date to eight months ago, do:-

select {fn Curtime()}, TxnDate,SubTotal from Invoice NOSYNC
    where TxnDate> {d'2007-08-01'}
    and TxnDate> {fn CONVERT( {fn TIMESTAMPADD(SQL_TSI_MONTH,-8, {fn CURDATE()} )}, SQL_DATE)}

This will display modified Sales Order lines from 15 minutes ago:

Select * from SalesOrderLine Calldirect where timemodified >= {fn TIMESTAMPADD(SQL_TSI_MINUTE, -15, {fn NOW()})}

While this will display today's modifed Sales Order Lines:

Select * from SalesOrderLine CallDirect where timemodified >= {fn CONVERT({fn Curdate()}, SQL_TIMESTAMP)}