SQL - Lesson 13. Date and time functions

These functions are designed to work with calendar data types. Let's look at the most applicable ones.

• CURDATE(), CURTIME() and NOW() The first function returns the current date, the second returns the current time, and the third returns the current date and time. Compare:

  SELECT CURDATE(), CURTIME(), NOW();

  
  The CURDATE() and NOW() functions are useful for adding records to the database that use the current time. In our store, all deliveries and sales use the current time. Therefore, to add records about deliveries and sales, it is convenient to use the CURDATE() function. For example, let’s say a product arrived at our store, let’s add information about it to the Delivery (incoming) table:

  INSERT INTO incoming (id_vendor, date_incoming) VALUES ("2", curdate());

  
  If we were storing the delivery date as a datatime type, then the NOW() function would be more suitable for us.


• ADDDATE(date, INTERVAL value) The function returns date with value added to it. The value value can be negative, then the final date will decrease. Let's see when our suppliers delivered goods:

  SELECT id_vendor, date_incoming FROM incoming;

  
  Let's assume we made a mistake when entering the date for the first supplier, let's reduce its date by one day:
  The value can be not only days, but also weeks (WEEK), months (MONTH), quarters (QUARTER) and years (YEAR). For example, let's reduce the delivery date for the second supplier by 1 week:
  In our Deliveries (incoming) table, we used the date type for the Delivery Date (date_incoming) column. As you remember from Lesson 2, this data type is designed to store only dates. But if we used the datatime type, then we would display not only the date, but also the time. Then we could use the ADDDATE function for time too. The value in this case can be seconds (SECOND), minutes (MINUTE), hours (HOUR) and their combinations:
  minutes and seconds (MINUTE_SECOND),
  hours, minutes and seconds (HOUR_SECOND),
  hours and minutes (HOUR_MINUTE),
  days, hours, minutes and seconds (DAY_SECOND),
  days, hours and minutes (DAY_MINUTE),
  days and hours (DAY_HOUR),
  years and months (YEAR_MONTH).
  For example, let's add two hours and 45 minutes to the date April 15, 2011:

  SELECT ADDDATE("2011-04-15 00:02:00", INTERVAL "02:45" HOUR_MINUTE);

  
  


• SUBDATE(date, INTERVAL value) function is identical to the previous one, but performs a subtraction operation rather than an addition.

  SELECT SUBDATE("2011-04-15 00:02:00", INTERVAL "23:53" HOUR_MINUTE);

  
  


• PERIOD_ADD(period, n) function adds n months to the date value period. Note: the date value must be in YYYYMM format. Let's add 2 months to February 2011 (201102):

  SELECT PERIOD_ADD(201102, 2);

  
  


• TIMESTAMPADD(interval, n, date) the function adds a time interval n to the date date, the values ​​of which are specified by the interval parameter. Possible values ​​for the interval parameter:

  FRAC_SECOND - microseconds
  SECOND - seconds
  MINUTE - minutes
  HOUR - hours
  DAY - days
  WEEK - weeks
  MONTH - months
  QUARTER - blocks
  YEAR - years
  

  SELECT TIMESTAMPADD(DAY, 2, "2011-04-02");

  
  


• TIMEDIFF(date1, date2) calculates the difference in hours, minutes and seconds between two dates.

  SELECT TIMEDIFF("2011-04-17 23:50:00", "2011_04-16 14:50:00");

  
  


• DATEDIFF(date1, date2) calculates the difference in days between two dates. For example, we want to find out how long ago the supplier "Williams" (id=1) supplied us with goods:
  


• PERIOD_DIFF(period1, period2) function calculates the difference in months between two dates represented in YYYYMM format. Let's find out the difference between January 2010 and August 2011:

  SELECT PERIOD_DIFF(201108, 201001);

  
  


• TIMESTAMPDIFF(interval, date1, date2) the function calculates the difference between dates date2 and date1 in the units specified in the interval parameter. Possible values ​​for the interval parameter:

  FRAC_SECOND - microseconds
  SECOND - seconds
  MINUTE - minutes
  HOUR - hours
  DAY - days
  WEEK - weeks
  MONTH - months
  QUARTER - blocks
  YEAR - years
  

  SELECT TIMESTAMPDIFF(DAY, "2011-04-02", "2011-04-17") AS days, TIMESTAMPDIFF(HOUR, "2011-04-16 20:14:00", "2011-04-17 23:58 :20") AS hours;

  
  


• SUBTIME(date, time) function subtracts time from date time:

  SELECT SUBTIME("2011-04-18 23:17:00", "02:15:30");

  
  


• DATE(datetime) returns the date, trimming the time. For example:

  SELECT DATE("2011-04-15 00:03:20");

  
  


• TIME(datetime) returns the time, truncating the date. For example:

  SELECT TIME("2011-04-15 00:03:20");

  
  


• TIMESTAMP(date) function takes a date and returns the full version with time. For example:

  SELECT TIMESTAMP("2011-04-17");

  
  


• DAY(date) and DAYOFMONTH(date) are synonymous functions that return the serial number of the day of the month from the date:

  SELECT DAY("2011-04-17"), DAYOFMONTH("2011-04-17");

  
  


