Greetings, dear readers of the blog site. A database often needs to store various data related to date and time. This may be the date the information was added, the date of user registration, the time of last authorization and other data. IN SQL language There are many functions related to date and time, so today we’ll look at them.

All the functions discussed below work with calendar data types.

Getting the current date and time.

To obtain current date and time function is used NOW().

SELECT NOW()
Result: 2015-09-25 14:42:53

To receive only current date there is a function CURDATE().

SELECT CURDATE()
Result: 2015-09-25

And function CURTIME(), which returns only current time:

SELECT CURTIME()
Result: 14:42:53

The CURDATE() and NOW() functions are useful for adding records to a database for which you want to store the date they were added. For example, when adding an article to a website, it would be a good idea to store its publication date. Then the request to add an article to the database will be something like this:

INSERT INTO posts (id_post, text_post, date_publication) VALUES (1, "article text", NOW ());

Adding and subtracting dates and times

Function ADDDATE (date, INTERVAL value) adds to date date meaning value and returns the resulting value. The following values can be used as value:

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

as well as their combinations:

MINUTE_SECOND - minutes and seconds
HOUR_SECOND hours - minutes and seconds
HOUR_MINUTE - hours and minutes
DAY_SECOND - days, hours, minutes and seconds
DAY_MINUTE - days, hours and minutes
DAY_HOUR - days and hours
YEAR_MONTH - years and months.

SELECT ADDDATE ("2015-09-28 10:30:20", INTERVAL 1 DAY)
Result: 2015-09-29 10:30:20

SELECT ADDDATE ("2015-09-28 10:30:20", INTERVAL "3 1:20" DAY_MINUTE)
Result: 2015-10-01 11:50:20

Function SUBDATE (date, INTERVAL value) produces subtraction values from date date. Example:

SELECT SUBDATE ("2015-09-28 10:30:20", INTERVAL 20 HOUR)
Result: 2015-09-27 14:30:20

Function PERIOD_ADD(period, n) adds to the value period n months. The period value must be in YYYYMM format (for example, September 2015 would be 201509). Example:

SELECT PERIOD_ADD (201509, 4)
Result: 201601

Function TIMESTAMPADD(interval, n, date) adds to date date time interval n , whose values 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 (QUARTER, 1, "2015-09-28")
Result: 2015-12-28

Function SUBTIME (date, time) subtracts from date date time time. Example:

SELECT SUBTIME("2015-09-28 10:30:20", "50:20:19")
Result: 2015-09-26 08:10:01

Calculating the interval between dates

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

SELECT TIMEDIFF("2015-09-28 10:30:20", "2015-09-29 10:30:20")
Result: -24:10:00

Function DATEDIFF(date1, date2) calculates difference in days between two dates, while hours, minutes and seconds are ignored when specifying dates. Example:

SELECT DATEDIFF("2015-09-28 00:00:20", "2015-09-27 23:40:20")
Result: 1

Using this function, it is easy to determine how many days have passed since the publication date of the article:

SELECT DATEDIFF (CURDATE(), date_publication) FROM posts WHERE id_post = 1

Function PERIOD_DIFF (period1, period2) calculates difference in months between two dates. Dates must be in YYYYMM format. For example, let’s find out how many months have passed from January 2015 to September 2015:

SELECT PERIOD_DIFF (201509, 201501)
Result: 9

Function TIMESTAMPDIFF(interval, date1, date2) calculates the difference between dates date2 and date1 in the units specified in the interval parameter. In this case, interval can take the following values:

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

SELECT TIMESTAMPDIFF (HOUR, "2015-09-28 10:30:20", "2015-09-28 19:50:20")
Result: 9

Retrieving various date and time formats and other information

Function DATE (datetime) returns date, cutting off time. Example:

SELECT DATE("2015-09-28 10:30:20")
Result: 2015-09-28

Function TIME (datetime) returns time, cutting off date. Example:

SELECT TIME ("2015-09-28 10:30:20")
Result: 10:30:20

Function TIMESTAMP (date) returns full format over time dates date . Example:

TIMESTAMP("2015-09-28")
Result: 2015-09-28 00:00:00

DAY (date) And DAYOFMONTH (date). Synonymous functions that return serial number of the day of the month. Example:

SELECT DAY("2015-09-28"), DAYOFMONTH("2015-09-28")
Result: 28 | 28

Functions DAYNAME (date),DAYOFWEEK (date) And WEEKDAY (date). The first function returns name of the day of the week, second - day of the week number(counting from 1 - Sunday to 7 - Saturday), the third is also the number of the day of the week, only another countdown (counting from 0 - Monday, to 6 - Sunday). Example:

SELECT DAYNAME("2015-09-28"), DAYOFWEEK("2015-09-28"), WEEKDAY("2015-09-28")
Result: Monday 2 | 0

Functions WEEK (date) And WEEKOFYEAR (datetime). Both functions return week number of the year, only the first week begins on Sunday, and the second on Monday. Example:

SELECT WEEK ("2015-09-28 10:30:20"), WEEKOFYEAR ("2015-09-28 10:30:20")
Result: 39 | 40

Function MONTH (date) returns numeric value of the month(from 1 to 12), and MONTHNAME (date) month name. Example:

SELECT MONTH("2015-09-28 10:30:20"), MONTHNAME("2015-09-28 10:30:20")
Result: 9 | September

Function QUARTER (date) returns block number years (from 1 to 4). Example:

SELECT QUARTER ("2015-09-28 10:30:20")
Result: 3

Function YEAR (date) returns year value(from 1000 to 9999). Example:

SELECT YEAR ("2015-09-28 10:30:20")
Result: 2015

