Time and Date Functions
Date and time functions can be used to generate, process, and manipulate data that consists of date or time data types, such as DATE and TIME.
Inserting Time and Date Function Values
If you wish to insert the value of a function into a table, one method supported by all the time/date functions is to use a SELECT subquery in your INSERT statement, as shown:
INSERT INTO t1 (c1, c2) SELECT CURRENT_DATE(), CURRENT_TIME()
Some functions, such as CURDATE(), CURTIME(), and NOW(), also support direct INSERT, as shown:
INSERT INTO t1 (c1) VALUES (CURDATE())
 
Table 57
Time and Date Functions  
Function
Description
CURDATE ( )
Returns the current local date in this format: ‘yyyy-mm-dd’. Uses the local clock time by default. If SET TIME ZONE has been called, then the value of CURDATE() is determined by calculating UTC time/date from the system clock and operating system locale setting, then adding the displacement value specified with SET TIME ZONE.
CURRENT_DATE ( )
Returns the current UTC date in this format: ‘yyyy-mm-dd’
CURTIME ( )
Returns the current local time in this format: ‘hh:mm:ss’. Uses the local clock time by default. If SET TIME ZONE has been called, then the value of CURTIME() is determined by calculating UTC time/date from the system clock and operating system locale setting, then adding the displacement value specified with SET TIME ZONE.
CURRENT_TIME ( )
Returns the current UTC time in this format: ‘hh:mm:ss’
CURRENT_TIMESTAMP ( )
Returns the current UTC date and time as a timestamp value in this format:
‘yyyy-mm-dd hh:mm:ss.mmm’
DATEADD (datepart, interval, date_exp)
Returns a new DATETIME value based on adding an interval to a date. For example, a datapart day, an interval of 11, and a date_exp of January 26, 2020, returns February 6, 2020.
Datepart specifies the part of the date to which interval is added, and must be one of the following values.
year
quarter
month
day
dayofyear
week
hour
minute
second
millisecond
Interval specifies a positive or negative integer value used to increment datepart. If interval contains a fractional portion, the fraction part is ignored.
Date_exp is an expression that returns a DATETIME value, a value that can be implicitly converted to a DATETIME value, or a character string in a DATE format. See DATETIME.
DATEDIFF (datepart, start, end)
Returns an integer for the difference between the two dates. The integer is the number of date and time boundaries crossed between the two dates.
For example, suppose that table mytest has two columns,col1 and col2, both of which are DATETIME. The value in col1 is 2000-01-01 11:11:11.234 and the value in col2 is 2004-09-11 10:10:10.211. The following SELECT statement returns 56, because that is the difference in months between col1 and col2: SELECT DATEDIFF(month, col1, col2) as Month_Difference FROM mytest.
Datepart specifies the part of the date on which to calculate the difference, and must be one of the following values.
year
quarter
month
day
dayofyear
week
hour
minute
second
millisecond
Start specifies the beginning date for the difference calculation. Start is an expression that returns a DATETIME value or a Unicode character string in a DATE format.
End specifies the ending date for the difference calculation. End is an expression that returns a DATETIME value or a Unicode character string in a DATE format.
Start is subtracted from end. An error is returned if the return value is outside of the range for integer values. See Data Type Ranges.
DATENAME (datepart, date_exp)
Returns an English character string (a VARCHAR) that represents the datepart of date_exp. For example, a datepart month returns the name of the month such as January, February, and so forth. A datepart weekday returns the day of the week such as Monday, Tuesday, and so forth.
Datepart the part of the date to return, and must be one of the following values.
year
quarter
month
day
dayofyear
week
weekday
hour
minute
second
millisecond
Date_exp is an expression that returns a DATETIME value, a value that can be implicitly converted to a DATETIME value, or a character string in a DATE format. See DATETIME.
DATEPART (datepart, date_exp)
Returns an integer that represents the datepart of date_exp. For example, a datepart month returns an integer representing the month (January = 1, December = 12). A datepart weekday returns an integer representing the day of the week (Sunday = 1, Saturday = 7).
Datepart is the part of the date to return, and must be one of the following values.
year
quarter
month
day
dayofyear
week
weekday
hour
minute
second
millisecond
TZoffset
The TZoffset value returns a time zone offset in number of minutes (signed). The DATEPART function with TZoffset works only with SYSDATETIMEOFFSET() and string literals containing a time zone offset. The time zone offset range is from -14:00 through +14:00. See Examples.
Date_exp is an expression that returns a DATETIME value, a value that can be implicitly converted to a DATETIME value, or a character string in a DATE format. See DATETIME.
DAY(date_exp)
Returns the day of the month for the given date_exp. Identical to DATEPART(day, date_exp). See DATEPART (datepart, date_exp).
DAYNAME (date_exp)
Returns an English character string containing the name of the day (for example, Sunday through Saturday) for the day portion of date_exp.
Date_exp can be a DATE, TIMESTAMP literal, or a column containing DATE, TIMESTAMP, or DATETIME data.
DAYOFMONTH (date_exp)
Returns the day of the month in date_exp as an integer in the range of 1 to 31. Date_exp can be a DATE, TIMESTAMP literal, or a column containing DATE, TIMESTAMP, or DATETIME data.
DAYOFYEAR (date_exp)
Returns the day of the year based on the year field in date_exp as an integer value in the range of 1-366.
EXTRACT (extract_field, extract_source)
Returns the extract_field portion of the extract_source. The extract_source argument is a date, time or interval expression.
The permitted values of extract_field are:
YEAR
MONTH
DAY
HOUR
MINUTE
SECOND
These values are returned from the target expression.
HOUR (time_exp)
Returns the hour as an integer in the rage of 0 to 23. Time_exp can be a DATE, TIMESTAMP literal, or a column containing DATE, TIMESTAMP, or DATETIME data.
MINUTE (time_exp)
Returns the minute as an integer in the range 0 to 59. Time_exp can be a DATE, TIMESTAMP literal, or a column containing DATE, TIMESTAMP, or DATETIME data.
MONTH (date_exp)
Returns the month as an integer in the range of 1 to 12. Date_exp can be a DATE, TIMESTAMP literal, or a column containing DATE, TIMESTAMP, or DATETIME data.
MONTHNAME (date_exp)
Returns an English character string containing the name of the month (for example, January through December) for the month portion of date_exp. Date_exp can be a DATE, TIMESTAMP literal, or a column containing DATE, TIMESTAMP, or DATETIME data.
NOW ( )
Returns the current local date and time as a timestamp value in this format:
‘yyyy-mm-dd hh:mm:ss.mmm’
Uses the local clock time by default. If SET TIME ZONE has been called, then the value of NOW() is determined by calculating UTC time/date from the system clock and operating system locale setting, then adding the displacement value specified with SET TIME ZONE.
QUARTER (date_exp)
Returns the quarter in date_exp as an integer value in the range of 1- 4, where 1 represents January 1 through March 31. Date_exp can be a DATE, TIMESTAMP literal, or a column containing DATE, TIMESTAMP, or DATETIME data.
SECOND (time_exp)
Returns the second as an integer in the range of 0 to 59. Time_exp can be a DATE, TIMESTAMP literal, or a column containing DATE, TIMESTAMP, or DATETIME data.
SYSDATETIMEOFFSET
Returns the current date and time along with the hour and minute offset between the current time zone and UTC of the computer on which the Pervasive PSQL database engine is running. Daylight saving time (DST) is taken into consideration.
The default format returned YYYY-MM-DD hh:mm:ss[.nnnnnnn] [<+ | ->hh:mm]. A "+" indicates that the current time zone is ahead of the UTC; a "-" indicates that the current time zone is behind the UTC.
TIMESTAMPADD (interval, integer_exp, timestamp_exp)
Returns the timestamp calculated by adding integer_exp intervals of type interval to timestamp_exp.
The allowed values for interval are:
SQL_TSI_YEAR
SQL_TSI_QUARTER
SQL_TSI_MONTH
SQL_TSI_WEEK
SQL_TSI_DAY
SQL_TSI_HOUR
SQL_TSI_MINUTE
SQL_TSI_SECOND
TIMESTAMPDIFF (interval, timestamp_exp1, timestamp_exp2)
Returns the integer number of intervals of type interval by which timestamp_exp2 is greater than timestamp_exp1.
The values allowed for interval are the same as for TIMESTAMPADD
WEEK (date_exp)
Returns the week of the year based on the week field in date_exp as an integer in the range of 1 to 53. Date_exp can be a DATE, TIMESTAMP literal, or a column containing DATE, TIMESTAMP, or DATETIME data.
WEEKDAY(date_exp)
Returns the day of the week for the given date_exp, where 1=Sunday and 7=Saturday. Identical to DATEPART(weekday, date_exp). See DATEPART (datepart, date_exp).
YEAR (date_exp)
Returns the year as an integer value. The range depends on the data source. Date_exp can be a DATE, TIMESTAMP literal, or a column containing DATE, TIMESTAMP, or DATETIME data.
Examples
The following example shows how to insert values from time or date functions. Some functions, such as CURDATE(), you can use directly in an INSERT statement. Other functions, however, are not supported in this manner. The method with the widest support is to use INSERT with SELECT. In the example below, the UTC time value returned by CURRENT_TIME() is inserted into table T1:
INSERT INTO T1 (C1) SELECT CURRENT_TIME()
============ 
The following example illustrates the use of hour.
SELECT c.Name,c.Credit_Hours FROM Course c WHERE c.Name = ANY (SELECT cl.Name FROM Class cl WHERE c.Name = cl.Name AND c.Credit_Hours >(HOUR (Finish_Time - Start_Time) + 1))
============ 
The following is an example of minute.
SELECT minute(log) FROM billing
============ 
The following example illustrates the use of second.
SELECT SECOND(log) FROM billing
SELECT log FROM billing where SECOND(log) = 31
============ 
The following example illustrates the use of NOW().
SELECT now() - log FROM billing
============ 
The following is a complex example that uses month, day, year, hour and minute.
SELECT Name, Section, MONTH(Start_Date), DAY(Start_Date), YEAR(Start_Date), HOUR(Start_Time), MINUTE(Start_Time) FROM Class
============ 
The following example illustrates use of CURDATE().
SELECT ID, Name, Section FROM Class WHERE (Start_Date - CURDATE()) <= 2 AND (Start_Date - CURDATE()) >= 0
============ 
The next example gives the day of the month and day of the week of the start date of class from the class table.
SELECT DAYOFMONTH(Start_date), DAYOFWEEK(Start_date) from Class
SELECT * FROM person WHERE YEAR(Date_Of_Birth) < 1970
============ 
The following example illustrates use of DATEPART with the TZoffset parameter.
SELECT DATEPART(TZoffset, SYSDATETIMEOFFSET())
Assuming the statement returns -360, the current time zone is 360 minutes behind UTC.
Assume that SELECT SYSDATETIMEOFFSET() returns 2011-01-24 14:33:08.4650000 -06:00. Given this, the following query returns -360:
SELECT DATEPART(TZoffset, '2011-01-24 14:33:08.4650000 -06:00')
If the time zone portion is omitted from the string literal, 0 is returned:
SELECT DATEPART(TZoffset, '2011-01-24 14:33:08.4650000')