Functions for working with dates in sql. SQL - Date Functions


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.

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.

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.

Last update: 07/29/2017

T-SQL provides a number of functions for working with dates and times:

    GETDATE: Returns the current local date and time based on the system clock as a datetime object

    SELECT GETDATE() -- 2017-07-28 21:34:55.830

    GETUTCDATE : Returns the current local date and time in Greenwich Mean Time (UTC/GMT) as a datetime object

    SELECT GETUTCDATE() -- 2017-07-28 18:34:55.830

    SYSDATETIME: Returns the current local date and time based on the system clock, but differs from GETDATE in that the date and time is returned as a datetime2 object

    SELECT SYSDATETIME() -- 2017-07-28 21:02:22.7446744

    SYSUTCDATETIME : Returns the current local date and time in Greenwich Mean Time (UTC/GMT) as a datetime2 object

    SELECT SYSUTCDATETIME() -- 2017-07-28 18:20:27.5202777

    SYSDATETIMEOFFSET : returns a datetimeoffset(7) object that contains the date and time relative to GMT

    SELECT SYSDATETIMEOFFSET() -- 2017-07-28 21:02:22.7446744 +03:00

    DAY: returns the day of the date, which is passed as a parameter

    SELECT DAY(GETDATE()) -- 28

    MONTH : returns the month of a date

    SELECT MONTH(GETDATE()) -- 7

    YEAR : returns the year from a date

    SELECT YEAR(GETDATE()) -- 2017

    DATENAME: Returns the date portion as a string. The date part selection parameter is passed as the first parameter, and the date itself is passed as the second parameter:

    SELECT DATENAME(month, GETDATE()) -- July

    To determine part of a date, you can use the following parameters (their abbreviated versions are indicated in parentheses):

    • year (yy, yyyy) : year

      quarter (qq, q) : quarter

      month (mm, m) : month

      dayofyear (dy, y) : day of the year

      day (dd, d) : day of the month

      week (wk, ww) : week

      weekday (dw) : day of the week

      hour (hh) : hour

      minute (mi, n) : minute

      second (ss, s) : second

      millisecond (ms) : millisecond

      microsecond (mcs) : microsecond

      nanosecond (ns) : nanosecond

      tzoffset (tz) : offset in minutes relative to GMT (for a datetimeoffset object)

    DATEPART: Returns the date part as a number. The date part selection parameter is passed as the first parameter (the same parameters are used as for DATENAME), and the date itself is passed as the second parameter:

    SELECT DATEPART(month, GETDATE()) -- 7

    DATEADD: Returns a date that is the result of adding a number to a specific date component. The first parameter represents the date component described above for the DATENAME function. The second parameter is the quantity to be added. The third parameter is the date itself, to which you need to add:

    SELECT DATEADD(month, 2, "2017-7-28") -- 2017-09-28 00:00:00.000 SELECT DATEADD(day, 5, "2017-7-28") -- 2017-08-02 00 :00:00.000 SELECT DATEADD(day, -5, "2017-7-28") -- 2017-07-23 00:00:00.000

    If the quantity added represents a negative number, then the date is actually decremented.

    DATEDIFF: Returns the difference between two dates. The first parameter is the date component, which indicates in what units the difference should be measured. The second and third parameters are the dates being compared:

    SELECT DATEDIFF(year, "2017-7-28", "2018-9-28") -- difference 1 year SELECT DATEDIFF(month, "2017-7-28", "2018-9-28") -- difference 14 months SELECT DATEDIFF(day, "2017-7-28", "2018-9-28") -- difference 427 days

    TODATETIMEOFFSET : Returns a datetimeoffset value that is the result of adding the time offset to another datetimeoffset object

    SELECT TODATETIMEOFFSET("2017-7-28 01:10:22", "+03:00")

    SWITCHOFFSET: Returns a datetimeoffset value that is the result of adding the time offset to a datetime2 object

    SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), "+02:30")

    EOMONTH : Returns the date of the last day for the month that is used in the date passed as a parameter.

    SELECT EOMONTH("2017-02-05") -- 2017-02-28 SELECT EOMONTH("2017-02-05", 3) -- 2017-05-31

    As an optional second parameter, you can pass the number of months that need to be added to the date. Then the last day of the month will be calculated for the new date.

    DATEFROMPARTS : by year, month and day creates a date

    SELECT DATEFROMPARTS(2017, 7, 28) -- 2017-07-28

    ISDATE: Tests whether an expression is a date. If it is, it returns 1, otherwise it returns 0.

    SELECT ISDATE("2017-07-28") -- 1 SELECT ISDATE("2017-28-07") -- 0 SELECT ISDATE("07-28-2017") -- 0 SELECT ISDATE("SQL") - - 0