• DAYNAME(date), DAYOFWEEK(date) and WEEKDAY(date) functions return the day of the week, the first - its name, the second - the number of the day of the week (counting from 1 - Sunday to 7 - Saturday), the third - the number of the day of the week (counting from 0 - Monday, to 6 - Sunday:

  SELECT DAYNAME("2011-04-17"), DAYOFWEEK("2011-04-17"), WEEKDAY("2011-04-17");

  
  


• WEEK(date), WEEKOFYEAR(datetime) both functions return the number of the week in the year, the first for the date type, and the second for the datetime type, the first has a week starting from Sunday, the second - from Monday:

  SELECT WEEK("2011-04-17"), WEEKOFYEAR("2011-04-17 23:40:00");

  
  


• MONTH(date) and MONTHNAME(date) both functions return month values. The first is its numerical value (from 1 to 12), the second is the name of the month:

  SELECT MONTH("2011-04-17"), MONTHNAME("2011-04-17");

  
  


• QUARTER(date) function returns the value of the quarter of the year (from 1 to 4):

  SELECT QUARTER("2011-04-17");

  
  


• YEAR(date) function returns the year value (from 1000 to 9999):

  SELECT YEAR("2011-04-17");

  
  


• DAYOFYEAR(date) returns the ordinal number of the day in the year (from 1 to 366):

  SELECT DAYOFYEAR("2011-04-17");

  
  


• HOUR(datetime) returns the hour value for time (0 to 23):

  SELECT HOUR("2011-04-17 18:20:03");

  
  


• MINUTE(datetime) returns the minutes value for time (from 0 to 59): SELECT UNIX_TIMESTAMP("2011-04-17"), FROM_UNIXTIME(1302524000);
  


• TIME_TO_SEC(time) and SEC_TO_TIME(n) reciprocal functions. The first converts time into the number of seconds that have passed since the beginning of the day. The second, on the contrary, takes the number of seconds from the beginning of the day and converts them into time:

  SELECT TIME_TO_SEC("22:10:30"), SEC_TO_TIME(45368);

  
  


• MAKEDATE(year, n) function takes the year and day number in the year and converts them to a date:

  SELECT MAKEDATE(2011, 120);

  
  

Well, that's all for today. Next time we'll look at functions that help convert dates from one format to another.

The SQL-92 standard specifies only functions that return the system date/time. For example, the CURRENT_TIMESTAMP function returns both date and time. Plus there are functions that return one thing.
Naturally, due to such limitations, language implementations expand the standard by adding functions that make it easier for users to work with data of this type. Here we will look at the date/time processing functions in T-SQL.

DATEADD function

Syntax

DATEADD( datepart , number, date)

This function returns a value of type datetime, which is obtained by adding to the date date number of intervals of type datepart, equal number. For example, we can add any number of years, days, hours, minutes, etc. to a given date. Valid Argument Values datepart are given below and taken from BOL.

Let today be 01/23/2004, and we want to know what day it will be in a week. We can write because the fractional part of the argument value datepart is discarded and we get 0 instead of one fourth and, as a consequence, the current day.
Alternatively, we can use instead CURRENT_TIMESTAMP T-SQL function GETDATE() with the same effect. The presence of two identical functions is supported, apparently, in anticipation of the subsequent development of the standard.
Example (Scheme 4). Determine what day it will be a week after the last flight.

SELECT DATEADD(day, 7, (SELECT MAX(date) max_date FROM pass_in_trip))

Using a subquery as an argument is acceptable because this subquery returns a SINGLE value of type datetime.

DATEDIFF function

Syntax

DATEDIFF( datepart , startdate , enddate)

The function returns the interval of time elapsed between two timestamps - startdate(starting mark) and enddate(end mark). This interval can be measured in different units. Possible options are determined by the argument datepart and are listed above in relation to the function DATEADD.
Example (Scheme 4). Determine the number of days that have passed between the first and last flights made.

(which gives -760) will be incorrect for two reasons.
Firstly, for flights that depart on one day and arrive the next, the value calculated in this way will be incorrect. Secondly, it is unreliable to make any assumptions about a day that is present only due to the need to match the type datetime.
But how can you tell if the plane landed the next day? Here the description of the subject area helps, which states that the flight cannot last more than a day. So, if the arrival time is no longer than the departure time, then this fact holds. Now the second question: how to calculate only the time, no matter what day it is on?
This is where the T-SQL function can help DATEPART.

DATEPART function

Syntax

DATEPART( datepart , date)

This function returns an integer representing the specified argument datepart part of the date specified by the second argument ( date).
List of valid argument values datepart, described above in this section, is supplemented with one more meaning

Note that the function returned DATEPART the value in this case (number of the day of the week) depends on the settings, which can be changed using the operator SET DATEFIRST, setting the first day of the week. For some, Monday is a hard day, and for others, it’s Sunday. By the way, the last value is the default.
However, let's return to our example. Assuming that the departure/arrival time is a multiple of a minute, we can define it as the sum of hours and minutes. Since date/time functions work with integer values, we will reduce the result to the smallest interval - minutes. So, the departure time of flight 1123 is in minutes

Now we have to compare whether the arrival time exceeds the departure time. If so, subtract the second from the first to get the flight duration. Otherwise, you need to add one day to the difference (24*60 = 1440 minutes).

SELECT CASE WHEN time_dep>=time_arr THEN time_arr-time_dep+1440 ELSE time_arr-time_dep END dur FROM (SELECT DATEPART(hh, time_out)*60 + DATEPART(mi, time_out) time_dep, DATEPART(hh, time_in)*60 + DATEPART(mi, time_in) time_arr FROM trip WHERE trip_no=1123 )tm

Here, in order not to repeat long constructions in the CASE statement, a subquery is used. Of course, the result turned out to be quite cumbersome, but absolutely correct in the light of the comments made to this problem.
Example (4 diagram). Determine the date and time of departure of flight 1123.
The Pass_in_trip table of completed flights contains only the flight date, but not the time, because According to the subject area, each flight can only be performed once per day. To solve this problem, you need to add time from the Trip table to the date stored in the Pass_in_trip table
DISTINCT is necessary here to eliminate possible duplicates, since the flight number and date are duplicated in this table for each passenger on a given flight.

DATENAME function

Syntax

DATENAME( datepart , date)

This function returns a symbolic representation of the component ( datepart) specified date ( date). The date component argument can take one of the values ​​listed in the table above.
This gives us a simple way to concatenate date components, resulting in any desired representation format. For example, the design

It should be noted that this function reveals the difference in values day And dayofyear argument datepart. The first gives a symbolic representation of the day of a specified date, while the second gives a symbolic representation of that day from the beginning of the year. Those.

SELECT DATENAME (day, "2003-12-31")

will give us 31, and

SELECT DATENAME (dayofyear, "2003-12-31")

- 365.
In some cases the function DATEPART can be replaced with simpler functions. Here they are:
DAY (date) is an integer representation of the day of the specified date. This function is equivalent to the function DATEPART(dd, date).
MONTH (date) is an integer representation of the month of the specified date. This function is equivalent to the function DATEPART(mm, date).
YEAR (date) is an integer representation of the year of the specified date. This function is equivalent to the function DATEPART(yy, date).

@@DATEFIRST function

@@DATEFIRST returns a number that specifies the first day of the week set for the current session. In this case, 1 corresponds to Monday, and 7, respectively, to Sunday. Those. If

SELECT @@DATEFIRST;

returns 7, then the first day of the week is Sunday (corresponds to the current settings on the site).

Transact-SQL language functions can be aggregate or scalar. These types of functions are discussed in this article.

Aggregate functions

Aggregate functions perform calculations on a group of column values ​​and always return a single value as the result of those calculations. Transact-SQL supports several common aggregate functions:

AVG

Calculates the arithmetic average of the data contained in a column. The values ​​on which the calculation is performed must be numeric.

MIN and MAX

Determines the maximum and minimum value of all data values ​​contained in a column. Values ​​can be numeric, string, or temporal (date/time).

SUM

Calculates the total sum of the values ​​in a column. The values ​​on which the calculation is performed must be numeric.

COUNT

Counts the number of non-null values ​​in a column. The count(*) function is the only aggregate function that does not perform calculations on columns. This function returns the number of rows (regardless of whether individual columns contain null values).

COUNT_BIG

Similar to the count function, with the difference that it returns a BIGINT data value.

The use of regular aggregate functions in a SELECT statement will be discussed in a future article.

Scalar functions

Transact-SQL scalar functions are used in creating scalar expressions. (A scalar function performs calculations on a single value or a list of values, while an aggregate function performs calculations on a group of values ​​over multiple rows.) Scalar functions can be broken down into the following categories:

numeric functions;

date functions;

string functions;

system functions;

metadata functions.

These types of functions are discussed in subsequent sections.

Numeric functions

Transact-SQL numeric functions are mathematical functions for modifying numeric values. A list of numerical functions and their brief descriptions are given in the table below:

Transact-SQL Numeric Functions

Function	Syntax	Description	Usage example
ABS	ABS(n)	Returns the absolute value (that is, negative values ​​are returned as positive) of a numeric expression n.	SELECT ABS(-5.320) -- Returns 5.320 SELECT ABS(8.90) -- Returns 8.90
ACOS, ASIN, ATAN, ATN2	ACOS(n), ASIN(n), ATAN(n), ATN2(n, m)	Inverse trigonometric functions that calculate the arccosine, arcsine, arctangent of the value n (for ATN2 the arctangent n/m is calculated). The input values ​​n, m and the result are of the FLOAT data type.
COS, SIN, TAN, COT	COS(n), SIN(n), TAN(n), COT(n)	Trigonometric functions that calculate cosine, sine, tangent, cotangent of the value n. The result has data type FLOAT.
DEGREES, RADIANS	DEGREES(n), RADIANS(n)	The DEGREES function converts radians to degrees, RADIANS, respectively, vice versa.	SELECT DEGREES(PI() / 4) -- Returns 45 SELECT COS(RADIANS(60.0)) -- Returns 0.5
CEILING	CEILING(n)	Rounds a number to a higher integer value.	SELECT CEILING(-5.320) -- Returns -5 SELECT CEILING(8.90) -- Returns 9
ROUND	ROUND(n, p, [t])	Rounds the value of n to the nearest p. When p is a positive number, the fractional part of n is rounded, and when it is negative, the integer part is rounded. When using the optional argument t, the number n is not rounded but rather truncated (that is, rounded down).	SELECT ROUND(5.3208, 3) -- Returns 5.3210 SELECT ROUND(125.384, -1) -- Returns 130.000 SELECT ROUND(125.384, -1, 1) -- Returns 120.000
FLOOR	FLOOR(n)	Rounds down to the lowest integer value.	SELECT FLOOR(5.88) -- Returns 5
EXP	EXP(n)	Calculates the value of e n .
LOG, LOG10	LOG(n), LOG10(n)	LOG(n) - calculates the natural logarithm (i.e., base e) of the number n, LOG10(n) - calculates the decimal (base 10) logarithm of the number n.
P.I.	PI()	Returns the value of π (3.1415).
POWER	POWER(x, y)	Calculates the value of x y .
RAND	RAND()	Returns an arbitrary number of type FLOAT in the range of values ​​between 0 and 1.
ROWCOUNT_BIG	ROWCOUNT_BIG()	Returns the number of table rows that were processed by the last Transact-SQL statement executed by the system. The return value is of type BIGINT.
SIGN	SIGN(n)	Returns the sign of n as a number: +1 if positive, -1 if negative.
SQRT, SQUARE	SQRT(n), SQUARE(n)	SQRT(n) - calculates the square root of the number n, SQUARE(n) - returns the square of the argument n.

Date functions

Date functions evaluate the corresponding date or time parts of an expression or return a time interval value. The date functions supported in Transact-SQL and their brief descriptions are given in the table below:

Transact-SQL Date Functions

Function	Syntax	Description	Usage example
GETDATE	GETDATE()	Returns the current system date and time.	SELECT GETDATE()
DATEPART	DATEPART (item, date)	Returns the date part specified in the item parameter as an integer.	Returns 1 (January) SELECT DATEPART(month, "01/01/2012") -- Returns 4 (Wednesday) SELECT DATEPART(weekday, "01/02/2012")
DATENAME	DATENAME (item, date)	Returns the date part specified in the item parameter as a character string.	Returns January SELECT DATENAME(month, "01/01/2012") -- Returns Wednesday SELECT DATENAME(weekday, "01/02/2012")
DATEDIFF	DATEDIFF (item, dat1, dat2)	Calculates the difference between two date parts dat1 and dat2 and returns an integer result in the units specified in the item argument.	Returns 19 (19 year interval between dates) SELECT DATEDIFF(year, "01/01/1990", "01/01/2010") -- Returns 7305 (7305 day interval between dates) SELECT DATEDIFF(day, "01/01/1990", "01/01" .2010")
DATEADD	DATEADD (item, n, date)	Adds the nth number of units specified in the item argument to the specified date. (n can also be negative.)	Will add 3 days to the current date SELECT DATEADD(day, 3, GETDATE())

String functions

String functions manipulate column values, which are typically character data types. The supported string functions in Transact-SQL and their brief descriptions are given in the table below:

Transact-SQL String Functions

Function	Syntax	Description	Usage example
ASCII, UNICODE	ASCII(char), UNICODE(char)	Converts the specified character to the corresponding ASCII code integer.	SELECT ASCII("W") -- 87 SELECT UNICODE("u") -- 1102
CHAR, NCHAR	CHAR(int), NCHAR(int)	Converts an ASCII code (or Unicode if NCHAR) to the appropriate character.	SELECT CHAR(87) -- "W" SELECT NCHAR(1102) -- "yu"
CHARINDEX	CHARINDEX (str1, str2)	Returns the starting position of the occurrence of the substring str1 in the string str2. If the string str2 does not contain the substring str1, the value 0 is returned	Returns 5 SELECT CHARINDEX ("morph", "polymorphism")
DIFFERENCE	DIFFERENCE (str1, str2)	Returns an integer between 0 and 4 that is the difference between the SOUNDEX values ​​of the two strings str1 and str2. The SOUNDEX method returns a number that characterizes the sound of the string. Using this method, you can identify similar sounding strings. Only works for ASCII characters.	Returns 2 SELECT DIFFERENCE ("spelling", "telling")
LEFT, RIGHT	LEFT (str, length), RIGHT (str, length)	Returns the number of first characters of the string str specified by the length parameter for LEFT and the last length characters of the string str for the RIGHT function.	DECLARE @str nvarchar(30) = "Synchronization"; -- Returns "Sync" SELECT LEFT(@str, 4) -- Returns "Zation" SELECT RIGHT(@str, 5)
LEN	LEN(str)	Returns the number of characters (not the number of bytes) of the string str specified in the argument, including trailing spaces.
LOWER, UPPER	LOWER(str), UPPER(str)	The LOWER function converts all uppercase letters in str1 to lowercase. Lowercase letters and other characters included in the string are not affected. The UPPER function converts all lowercase letters in the string str to uppercase.	DECLARE @str nvarchar(30) = "Synchronization"; -- Returns "SYNCHRONIZATION" SELECT UPPER(@str) -- Returns "synch" SELECT LOWER(@str)
LTRIM, RTRIM	LTRIM(str), RTRIM(str)	The LTRIM function removes leading spaces in the string str, RTRIM respectively removes spaces at the end of the string.
QUOTENAME	QUOTENAME(char_string)	Returns a Unicode-encoded string with delimiters added to convert the input string to a valid delimited identifier.	DECLARE @str nvarchar(30) = "Synchronization"; -- Return "[Sync]" SELECT QUOTENAME(@str)
PATINDEX	PATINDEX (%p%, expr)	Returns the starting position of the first occurrence of the pattern p in the given expr, or zero if the pattern is not found.	Returns 4 SELECT PATINDEX("%chro%", "Synchronization")
REPLACE	REPLACE (str1, str2, str3)	Replaces all occurrences of the substring str2 in the string str1 with the substring str3.	Returns "Desynchronization" SELECT REPLACE("Synchronization", "Synchronization", "Desynchronization")
REPLICATE	REPLICATE (str, i)	Repeats the string str i times.	Returns "aBaBaBaBaB" SELECT REPLICATE("aB", 5)
REVERSE	REVERSE (str)	Prints the string str in reverse order.	Returns "yaitsazinorkhniS" SELECT REVERSE("Synchronization")
SOUNDEX	SOUNDEX (str)	Returns a four-character soundex code used to determine whether two strings are similar. Only works for ASCII characters.
SPACE	SPACE (length)	Returns a string of spaces with the length specified in the length parameter. Similar to REPLICATE(" ", length).
STR	STR (f[, len[, d]])	Converts the specified floating-point expression f to a string, where len is the length of the string, including decimal point, sign, digits, and spaces (defaults to 10), and d is the number of decimal places to return.	Returns "3.14" SELECT STR (3.1415, 4, 2)
STUFF	STUFF (str1, a, length, str2)	Removes length characters from string str1, starting at position a, and inserts string str2 in their place.	Note in a book SELECT STUFF("Notebook", 5, 0," in a ") -- Handbook SELECT STUFF("Notebook", 1, 4, "Hand")
SUBSTRING	SUBSTRING (str1, a, length)	Extracts from the string str, starting at position a, a substring of length length.

System functions

Transact-SQL system functions provide extensive information about database objects. Most system functions use an internal numeric identifier (ID) that is assigned to each database object when it is created. Using this identifier, the system can uniquely identify each database object.

The following table lists some of the most important system functions along with their brief descriptions:

Transact-SQL System Functions

Function	Syntax	Description	Usage example
CAST	CAST (w AS type [(length)]	Converts expression w to the specified data type type (if possible). The argument w can be any valid expression.	Returns 3 SELECT CAST (3.1258 AS INT)
COALESCE	COALESCE (a1, a2)	Returns the first expression value from the list of expressions a1, a2, ... that is not null.
COL_LENGTH	COL_LENGTH (obj, col)	Returns the length of the col column of the database object (table or view) obj.	Returns 4 SELECT COL_LENGTH("Employee", "Id")
CONVERT	CONVERT (type[(length)], w)	Equivalent to the CAST function, but the arguments are specified differently. Can be used with any data type.
CURRENT_TIMESTAMP	CURRENT_TIMESTAMP	Returns the current date and time.
CURRENT_USER	CURRENT_USER	Returns the name of the current user.
DATALENGTH	DATALENGTH(z)	Returns the number of bytes occupied by the expression z.	This query returns the length of each field SELECT DATALENGTH(FirstName) FROM Employee
GETANSINUL	GETANSINULL("dbname")	Returns 1 if the use of null values ​​in database dbname complies with the ANSI SQL standard.
ISNULL	ISNULL (expr, value)	Returns the value of expr if it is not NULL; otherwise, value is returned.
ISNUMERIC	ISNUMERIC (expr)	Determines whether the expression expr is a valid numeric type.
NEWID	NEWID()	Creates a unique identification number ID consisting of a 16-byte binary string designed to store values ​​of the UNIQUEIDENTIFIER data type.
NEWSEQUENTIALID	NEWSEQUENTIALID()	Creates a GUID that is larger than any other GUID previously created by this function on the specified computer. (This function can only be used as a default value for a column.)
NULLIF	NULLIF (expr1, expr2)	Returns null if the values ​​of expr1 and expr2 are the same.	The query returns NULL for a project -- whose Number = "p1" SELECT NULLIF(Number, "p1") FROM Project
SERVERPROPERTY	SERVERPROPERTY (propertyname)	Returns information about database server properties.
SYSTEM_USER	SYSTEM_USER	Returns the ID of the current user.
USER_ID	USER_ID()	Returns the user ID username. If no user is specified, the current user ID is returned.
USER_NAME	USER_NAME()	Returns the username with the specified id. If an identifier is not specified, the current user's name is returned.

Metadata functions

Basically, metadata functions return information about a specified database and database objects. The table below summarizes some of the most important metadata functions along with their brief descriptions:

Transact-SQL Metadata Functions

Function	Syntax	Description	Usage example
COL_NAME	COL_NAME (tab_id, col_id)	Returns the name of the column with the specified identifier col_id of the table with identifier tab_id.	Returns the name of the column "LastName" SELECT COL_NAME (OBJECT_ID("Employee"), 3)
COLUMNPROPERTY	COLUMNPROPERTY (id, col, property)	Returns information about the specified column.	Returns the value of the PRECISION property -- for the Id column of the Employee table SELECT COLUMNPROPERTY (OBJECT_ID("Employee"), "Id", "precision")
DATABASEPROPERTY	DATABASEPROPERTY (database, property)	Returns the value of the database property property.	Returns the value of the IsNullConcat property -- for the SampleDb database SELECT DATABASEPROPERTY ("SampleDb", "IsNullConcat")
DB_ID	DB_ID()	Returns the database ID db_name. If a database name is not specified, the ID of the current database is returned.
DB_NAME	DB_NAME()	Returns the name of the database that has db_id. If an identifier is not specified, the name of the current database is returned.
INDEX_COL	INDEX_COL (table, i, no)	Returns the name of the indexed column of table table. A column is indicated by index identifier i and column position no in that index.
INDEXPROPERTY	INDEXPROPERTY (obj_id, index_name, property)	Returns the properties of a named index or statistic for the specified table ID number, the name of the index or statistic, and the name of the property.
OBJECT_NAME	OBJECT_NAME (obj_id)	Returns the name of the database object that has the identifier obj_id.	SELECT OBJECT_NAME(245575913);
OBJECT_ID	OBJECT_ID (obj_name)	Returns the object identifier obj_name of the database.	Returns 245575913 - Employee table ID SELECT OBJECT_ID("Employee")
OBJECTPROPERTY	OBJECTPROPERTY (obj_id, property)	Returns information about objects from the current database.

From the author: Today we will talk about how date functions work in SQL. The following table provides a list of all the important date and time functions that are available. There are others supported by various DBMSs. This list represents the functions available in the MySQL DBMS.

ADDDATE(). Adds dates

ADDTIME(). Adds time

CONVERT_TZ(). Converts from one time zone to another

CURDATE(). Returns the current date

CURRENT_DATE(), CURRENT_DATE. Synonyms for CURDATE()

CURRENT_TIME(), CURRENT_TIME. Synonyms for CURTIME()

CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP. Synonyms for NOW()

CURTIME(). Returns the current time

DATE_ADD(). Adds two dates

DATE_FORMAT(). Sets the specified date format

DATE_SUB(). Subtracts one date from another

DATE(). Extracts the date part from an expression representing a date or time and date

DATEDIFF(). Subtracts one date from another

DAY(). Synonym for DAYOFMONTH()

DAYNAME(). Returns the day of the week

DAYOFMONT(). Returns the day of the month (1-31)

DAYOFWEEK(). Returns the index of the day of week of the argument

DAYOFYEAR(). Returns the number of the day in the year (1-366)

EXTRACT. Retrieves the date part

FROM_DAYS(). Converts a day number to a date

FROM_UNIXTIME(). Formats a date as a UNIX timestamp

HOUR(). Retrieves the hour

LAST_DAY. Returns the last day of the month for the argument

LOCALTIME(), LOCALTIME. Synonym for NOW()

LOCALTIMESTAMP, LOCALTIMESTAMP(). Synonym for NOW()

MAKEDATE(). Creates a date from the year and day of the year

MAKETIME. MAKETIME(). MICROSECOND(). Returns microseconds from argument

MINUTE(). Returns minutes from argument

MONTH(). Returning the month from a date

MONTHNAME(). Returns the name of the month

NOW(). Returns the current date and time

PERIOD_ADD(). Adds a period to the year-month

PERIOD_DIFF(). Returns the number of months between periods

QUARTER(). Returns the quarter from the argument

SEC_TO_TIME(). Converts seconds to "HH:MM:SS" format

SECOND(). Returns seconds (0-59)

STR_TO_DATE(). Converts a string to a date

SUBDATE(). When called with three arguments, the DATE_SUB() synonym

SUBTIME(). Subtracts time

SYSDATE(). Returns the execution time of a function

TIME_FORMAT(). Sets the time format

TIME_TO_SEC(). Returns the argument converted to seconds

TIME(). Extracts the time part of the passed expression

TimeDiff(). Subtracts time

TIMESTAMP(). With one argument, this function returns a date or datetime expression. With two arguments - adds these two arguments

TIMESTAMPADD(). Adds an interval to a date and time expression

TIMESTAMPDIFF(). Subtracts an interval from a datetime expression

TO_DAYS(). Returns the date argument converted to days

UNIX_TIMESTAMP(). Returns a UNIX timestamp

UTC_DATE(). Returns the current UTC date

UTC_TIME(). Returns the current UTC time

UTC_TIMESTAMP(). Returns the current UTC date and time

WEEK(). Returns the week number

WEEKDAY(). Returns the index of the day of the week

WEEKOFYEAR(). Returns the calendar week number (1-53)

YEAR(). Returns the year

YEARWEEK(). Returns the year and week

ADDDATE(date,INTERVAL expr unit), ADDDATE(expr,days)

When called with a second argument specified as INTERVAL, ADDDATE() is a synonym for DATE_ADD(). The related function SUBDATE() is a synonym for DATE_SUB(). For information about the INTERVAL block argument, see DATE_ADD().

When called with the second argument given in days, MySQL treats this as an integer number of days to add to the expression.

ADDTIME(expr1,expr2)

ADDTIME() adds expr2 to expr1 and returns the result. Expr1 is a time or datetime expression, while expr2 is a time expression.

CONVERT_TZ(dt,from_tz,to_tz)

Converts the date and time value dt from the time zone specified in from_tz to the time zone specified in to_tz and returns the resulting value. This function returns NULL if the arguments are invalid.

CURDATE()

Returns the current date as a value in the format "YYYY-MM-DD" or YYYYMMDD, depending on whether this function is used in a string or numeric context.

CURRENT_DATE and CURRENT_DATE()

CURRENT_DATE and CURRENT_DATE() are synonyms for CURDATE()

CURTIME()

Returns the current time as a value in the format "HH:MM:SS" or HHMMSS, depending on whether the function is used in a string or numeric context. The value is expressed for the current time zone.

CURRENT_TIME and CURRENT_TIME()

CURRENT_TIME and CURRENT_TIME() are synonyms for CURTIME().

CURRENT_TIMESTAMP and CURRENT_TIMESTAMP()

CURRENT_TIMESTAMP and CURRENT_TIMESTAMP() are synonyms for NOW().

DATE(expr)

Retrieves the date part of a date or datetime expr expression.

DATEDIFF(expr1,expr2)

DATEDIFF() returns expr1.expr2 expressed as the number of days between two dates. Both expr1 and expr2 are date or datetime expressions. Only the date parts are used in the calculations.

DATE_ADD(date,INTERVAL expr unit), DATE_SUB(date,INTERVAL expr unit)

These functions perform arithmetic operations on dates. date is represented as a DATETIME or DATE value indicating the start date. expr is an expression that specifies the interval value to add or subtract from the original date. expr is a string; it may start with "-" for negative intervals.

unit is a keyword that specifies the units of measure for the expression. The INTERVAL keyword and units notation are not case sensitive. The following table shows the expected form of the expr argument for each unit value.

The QUARTER and WEEK values ​​are available in MySQL since version 5.0.0.

DATE_FORMAT (date,format)

This command formats the date value according to the specified format string. The following pointers can be used in the format line. Format indicators must be preceded by a '%' character.

%a. Abbreviated name of the day of the week (Sun..Sat)

%b. Abbreviated month name (Jan..Dec)

%With. Numerical designation of the month (0…12)

%D. Day of the month with English suffix (0, 1, 2, 3,.)

%d. Numerical designation of the day of the month (00..31)

%e. Numerical designation of the day of the month (00..31)

%f. Microseconds (000000..999999)

%H. Hour (00..23)

%h. Hour (01..12)

%I. Hour (01..12)

%i. Numerical designation of minutes (00..59)

%J. Day of the year (001..366)

%k. Hour (0..23)

%l. Hour (1..12)

%M. Name of the month (January..December)

%m. Numerical designation of the month (00..12)

%R. AM or PM

%r. Time, 12-hour format (hh:mm:ss followed by AM or PM)

%S. Seconds (00..59)

%s. Seconds (00..59)

%T. Time, 24-hour format (hh:mm:ss)

%U. Week (00..53), where Sunday is the first day of the week

%u. Week (00..53), where Monday is the first day of the week

%V. Week (01..53), where Sunday is the first day of the week; used with %X

%v. Week (01..53), where Monday is the first day of the week; used with %x

%W. Name of the day of the week (Sunday..Saturday)

%w. Day of the week (0=Sunday..6=Saturday)

%X. Year for a week where the first day of the week is Sunday, a four-digit number; used with %V

%x. Year for a week where the first day of the week is Monday, a four-digit number; used with %V

%Y. Year, date, four digits

%y. Numerical designation of the year (two digits)

%%. Literally the % symbol

%x. x, for all.x. not listed above

DATE_SUB(date,INTERVAL expr unit)

Similar to the DATE_ADD() function.

DAY(date)

DAY() is a synonym for the DAYOFMONTH() function.

DAYNAME(date)

Returns the day of the week for the specified date.

DAYOFMONTH(date)

Returns the day of the month for the specified date in the range 0 to 31.

DAYOFWEEK(date)

Returns the index of the day of the week (1 = Sunday, 2 = Monday, ., 7 = Saturday). These index values ​​follow the ODBC standard.

DAYOFYEAR(date)

Returns the day of the year for the specified date in the range 1 to 366.

EXTRACT(unit FROM date)

The EXTRACT() function uses the same types of unit indicators as DATE_ADD() or DATE_SUB(), but does not perform arithmetic operations on dates, but rather extracts the unit indicator part of the date.

FROM_DAYS(N)

The DATE value is returned taking into account the number of days N.

Note. Use FROM_DAYS() for old dates carefully. The function is not intended to work with date values ​​before the introduction of the Gregorian calendar (1582).

FROM_UNIXTIME(unix_timestamp), FROM_UNIXTIME(unix_timestamp,format)

Returns a representation of the unix_timestamp argument as a value in the format "YYYY-MM-DD HH:MM:SS or YYYYMMDDHHMMSS" depending on whether this function is used in a string or numeric context. The value is expressed in the current time zone. The unix_timestamp parameter is an internal timestamp value that is generated by the UNIX_TIMESTAMP() function.

If format is specified, the result is formatted according to the format string, which is used in the same way as described in the DATE_FORMAT() section.

HOUR(time)

Returns the hours from the specified time. The range of the returned value is from 0 to 23. However, the range of TIME values ​​is actually much larger, so HOUR can return values ​​greater than 23.

LAST_DAY(date)

Takes a date or datetime value and returns the value corresponding to the last day of the month. Returns NULL if the argument is invalid.

LOCALTIME and LOCALTIME()

LOCALTIME and LOCALTIME() are synonyms for NOW().

LOCALTIMESTAMP and LOCALTIMESTAMP()

LOCALTIMESTAMP and LOCALTIMESTAMP() are synonyms for NOW().

MAKEDATE(year,dayofyear)

Returns the values ​​for the date, the specified year, and the day of the year. The value of dayofyear must be greater than 0 or the result will be NULL.

MAKETIME(hour,minute,second)

Returns the time value calculated from the arguments hour, minute and second.

MICROSECOND(expr)

Returns microseconds from a time expression or datetime(expr) expression as a number in the range 0 to 999999.

MINUTE(time)

Returns the minutes for the specified time, in the range 0 to 59.

MONTH(date)

Returns the month for the specified date in the range 0 to 12.

MONTHNAME(date)

Returns the full month name for the specified date.

NOW()

Returns the current date and time as a value in the format "YYYY-MM-DD HH:MM:SS" or YYYYMMDDHHMMSS, depending on whether the function is used in a string or numeric context. This value is expressed in the current time zone.

PERIOD_ADD(P,N)

Adds N months to period P (in YYMM or YYYYMM format). Returns a value in YYYYMM format. Note that the period argument P is not a date value.

PERIOD_DIFF(P1,P2)

Returns the number of months between periods P1 and P2. Periods P1 and P2 must be specified in the format YYMM or YYYYMM. Note that the period arguments P1 and P2 are not date values.

QUARTER(date)

Returns the quarter of the year for the specified date in the range 1 to 4.

SECOND(time)

Returns the seconds value for a time in the range 0 to 59.

SEC_TO_TIME(seconds)

Returns the seconds argument converted to hours, minutes, and seconds in the format "HH:MM:SS" or HHMMSS, depending on whether the function is used in a string or numeric context.

STR_TO_DATE(str,format)

This is the inverse of the DATE_FORMAT() function. It accepts the string str and the format string format. The STR_TO_DATE() function returns DATETIME if the format string contains both a date and time. Otherwise, it returns DATE or TIME if the string contains only a date or time.

SUBDATE(date,INTERVAL expr unit) and SUBDATE(expr,days)

If SUBDATE() is called with a second argument specified as INTERVAL, the function is a synonym for DATE_SUB(). For information about the INTERVAL argument, see DATE_ADD().

SUBTIME(expr1,expr2)

The SUBTIME() function returns expr1. expr2 is expressed as a value in the same format as expr1. The value of expr1 is a time or datetime expression, and the value of expr2 is a time expression.

SYSDATE()

Returns the current date and time as a value in the format "YYYY-MM-DD HH:MM:SS" or YYYYMMDDHHMMSS, depending on whether the function is used in a string or numeric context.

TIME(expr)

Extracts the time portion of expr and returns it as a string.

TIMEDIFF(expr1,expr2)

TIMEDIFF() returns expr1. expr2 is expressed as a time value. The values ​​expr1 and expr2 are either time or datetime expressions, but both must be of the same type.

TIMESTAMP (expr), TIMESTAMP (expr1, expr2)

With one argument specified, this function returns a date or datetime expression expr as a datetime value. With two arguments, it adds the time expression expr2 to the date or datetime expression expr1 and returns the result as a datetime value.

TIMESTAMPADD(unit,interval,datetime_expr)

This function adds an integer interval expression to a date or time expression datetime_expr. The units for the interval are specified by the unit argument, which can take one of the following values:

The unit value can be specified using one of the keywords as shown above, or with the SQL_TSI_ prefix. For example, DAY and SQL_TSI_DAY are valid values.

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

Returns the integer difference between the date or datetime expressions datetime_expr1 and datetime_expr2. The units of measurement for the result are specified by the unit argument. The valid values ​​for the unit argument are the same as those listed in the TIMESTAMPADD() function.

TIME_FORMAT(time,format)

This function is used in the same way as the DATE_FORMAT() function, but the format string can only contain format specifiers for hours, minutes, and seconds.

If the time value contains an hour part that is greater than 23, the clock format indicators %H and %k give a value greater than the normal range of 0 to 23. Other clock format indicators give a value of modulo 12 hour.

TIME_TO_SEC(time)

Returns the time argument converted to seconds.

TO_DAYS(date)

Returns the day number (number of days since year 0) for the given date date.

UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date)

If this function is called without an argument, it returns the Unix timestamp (seconds since "1970-01-01 00:00:00" UTC) as a positive integer. If UNIX_TIMESTAMP() is called with a date argument, it returns the value of the argument expressed in seconds since "1970-01-01 00:00:00" UTC. date can be a DATE string, a DATETIME string, a TIMESTAMP, or a number in the format YYMMDD or YYYYMMDD.

UTC_DATE, UTC_DATE()

Returns the current UTC date as a value in the format "YYYY-MM-DD" or YYYYMMDD, depending on whether this function is used in a string or numeric context.

UTC_TIME, UTC_TIME()

Returns the current UTC time as a value in the format "HH:MM:SS" or HHMMSS, depending on whether the function is used in a string or numeric context.

UTC_TIMESTAMP, UTC_TIMESTAMP()

Returns the current UTC date and time as the value "YYYY-MM-DD HH:MM:SS" or in the format YYYYMMDDHHMMSS, depending on whether this function is used in a string or numeric context.

WEEK(date[,mode])

This function returns the week number for the given date date. The two-argument WEEK() form allows you to specify whether the week starts on Sunday or Monday, and whether the return value should be in the range 0 to 53 or 1 to 53. If the mode argument is omitted, the value of the default_week_format system variable is used

WEEKDAY(date)

Returns the index of the day of the week for the given date date (0 = Monday, 1 = Tuesday, 6 = Sunday).

WEEKOFYEAR(date)

Returns the calendar week for the given date date as a number in the range 1 to 53. WEEKOFYEAR() is a compatibility function equivalent to WEEK(date,3).

YEAR(date)

Returns the year for the given date, ranging from 1000 to 9999, or 0 for date.zero.

YEARWEEK(date), YEARWEEK(date,mode)

Returns the year and week for the given date date. The mode argument works exactly like the mode argument to the WEEK() function. The resulting year may be different from the year in the date argument for the first and last week of the year.

Note. The week number is different from what WEEK() will return (0) for the optional 0 or 1 arguments, because WEEK() will return the week in the context of a given year.