Date and Time Functions and Operators

This is a list of date and time functions and operators that can be used in TurboSQL.

Syntax

date + days
timestamp + days
time + minutes

Description

Adds a number of days to a date or timestamp. Adds a number of minutes to a time value.

Examples

CURRENT_DATE + 1 --Tomorrow's date
CURRENT_TIMESTAMP + 1 --Tomorrow's time exactly like now
CURRENT_TME + 60 --One hour from now
CURRENT_TIME + 0.25 --15 seconds later

Syntax

date - days
date1 - date2
timestamp - days
timestamp1 - timestamp2
time - minutes
time1 - time2

Description

Subtracts a number of days from a date or a timestamp. Subtracts a number of minutes from a time value. Calculates the number of days between two dates or timestamps. Calculates the number of minutes between two time values.

Examples

CURRENT_DATE - 1 --Yesterday
CURRENT_TIMESTAMP - 1 --24 hours ago
CURRENT_DATE - DATE'1/1/2006' --Number of days since the beginning of 2006
CURRENT_TIME - 60 --One hour ago
CURRENT_TIME - TIME'12:00 pm' --Number of hours since noon (may be negative)

CURRENT_DATE

Syntax

CURRENT_DATE

Description

Returns the date of the current day according to your system (local time).

CURRENT_TIME

Syntax

CURRENT_TIME

Description

Returns the time of the current millisecond according to your system (local time).

CURRENT_TIMESTAMP

Syntax

CURRENT_TIMESTAMP

Description

Returns the timestamp of the current millisecond (i.e. CURRENT_DATE and CURRENT_TIME together) according to your system (local time).

DATETIMESTR

Syntax

DATETIMESTR(TimeStamp, Precision)

Description

Calculates a string representation of the time stamp in the current locale. Precision is 2 for minutes, 3 for seconds and 4 for milliseconds.

EXTRACT

Syntax

EXTRACT(kind FROM date)

Description

Calculates a value from date. Kind is one of these:

YEAR	Returns the year.
MONTH	Returns the month.
DAY	Returns the day.
WEEKDAY	Returns the day of the week. 1 for Monday, 2 for Tuesday etc.
WEEKDAYNAME	Returns the name of the day of the week in the current locale.
WEEK	Returns the number of the week in the year according the ISO standard.
HOUR	Returns the hour.
MINUTE	Returns the minute.
SECOND	Returns the second.
MILLISECOND	Returns the millisecond.

Examples

EXTRACT(DAY FROM CURRENT_DATE)
EXTRACT(HOUR FROM CURRENT_TIME)
EXTRACT(SECOND FROM CURRENT_TIMESTAMP)
EXTRACT(WEEKDAYNAME FROM CURRENT_DATE)
EXTRACT(MILLISECOND FROM CURRENT_TIME)
EXTRACT(WEEK FROM CURRENT_TIMESTAMP)

MAKEDATE

Syntax

MAKEDATE(year, month, day)

Description

Returns the date value for the given date.

Example

SELECT * FROM MyTable WHERE Abs(Today - MakeDate(EXTRACT(YEAR FROM CURRENT_DATE), EXTRACT(MONTH FROM Birthday), EXTRACT(DAY FROM Birthday))) < 7

MAKETIMESTAMP

Syntax

MAKETIMESTAMP(year, month, day, hour, minute, second, millisecond)

Description

Returns the time stamp value for the given datetime.

MAKETIME

Syntax

MAKETIME(hour, minute, second, millisecond)

Description

Returns the time value for the given time.

TIMESTR

Syntax

TIMESTR(time, precision)

Description

Calculates a string representation of the time value in the current locale. Precision is 2 for minutes, 3 for seconds and 4 for milliseconds.