An example of using the functions is creating an order table that contains the order date:

CREATE TABLE Orders (Id INT IDENTITY PRIMARY KEY, ProductId INT NOT NULL, CustomerId INT NOT NULL, CreatedAt DATE NOT NULL DEFAULT GETDATE(), ProductCount INT DEFAULT 1, Price MONEY NOT NULL);

The DEFAULT GETDATE() expression specifies that if no date is supplied when adding data, it is automatically calculated using the GETDATE() function.

Another example - let's find orders that were made 16 days ago:

SELECT * FROM Orders WHERE DATEDIFF(day, CreatedAt, GETDATE()) = 16

SQL working with dates– is so important that you cannot do without knowledge of basic sql operators in any worthwhile project. Whatever one may say, in all services there is a need to work with time. As a rule, this is the calculation of periods from one date to another, for example, displaying a list of registered users for a year, month, day, hour.

I want to give a number of solutions to common tasks related to working with dates in SQL, which I myself encountered on a daily basis, I hope this will be relevant and useful for you.

How to get current date in SQL
WHERE date = CURDATE()
or another option
WHERE date = STR_TO_DATE(now(), "%Y-%m-%d")

Add one hour to date in SQL
DATE_ADD("2013-03-30", INTERVAL 1 HOUR)

Add one day to a date in SQL
DATE_ADD("2013-03-30", INTERVAL 1 DAY)
Similarly, you can add any number of days to the current date.

Add one month to a date in SQL
DATE_ADD("2013-03-30", INTERVAL 1 MONTH)
Similarly, you can add any number of months to the current date.

Get yesterday's day in SQL
DATE_ADD(CURDATE(), INTERVAL -1 DAY)
or
DATE_SUB(CURDATE(), INTERVAL 1 DAY)

Get start date of current week in SQL
This is one of the most difficult tasks at first glance, but it can be solved very simply
CURDATE()-WEEKDAY(CURDATE());

Get a selection from this Monday to the current day of the week in SQL

Get a selection from the first day of the current month to the current day of the week in SQL
WHERE (date BETWEEN (CURDATE()-WEEKDAY(CURDATE())) AND CURDATE())

How to get user's date of birth in SQL
SELECT name, birth, CURRENT_DATE, (YEAR(CURRENT_DATE)-YEAR(birth)) - (RIGHT(CURRENT_DATE,5)

Find all users whose birthday is next month in SQL
SELECT name, birth FROM user WHERE MONTH(birth) = MONTH(DATE_ADD(NOW(), INTERVAL 1 MONTH));
or another option
SELECT name, birth FROM pet WHERE MONTH(birth) = MOD(MONTH(NOW()), 12) + 1;

In addition to the above cases on working with dates in SQL, I recommend that you read the documentation for the following operators:
NOW()– Returns the current date and time.
CURDATE()– Returns the current date.
CURTIME()– We return the current time.
DATE()– Consists of two parts date and time.
EXTRACT()– Returns a single date/time value.
DATE_ADD()– Adds the specified number of days/minutes/hours, etc. to the sample.
DATE_SUB()– Subtract the specified interval from the date.
DATEDIFF()– Returns the time value between two dates.
DATE_FORMAT()– Function for different output of time information.

Working with dates in SQl, as it turns out, is not so difficult, and now, instead of calculating periods using PHP, you can do this at the stage of executing the SQL query and get the necessary data selection.


2024 gtavrl.ru.