User's Guide Part VI. SQL Anywhere Reference Chapter 42. Watcom-SQL FunctionsDate and time functions perform conversion, extraction or manipulation operations on date and time data types and can return date and time information.
Date and time function:
| DATE ( expression )
| DATEFORMAT ( datetime-expr, string-expr )
| DATENAME ( datepart, date-expr )
| DATETIME ( expression )
| DAY ( date-expr )
| DAYNAME( date-expr )
| DAYS ( date-expr )
| DAYS ( date-expr, date-expr )
| DAYS ( date-expr, integer-expr )
| DOW ( date-expr )
| HOUR ( datetime-expr )
| HOURS ( datetime-expr )
| HOURS ( datetime-expr, datetime-expr )
| HOURS ( datetime-expr, integer-expr )
| MINUTE ( datetime-expr )
| MINUTES ( datetime-expr )
| MINUTES ( datetime-expr, datetime-expr )
| MINUTES ( datetime-expr, integer-expr )
| MONTH ( date-expr )
| MONTHNAME ( date-expr )
| MONTHS ( date-expr )
| MONTHS ( date-expr, date-expr )
| MONTHS ( date-expr, integer-expr )
| NOW ( * )
| QUARTER( date-expr )
| SECOND( expression )
| SECONDS ( datetime-expr )
| SECONDS ( datetime-expr, datetime-expr )
| SECONDS ( datetime-expr, integer-expr )
| TODAY ( * )
| WEEKS ( date-expr )
| WEEKS ( date-expr, date-expr )
| WEEKS ( date-expr, integer-expr )
| YEAR ( date-expr )
| YEARS ( date-expr )
| YEARS ( date-expr, date-expr )
| YEARS ( date-expr, integer-expr )
| YMD ( integer-expr, integer-expr, integer-expr )
The date and time functions allow manipulation of time units. Most time units (such as MONTH) have four functions for time manipulation, although only two names are used (such as MONTH and MONTHS).
SQL Anywhere also supports several Transact-SQL date and time functions, allowing an alternative way of accessing and manipulating date and time functions. For information about the Transact-SQL date and time functions, see "Compatibility of date and time functions".
Arguments to date functions should be converted to dates before being used, so that
days ( '1995-11-17', 2 )
is not correct, but
days ( date( '1995-11-17' ), 2 )
is correct.
DATE( expression ) Converts the expression into a date, and removes any hours, minutes or seconds. Conversion errors may be reported.
DATEFORMAT( date-expr, string-expr ) Returns a string representing the date date-expr in the format specified by string-expr. Any allowable date format can be used for string-expr.
For example,
DATEFORMAT('1989-01-01', 'Mmm Dd, yyyy')
is
'Jan 1, 1989'
| Year 2000 compliance It is possible in using the DATEFORMAT function to produce a string with the year value represented by only two digits. This can cause problems with year 2000 compliance even though no error has occurred.For more information on year 2000 compliance, please see "Date to string conversions". |
For more information, see the DATE_FORMAT option in "SET OPTION statement".
DATENAME ( datepart, date ) Returns the name of the specified part (such as the month "June") of a DATETIME value, as a character string. If the result is numeric, such as 23 for the day, it is still returned as a character string. For example, the following statement displays the value May.
SELECT datename( month , '1987/05/02' )
DATETIME( expression ) Converts the expression into a timestamp. Conversion errors may be reported.
DAY( date-expr ) Returns a number from 1 to 31 corresponding to the day of the given date.
DAYNAME( date-expr ) Returns the name of the day from the supplied date expression. For example, with the date_order option set to the default value of ymd:
SELECT DAYNAME ('1987/05/02')
returns the value Saturday.
DAYS( datetime-expr ) Return the number of days since an arbitrary starting date.
DAYS( date-expr, date-expr ) Returns the number of days from the first date to the second date. The number may be negative. Hours, minutes and seconds are ignored.
DAYS( date-expr, integer-expr ) Add integer-expr days to the given date. If the integer-expr is negative, the appropriate number of days are subtracted from the date. Hours, minutes and seconds are ignored.
DOW( date-expr ) Returns a number from 1 to 7 representing the day of the week of the given date, with Sunday=1, Monday=2, and so on.
HOUR( datetime-expr ) Returns a number from 0 to 23 corresponding to the hour component of the given date.
HOURS( datetime-expr ) Return the number of hours since an arbitrary starting date and time.
HOURS( datetime-expr, datetime-expr ) Returns the number of whole hours from the first date/time to the second date/time. The number may be negative.
HOURS( datetime-expr, integer-expr ) Add integer-expr hours to the given date/time. If the integer-expr is negative, the appropriate number of hours are subtracted from the date/time.
MINUTE( datetime-expr ) Returns a number from 0 to 59 corresponding to the minute component of the given date/time.
MINUTES( datetime-expr ) Return the number of minutes since an arbitrary starting date and time.
MINUTES( datetime-expr, datetime-expr ) Returns the number of whole minutes from the first date/time to the second date/time. The number may be negative.
MINUTES( datetime-expr, integer-expr ) Add integer-expr minutes to the given date/time. If the integer-expr is negative, the appropriate number of minutes are subtracted from the date/time.
MONTH( date-expr ) Returns a number from 1 to 12 corresponding to the month of the given date.
MONTHNAME( date-expr ) Returns the name of the month from the supplied date expression. For example, with the date_order option set to the default value of ymd:
SELECT MONTHNAME ('1987/05/02')
returns the value May.
MONTHS( datetime-expr ) Return the number of months since an arbitrary starting date. This number is often useful for determining if two date/time expressions are on the same month in the same year.
MONTHS( invoice_sent ) = MONTHS( payment_received )
Note that comparing the MONTH function would be wrong if a payment were made 12 months after the invoice was sent.
MONTHS( date-expr, date-expr ) Returns the number of whole months from the first date to the second date. The number may be negative. Hours, minutes and seconds are ignored.
MONTHS( date-expr, integer-expr ) Add integer-expr months to the given date. If the new date is past the end of the month (such as MONTHS('1992-01-31', 1) ) the result is set to the last day of the month. If the integer-expr is negative, the appropriate number of months are subtracted from the date. Hours, minutes and seconds are ignored.
NOW( * ) Returns the current date and time. This is the historical syntax for CURRENT TIMESTAMP.
QUARTER( date-expr ) Returns the quarter from the supplied date expression. For example, with the date_order option set to the default value of ymd:
SELECT QUARTER ('1987/05/02')
returns the value 2.
SECOND( datetime-expr ) Returns a number from 0 to 59 corresponding to the second component of the given date.
SECONDS( datetime-expr ) Return the number of seconds since an arbitrary starting date and time.
SECONDS( datetime-expr, datetime-expr ) Returns the number of whole seconds from the first date/time to the second date/time. The number may be negative.
SECONDS( datetime-expr, integer-expr ) Add integer-expr seconds to the given date/time. If the integer-expr is negative, the appropriate number of seconds are subtracted from the date/time.
TODAY( * ) Returns today's date. This is the historical syntax for CURRENT DATE.
WEEKS( datetime-expr ) Return the number of weeks since an arbitrary starting date. (Weeks are defined as going from Sunday to Saturday, as they do in a North American calendar.) This number is often useful for determining if two dates are in the same week.
WEEKS( invoice_sent ) = WEEKS( payment_received )
WEEKS( date-expr, date-expr ) Returns the number of whole weeks from the first date to the second date. The number may be negative. Hours, minutes and seconds are ignored.
WEEKS( date-expr, integer-expr ) Add integer-expr weeks to the given date. If the integer-expr is negative, the appropriate number of weeks are subtracted from the date. Hours, minutes and seconds are ignored.
YEAR( date-expr ) Returns a 4 digit number corresponding to the year of the given date.
YEARS( date-expr ) Same as the YEAR function.
YEARS( date-expr, date-expr ) Returns the number of whole years from the first date to the second date. The number may be negative. Hours, minutes and seconds are ignored. For example, age can be calculated by
YEARS( birthdate, CURRENT DATE )
YEARS( date-expr, integer-expr ) Add integer-expr years to the given date. If the new date is past the end of the month (such as YEARS( '1992-02-29', 1 ) ) the result is set to the last day of the month. If the integer-expr is negative, the appropriate number of years are subtracted from the date. Hours, minutes, and seconds are ignored.
YMD( year-num, month-num, day-num ) Returns a date value corresponding to the given year, month, and day of the month. If the month is outside the range 1-12, the year is adjusted accordingly. Similarly, the day is allowed to be any integer: the date is adjusted accordingly. For example,
YMD( 1992, 15, 1 ) = 'Mar 1 1993'
YMD( 1992, 15, 1-1 ) = 'Feb 28 1993'
YMD( 1992, 3, 1-1 ) = 'Feb 29 1992'