Calendar Date/Time Functions
The calendar date/time functions manipulate data of the data types DATE and
DATETIME based on a calendar year. You must select these functions together with
another column; they cannot be selected alone.
Functions
CURRENT_DATE
This function returns the current date. The date is determined by the system in
which the Analytics Server is running.
Syntax
CURRENT_DATE
CURRENT_TIME
This function returns the current time. The time is determined by the system in
which the Analytics Server is running.
note: The Analytics Server does not cache queries that contain this function.
Syntax
CURRENT_TIME(integer)
Where
integer is any integer representing the number of digits of precision with which
to display the fractional second. The argument is optional; the function returns
the default precision when no argument is specified.
CURRENT_TIMESTAMP
This function returns the current date/timestamp. The timestamp is determined by
the system in which the Analytics Server is running.
note: The Analytics Server does not cache queries that contain this function.
Syntax
CURRENT_TIMESTAMP(integer)
Where
integer is any integer representing the number of digits of precision with which
to display the fractional second. The argument is optional; the function returns
the default precision when no argument is specified.
DAY_OF_QUARTER
This function returns a number (between 1 and 92) corresponding to the day of
the quarter for the specified date.
Syntax
DAY_OF_QUARTER(dateExpr)
Where
dateExpr is any expression that evaluates to a date.
DAYNAME
This function returns the name of the day of the week for a specified date.
Syntax
DAYNAME(dateExpr)
Where
dateExpr is any expression that evaluates to a date.
DAYOFMONTH
This function returns the number corresponding to the day of the month for a specified
date.
Syntax
DAYOFMONTH(dateExpr)
Where
dateExpr is any expression that evaluates to a date.
DAYOFWEEK
This function returns a number between 1 and 7 corresponding to the day of the
week, Sunday through Saturday, for a specified date. For example, the number 1
corresponds to Sunday, and the number 7 corresponds to Saturday.
Syntax
DAYOFWEEK(dateExpr)
Where
dateExpr is any expression that evaluates to a date.
DAYOFYEAR
This function returns the number (between 1 and 366) corresponding to the day of
the year for a specified date.
Syntax
DAYOFYEAR(dateExpr)
Where
dateExpr is any expression that evaluates to a date.
HOUR
This function returns a number (between 0 and 23) corresponding to the hour for a
specified time. For example, 0 corresponds to 12 a.m. and 23 corresponds to 11
p.m.
Syntax
HOUR(timeExpr)
Where
timeExpr is any expression that evaluates to a time.
MINUTE
This function returns a number (between 0 and 59) corresponding to the minute for
a specified time.
Syntax
MINUTE(timeExpr)
Where
timeExpr is any expression that evaluates to a time.
MONTH
This function returns the number (between 1 and 12) corresponding to the month
for a specified date.
Syntax
MONTH(dateExpr)
Where
dateExpr is any expression that evaluates to a date.
MONTH_OF_QUARTER
This function returns the number (between 1 and 3) corresponding to the month in
the quarter for a specified date.
Syntax
MONTH_OF_QUARTER(dateExpr)
Where
dateExpr is any expression that evaluates to a date.
MONTHNAME
This function returns the name of the month for a specified date.
Syntax
MONTHNAME(dateExpr)
Where
dateExpr is any expression that evaluates to a date.
NOW
This function returns the current timestamp. The NOW function is equivalent to the
CURRENT_TIMESTAMP function.
Syntax
NOW()
QUARTER_OF_YEAR
This function returns the number (between 1 and 4) corresponding to the quarter
of the year for a specified date.
Syntax
QUARTER_OF_YEAR(dateExpr)
Where
dateExpr is any expression that evaluates to a date.
SECOND
This function returns the number (between 0 and 59) corresponding to the seconds
for a specified time.
Syntax
SECOND(timeExpr)
Where
timeExpr is any expression that evaluates to a time.
TIMESTAMPADD
This function adds a specified number of intervals to a specified timestamp, and
returns a single timestamp.
In the simplest scenario, this function adds the specified integer value to the
appropriate component of the timestamp, based on the interval. Adding a week
translates to adding seven days, and adding a quarter translates to adding three
months. A negative integer value results in a subtraction (such as going back in
time).
An overflow of the specified component (such as more than 60 seconds, 24 hours,
12 months, and so on) necessitates adding an appropriate amount to the next
component. For example, when adding to the day component of a timestamp,
this function considers overflow and takes into account the number of days in a
particular month (including leap years when February has 29 days).
When adding to the month component of a timestamp, this function verifies that
the resulting timestamp has enough days for the day component. For example,
adding 1 month to 2000-05-31 does not result in 2000-06-31 because June does not
have 31 days. This function reduces the day component to the last day of the
month, 2000-06-30 in this example.
A similar issue arises when adding to the year component of a timestamp having a
month component of February and a day component of 29 (that is, last day of
February in a leap year). If the resulting timestamp does not fall on a leap year, the
function reduces the day component to 28.
Syntax
TIMESTAMPADD(interval, intExpr, timestamp)
Where
interval is the specified interval. Valid values are: SQL_TSI_SECOND,
SQL_TSI_MINUTE, SQL_TSI_HOUR, SQL_TSI_DAY, SQL_TSI_WEEK, SQL_TSI_MONTH,
SQL_TSI_QUARTER, SQL_TSI_YEAR.
intExpr is any expression that evaluates to an integer value.
timestamp is any valid timestamp. This value is used as the base in the calculation
A null integer expression or a null timestamp passed to this function results in a
null return value.
TIMESTAMPDIFF
This function first determines the timestamp component that corresponds to the
specified interval parameter, and then looks at the higher order components of
both timestamps to calculate the total number of intervals for each timestamp.
For example, if the specified interval corresponds to the month component, the
function calculates the total number of months for each timestamp by adding the
month component and twelve times the year component. Then the function subtracts
the first timestamp’s total number of intervals from the second timestamp’s
total number of intervals.
The TIMESTAMPDIFF function rounds up to the next integer whenever fractional
intervals represent a crossing of an interval boundary. For example, the difference
in years between 1999-12-31 and 2000-01-01 is one year because the fractional
year represents a crossing from one year to the next (such as 1999 to 2000). By
contrast, the difference between 1999-01-01 and 1999-12-31 is zero years because
the fractional interval falls entirely within a particular year (that is, 1999).
When calculating the difference in weeks, the function calculates the difference
in days and divides by seven before rounding. Additionally, the function takes into
account how the parameter FIRST_DAY_OF_THE_WEEK has been configured in the
NQSConfig.INI file. For example, with Sunday as the start of the week, the difference
in weeks between 2000-07-06 (a Thursday) and 2000-07-10 (the following
Monday) results in a value of 1 week. With Tuesday as the start of the week, however,
the function would return zero weeks since the fractional interval falls entirely
within a particular week. When calculating the difference in quarters, the function
calculates the difference in months and divides by three before rounding.
Syntax
TIMESTAMPDIFF(interval, timestamp1, timestamp2)
Where
interval is the specified interval. Valid values are: SQL_TSI_SECOND,
SQL_TSI_MINUTE, SQL_TSI_HOUR, SQL_TSI_DAY, SQL_TSI_WEEK, SQL_TSI_MONTH,
SQL_TSI_QUARTER, SQL_TSI_YEAR.
timestamp1 is any valid timestamp.
timestamp2 is any valid timestamp.
WEEK_OF_QUARTER
This function returns a number (between 1 and 13) corresponding to the week of
the quarter for the specified date.
Syntax
WEEK_OF_QUARTER(dateExpr)
Where
dateExpr is any expression that evaluates to a date.
WEEK_OF_YEAR
This function returns a number (between 1 and 53) corresponding to the week of
the year for the specified date.
Syntax
WEEK_OF_YEAR(dateExpr)
Where
dateExpr is any expression that evaluates to a date.
YEAR
This function returns the year for the specified date.
Syntax
YEAR(dateExpr)
Where
dateExpr is any expression that evaluates to a date