Function DAYOFYEAR (date) returns serial number of the day per year (from 1 to 366). Primer:

SELECT DAYOFYEAR ("2015-09-28 10:30:20")
Result: 271

Function HOUR (datetime) returns hour value(from 0 to 23). Example:

SELECT HOUR ("2015-09-28 10:30:20")
Result: 10

Function MINUTE (datetime) returns minutes value(from 0 to 59). Example:

SELECT MINUTE ("2015-09-28 10:30:20")
Result: 30

Function SECOND (datetime) returns seconds value(from 0 to 59). Example:

SELECT SECOND ("2015-09-28 10:30:20")
Result: 20

Function EXTRACT (type FROM date) returns the date part specified by the type parameter. Example:

SELECT EXTRACT (YEAR FROM "2015-09-28 10:30:20"), EXTRACT (MONTH FROM "2015-09-28 10:30:20"), EXTRACT (DAY FROM "2015-09-28 10:30 :20"), EXTRACT (HOUR FROM "2015-09-28 10:30:20"), EXTRACT (MINUTE FROM "2015-09-28 10:30:20"), EXTRACT (SECOND FROM "2015-09- 28 10:30:20")
Result: 2015 | 9 | 28 | 10 | 30 | 20

Reciprocal functions TO_DAYS (date) And FROM_DAYS(n). First converts date to number of days, passed since the zero year. The second, on the contrary, accepts number of days, passed from year zero and converts them to date. Example:

SELECT TO_DAYS ("2015-09-28 10:30:20"), FROM_DAYS (736234)
Result: 736234 | 2015-09-28

Reciprocal functions UNIX_TIMESTAMP (date) And FROM_UNIXTIME(n). First converts date to number of seconds passed since January 1, 1970. The second, on the contrary, accepts number of seconds, from January 1, 1970 and converts them to date. Example:

SELECT UNIX_TIMESTAMP ("2015-09-28 10:30:20"), FROM_UNIXTIME (1443425420)
Result: 1443425420 | 2015-09-28 10:30:20

Reciprocal functions TIME_TO_SEC (time) And SEC_TO_TIME(n). First converts time to number of seconds, passed from 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. Example:

SELECT TIME_TO_SEC ("10:30:20"), SEC_TO_TIME (37820)
Result: 37820 | 10:30:20

Function MAKEDATE (year, n) takes the year year and the number of the day in year n and converts them to a date. Example.

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 date and returns 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.

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 (i.e. negative values are returned as positive) 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 the argument p positive number, the fractional part of the number 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 last instruction Transact-SQL 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. Supported in Transact- SQL functions The dates 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 nth quantity units specified in the item argument for the specified date 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 description:

Transact-SQL System Functions

Function	Syntax	Description	Usage example
CAST	CAST (w AS type [(length)]	Converts expression w to specified type data 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.

Getting the current date and time

To get the current date (without time) in YYYY-MM-DD format, you can use the functions:

CURDATE()
CURRENT_DATE()

To obtain the current time (without date) in the HH:MM:SS format, you can use the functions:

CURTIME()
CURRENT_TIME()

To obtain the current date and time in the YYYY-MM-DD HH:MM:SS format, you can use any of the following functions:

NOW()
SYSDATE()
CURRENT_TIMESTAMP

Example 1

SELECT CURDATE(); or SELECT CURRENT_DATE();

Result: 2016-08-28

SELECT CURTIME(); or SELECT CURENT_TIME();

Result: 19:29:54

SELECT NOW(); or SELECT SYSDATE(); or SELECT CURRENT_TIMESTAMP;

Result: 2016-08-28 19:29:54

Adding and subtracting a time interval

With dates and times, you often have to perform arithmetic operations: add or subtract a certain time interval to a given date. Adding a time interval can be done in three ways:

Function DATE_ADD(original_date, INTERVAL expression type);
Function ADDDATE(original_date, INTERVAL expression type);
Expression: source_date + INTERVAL expression type.

Subtracting a time interval can also be done in three ways:

Function DATE_SUB(source_date, INTERVAL expression type);
Function SUBDATE(source_date, INTERVAL expression type);
Expression: source_date - INTERVAL expression type.

Source_date is a date to which a certain time interval is added or subtracted. Expression– this is the added or subtracted time interval itself, specified in text format. Type– an argument indicating the type of interval to be added. This option specifies how to correctly interpret the expression. For example, the expression '3:24' can be interpreted as either 3 hours 24 minutes or 3 minutes 24 seconds. If the type "MINUTE_SECOND" is specified, then the interpretation will be unambiguous. The relationship between the expression and type arguments is shown in the table:

Example 2

The next three commands do the same operation. They add a second to the given time.

SELECT "2016-09-10 23:59:59" + Interval 1 SECOND;

SELECT ADDDATE("2016-09-10 23:59:59", Interval 1 SECOND);

SELECT DATE_ADD("2016-09-10 23:59:59", Interval 1 SECOND);

The result of all three teams is the same: 2016-09-11 00:00:00.

Example 3

The following three commands calculate a date that is exactly one and a half years prior to a given one:

SELECT DATE_SUB("2016-09-10 23:59:59", Interval "1-6" YEAR_MONTH);

SELECT SUBDATE("2016-09-10 23:59:59", Interval "1-6" YEAR_MONTH);

SELECT "2016-09-10 23:59:59" - Interval "1-6" YEAR_MONTH;

The result of all three teams is the same: 2015-03-10 23:59:59.

Conversion functions to other units

Sometimes it is convenient to work with a date represented as the number of days that have passed since year 0. To convert a date to this format and back in MySQL, there are two functions: