Getdate (transact-sql)getdate (transact-sql)

SQL Справочник

SQL Ключевые слова ADD ADD CONSTRAINT ALTER ALTER COLUMN ALTER TABLE ALL AND ANY AS ASC BACKUP DATABASE BETWEEN CASE CHECK COLUMN CONSTRAINT CREATE CREATE DATABASE CREATE INDEX CREATE OR REPLACE VIEW CREATE TABLE CREATE PROCEDURE CREATE UNIQUE INDEX CREATE VIEW DATABASE DEFAULT DELETE DESC DISTINCT DROP DROP COLUMN DROP CONSTRAINT DROP DATABASE DROP DEFAULT DROP INDEX DROP TABLE DROP VIEW EXEC EXISTS FOREIGN KEY FROM FULL OUTER JOIN GROUP BY HAVING IN INDEX INNER JOIN INSERT INTO INSERT INTO SELECT IS NULL IS NOT NULL JOIN LEFT JOIN LIKE LIMIT NOT NOT NULL OR ORDER BY OUTER JOIN PRIMARY KEY PROCEDURE RIGHT JOIN ROWNUM SELECT SELECT DISTINCT SELECT INTO SELECT TOP SET TABLE TOP TRUNCATE TABLE UNION UNION ALL UNIQUE UPDATE VALUES VIEW WHERE

MySQL Функции Функции строк ASCII CHAR_LENGTH CHARACTER_LENGTH CONCAT CONCAT_WS FIELD FIND_IN_SET FORMAT INSERT INSTR LCASE LEFT LENGTH LOCATE LOWER LPAD LTRIM MID POSITION REPEAT REPLACE REVERSE RIGHT RPAD RTRIM SPACE STRCMP SUBSTR SUBSTRING SUBSTRING_INDEX TRIM UCASE UPPER Функции чисел ABS ACOS ASIN ATAN ATAN2 AVG CEIL CEILING COS COT COUNT DEGREES DIV EXP FLOOR GREATEST LEAST LN LOG LOG10 LOG2 MAX MIN MOD PI POW POWER RADIANS RAND ROUND SIGN SIN SQRT SUM TAN TRUNCATE Функции дат ADDDATE ADDTIME CURDATE CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURTIME DATE DATEDIFF DATE_ADD DATE_FORMAT DATE_SUB DAY DAYNAME DAYOFMONTH DAYOFWEEK DAYOFYEAR EXTRACT FROM_DAYS HOUR LAST_DAY LOCALTIME LOCALTIMESTAMP MAKEDATE MAKETIME MICROSECOND MINUTE MONTH MONTHNAME NOW PERIOD_ADD PERIOD_DIFF QUARTER SECOND SEC_TO_TIME STR_TO_DATE SUBDATE SUBTIME SYSDATE TIME TIME_FORMAT TIME_TO_SEC TIMEDIFF TIMESTAMP TO_DAYS WEEK WEEKDAY WEEKOFYEAR YEAR YEARWEEK Функции расширений BIN BINARY CASE CAST COALESCE CONNECTION_ID CONV CONVERT CURRENT_USER DATABASE IF IFNULL ISNULL LAST_INSERT_ID NULLIF SESSION_USER SYSTEM_USER USER VERSION

SQL Server функции Функции строк ASCII CHAR CHARINDEX CONCAT Concat with + CONCAT_WS DATALENGTH DIFFERENCE FORMAT LEFT LEN LOWER LTRIM NCHAR PATINDEX QUOTENAME REPLACE REPLICATE REVERSE RIGHT RTRIM SOUNDEX SPACE STR STUFF SUBSTRING TRANSLATE TRIM UNICODE UPPER Функции чисел ABS ACOS ASIN ATAN ATN2 AVG CEILING COUNT COS COT DEGREES EXP FLOOR LOG LOG10 MAX MIN PI POWER RADIANS RAND ROUND SIGN SIN SQRT SQUARE SUM TAN Функции дат CURRENT_TIMESTAMP DATEADD DATEDIFF DATEFROMPARTS DATENAME DATEPART DAY GETDATE GETUTCDATE ISDATE MONTH SYSDATETIME YEAR Функции расширений CAST COALESCE CONVERT CURRENT_USER IIF ISNULL ISNUMERIC NULLIF SESSION_USER SESSIONPROPERTY SYSTEM_USER USER_NAME

MS Access функции Функции строк Asc Chr Concat with & CurDir Format InStr InstrRev LCase Left Len LTrim Mid Replace Right RTrim Space Split Str StrComp StrConv StrReverse Trim UCase Функции чисел Abs Atn Avg Cos Count Exp Fix Format Int Max Min Randomize Rnd Round Sgn Sqr Sum Val Функции дат Date DateAdd DateDiff DatePart DateSerial DateValue Day Format Hour Minute Month MonthName Now Second Time TimeSerial TimeValue Weekday WeekdayName Year Другие функции CurrentUser Environ IsDate IsNull IsNumeric

SQL ОператорыSQL Типы данныхSQL Краткий справочник


STR_TO_DATE(str,format)

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

mysql> SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y');
+---------------------------------------------------------+
| STR_TO_DATE('04/31/2004', '%m/%d/%Y')                   |
+---------------------------------------------------------+
| 2004-04-31                                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

TIME_FORMAT(time,format)

This function is used like the DATE_FORMAT() function, but the format string may contain format specifiers only for hours, minutes and seconds.

If the time value contains an hour part that is greater than 23, the %H and %k hour format specifiers produce a value larger than the usual range of 0 to 23. The other hour format specifiers produce the hour value modulo 12.

mysql> SELECT TIME_FORMAT('100:00:00', '%H %k %h %I %l');
+---------------------------------------------------------+
| TIME_FORMAT('100:00:00', '%H %k %h %I %l')              |
+---------------------------------------------------------+
| 100 100 04 04 4                                         |
+---------------------------------------------------------+
1 row in set (0.00 sec)

CONVERT_TZ(dt,from_tz,to_tz)

This converts a datetime value dt from the time zone given by from_tz to the time zone given by to_tz and returns the resulting value. This function returns NULL if the arguments are invalid.

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');
+---------------------------------------------------------+
| CONVERT_TZ('2004-01-01 12:00:00','GMT','MET')           |
+---------------------------------------------------------+
| 2004-01-01 13:00:00                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');
+---------------------------------------------------------+
| CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00')     |
+---------------------------------------------------------+
| 2004-01-01 22:00:00                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

Пример

Рассмотрим несколько примеров функции TO_CHAR и изучим, как использовать функцию TO_CHAR в Oracle/PLSQL.

Для чисел

Ниже приведены примеры для функции TO_CHAR для чисел.

Oracle PL/SQL

SQL> SELECT TO_CHAR(1242.78, ‘9999.9’) FROM DUAL;

—Результат: 1242.8

SQL> SELECT TO_CHAR(-1242.78, ‘9999.9’) FROM DUAL;

—Результат: -1242.8

SQL> SELECT TO_CHAR(1242.78, ‘9,999.99’) FROM DUAL;

—Результат: 1,242.78


SQL> SELECT TO_CHAR(1242.78, ‘$9,999.00’) FROM DUAL;

—Результат: $1,242.78

SQL> SELECT TO_CHAR(18, ‘000099’) FROM DUAL;

—Результат: 000018

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

SQL>SELECTTO_CHAR(1242.78,’9999.9′)FROMDUAL;   —Результат:    1242.8  

SQL>SELECTTO_CHAR(-1242.78,’9999.9′)FROMDUAL;   —Результат:   -1242.8  

SQL>SELECTTO_CHAR(1242.78,’9,999.99′)FROMDUAL;   —Результат:    1,242.78  

SQL>SELECTTO_CHAR(1242.78,’$9,999.00′)FROMDUAL;   —Результат:   $1,242.78  

SQL>SELECTTO_CHAR(18,’000099′)FROMDUAL;   —Результат:    000018

Для дат

Ниже приведен список допустимых параметров, когда функция TO_CHAR используется для преобразования даты в строку. Эти параметры могут быть использованы во многих комбинациях.

Параметр Пояснение
YEAR Год.
YYYY 4-значный год.
YYY YY Y Последние 3, 2 или 1 цифра(ы) года.
IYY IY I Последние 3, 2 или 1 цифра(ы) года ISO.
IYYY 4-значный год в соответствии со стандартом ISO.
Q Квартал года (1, 2, 3, 4; JAN-MAR = 1).
MM Месяц (01-12; JAN = 01).
MON Сокращенное название месяца.
MONTH Название месяца, дополненное пробелами длиной до 9 символов.
RM Римская цифра RM (I-XII; JAN = I).
WW Неделя года (1-53), где неделя 1 начинается в первый день года и продолжается до седьмого дня года.
W Неделя месяца (1-5), где неделя 1 начинается в первый день месяца и заканчивается седьмым.
IW Неделя года (1-52 или 1-53) на основе стандарта ISO.
D День недели (1-7).
DAY Название дня.
DD День месяца (1-31).
DDD День года (1-366).
DY Сокращенное название дня.
J юлианский день; количество дней с 1 января 4712 г. до н.э.
HH Час дня (1-12).
HH12 Час дня (1-12).
HH24 Час дня (0-23).
MI Минуты (0-59).
SS Секунды (0-59).
SSSSS Секунды после полуночи (0-86399).
FF Дробные секунды.

Ниже приведены примеры функции TO_CHAR для дат.

Oracle PL/SQL

SQL> SELECT TO_CHAR(sysdate, ‘yyyy/mm/dd’) FROM DUAL;

—Результат: 2014/08/28

SQL> SELECT TO_CHAR(sysdate, ‘yyyy.mm.dd’) FROM DUAL;

—Результат: 2014.08.28

SQL> SELECT TO_CHAR(sysdate, ‘Month DD, YYYY’) FROM DUAL;

—Результат: Август 28, 2014

SQL> SELECT TO_CHAR(sysdate, ‘FMMonth DD, YYYY’) FROM DUAL;

—Результат: Август 28, 2014


SQL> SELECT TO_CHAR(sysdate, ‘MON DDth, YYYY’) FROM DUAL;

—Результат: АВГ 28TH, 2014

SQL> SELECT TO_CHAR(sysdate, ‘FMMON DDth, YYYY’) FROM DUAL;

—Результат: АВГ 28TH, 2014

SQL> SELECT TO_CHAR(sysdate, ‘FMMon ddth, YYYY’) FROM DUAL;

—Результат: Авг 28th, 2014

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

SQL>SELECTTO_CHAR(sysdate,’yyyy/mm/dd’)FROMDUAL;   —Результат:   2014/08/28  

SQL>SELECTTO_CHAR(sysdate,’yyyy.mm.dd’)FROMDUAL;   —Результат:   2014.08.28  

SQL>SELECTTO_CHAR(sysdate,’Month DD, YYYY’)FROMDUAL;   —Результат:   Август   28, 2014  

SQL>SELECTTO_CHAR(sysdate,’FMMonth DD, YYYY’)FROMDUAL;   —Результат:   Август 28, 2014  

SQL>SELECTTO_CHAR(sysdate,’MON DDth, YYYY’)FROMDUAL;   —Результат:   АВГ 28TH, 2014  

SQL>SELECTTO_CHAR(sysdate,’FMMON DDth, YYYY’)FROMDUAL;   —Результат:   АВГ 28TH, 2014  

SQL>SELECTTO_CHAR(sysdate,’FMMon ddth, YYYY’)FROMDUAL;   —Результат:   Авг 28th, 2014

Вы заметите, что в некоторых примерах функции TO_CHAR, параметр format_mask начинается с ‘FM’. Это означает, что нули и пробелы подавлены. Это можно увидеть в приведенных ниже примерах.

Oracle PL/SQL

SQL> SELECT TO_CHAR(SYSDATE, ‘FMMonth DD, YYYY’) FROM DUAL;

—Результат: Август 9, 2014

SQL> SELECT TO_CHAR(SYSDATE, ‘FMMON DDth, YYYY’) FROM DUAL;

—Результат: АВГ 9TH, 2014

SQL> SELECT TO_CHAR(SYSDATE, ‘FMMon ddth, YYYY’) FROM DUAL;

—Результат: Авг 9th, 2014

1 2 3 4 5 6 7 8 9 10 11

SQL>SELECTTO_CHAR(SYSDATE,’FMMonth DD, YYYY’)FROMDUAL;   —Результат:   Август 9, 2014  

SQL>SELECTTO_CHAR(SYSDATE,’FMMON DDth, YYYY’)FROMDUAL;   —Результат:   АВГ 9TH, 2014  

SQL>SELECTTO_CHAR(SYSDATE,’FMMon ddth, YYYY’)FROMDUAL;   —Результат:   Авг 9th, 2014

Нули были подавлены, так что день показывает, как «9», а не «09».

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

When invoked with the INTERVAL form of the second argument, ADDDATE() is a synonym for DATE_ADD(). The related function SUBDATE() is a synonym for DATE_SUB(). For information on the INTERVAL unit argument, see the discussion for DATE_ADD().

mysql> SELECT DATE_ADD('1998-01-02', INTERVAL 31 DAY);
+---------------------------------------------------------+
| DATE_ADD('1998-01-02', INTERVAL 31 DAY)                 |
+---------------------------------------------------------+
| 1998-02-02                                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT ADDDATE('1998-01-02', INTERVAL 31 DAY);
+---------------------------------------------------------+
| ADDDATE('1998-01-02', INTERVAL 31 DAY)                  |
+---------------------------------------------------------+
| 1998-02-02                                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

When invoked with the days form of the second argument, MySQL treats it as an integer number of days to be added to expr.

mysql> SELECT ADDDATE('1998-01-02', 31);
+---------------------------------------------------------+
| DATE_ADD('1998-01-02', INTERVAL 31 DAY)                 |
+---------------------------------------------------------+
| 1998-02-02                                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

SQL References

SQL Keywords ADD ADD CONSTRAINT ALTER ALTER COLUMN ALTER TABLE ALL AND ANY AS ASC BACKUP DATABASE BETWEEN CASE CHECK COLUMN CONSTRAINT CREATE CREATE DATABASE CREATE INDEX CREATE OR REPLACE VIEW CREATE TABLE CREATE PROCEDURE CREATE UNIQUE INDEX CREATE VIEW DATABASE DEFAULT DELETE DESC DISTINCT DROP DROP COLUMN DROP CONSTRAINT DROP DATABASE DROP DEFAULT DROP INDEX DROP TABLE DROP VIEW EXEC EXISTS FOREIGN KEY FROM FULL OUTER JOIN GROUP BY HAVING IN INDEX INNER JOIN INSERT INTO INSERT INTO SELECT IS NULL IS NOT NULL JOIN LEFT JOIN LIKE LIMIT NOT NOT NULL OR ORDER BY OUTER JOIN PRIMARY KEY PROCEDURE RIGHT JOIN ROWNUM SELECT SELECT DISTINCT SELECT INTO SELECT TOP SET TABLE TOP TRUNCATE TABLE UNION UNION ALL UNIQUE UPDATE VALUES VIEW WHERE

MySQL Functions String Functions ASCII CHAR_LENGTH CHARACTER_LENGTH CONCAT CONCAT_WS FIELD FIND_IN_SET FORMAT INSERT INSTR LCASE LEFT LENGTH LOCATE LOWER LPAD LTRIM MID POSITION REPEAT REPLACE REVERSE RIGHT RPAD RTRIM SPACE STRCMP SUBSTR SUBSTRING SUBSTRING_INDEX TRIM UCASE UPPER

Numeric Functions ABS ACOS ASIN ATAN ATAN2 AVG CEIL CEILING COS COT COUNT DEGREES DIV EXP FLOOR GREATEST LEAST LN LOG LOG10 LOG2 MAX MIN MOD PI POW POWER RADIANS RAND ROUND SIGN SIN SQRT SUM TAN TRUNCATE

Date Functions ADDDATE ADDTIME CURDATE CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURTIME DATE DATEDIFF DATE_ADD DATE_FORMAT DATE_SUB DAY DAYNAME DAYOFMONTH DAYOFWEEK DAYOFYEAR EXTRACT FROM_DAYS HOUR LAST_DAY LOCALTIME LOCALTIMESTAMP MAKEDATE MAKETIME MICROSECOND MINUTE MONTH MONTHNAME NOW PERIOD_ADD PERIOD_DIFF QUARTER SECOND SEC_TO_TIME STR_TO_DATE SUBDATE SUBTIME SYSDATE TIME TIME_FORMAT TIME_TO_SEC TIMEDIFF TIMESTAMP TO_DAYS WEEK WEEKDAY WEEKOFYEAR YEAR YEARWEEK

Advanced Functions BIN BINARY CASE CAST COALESCE CONNECTION_ID CONV CONVERT CURRENT_USER DATABASE IF IFNULL ISNULL LAST_INSERT_ID NULLIF SESSION_USER SYSTEM_USER USER VERSION

SQL Server Functions String Functions ASCII CHAR CHARINDEX CONCAT Concat with + CONCAT_WS DATALENGTH DIFFERENCE FORMAT LEFT LEN LOWER LTRIM NCHAR PATINDEX QUOTENAME REPLACE REPLICATE REVERSE RIGHT RTRIM SOUNDEX SPACE STR STUFF SUBSTRING TRANSLATE TRIM UNICODE UPPER

Numeric Functions ABS ACOS ASIN ATAN ATN2 AVG CEILING COUNT COS COT DEGREES EXP FLOOR LOG LOG10 MAX MIN PI POWER RADIANS RAND ROUND SIGN SIN SQRT SQUARE SUM TAN

Date Functions CURRENT_TIMESTAMP DATEADD DATEDIFF DATEFROMPARTS DATENAME DATEPART DAY GETDATE GETUTCDATE ISDATE MONTH SYSDATETIME YEAR

Advanced Functions CAST COALESCE CONVERT CURRENT_USER IIF ISNULL ISNUMERIC NULLIF SESSION_USER SESSIONPROPERTY SYSTEM_USER USER_NAME

MS Access Functions String Functions Asc Chr Concat with & CurDir Format InStr InstrRev LCase Left Len LTrim Mid Replace Right RTrim Space Split Str StrComp StrConv StrReverse Trim UCase

Numeric Functions Abs Atn Avg Cos Count Exp Fix Format Int Max Min Randomize Rnd Round Sgn Sqr Sum Val

Date Functions Date DateAdd DateDiff DatePart DateSerial DateValue Day Format Hour Minute Month MonthName Now Second Time TimeSerial TimeValue Weekday WeekdayName Year

Other Functions CurrentUser Environ IsDate IsNull IsNumeric

SQL OperatorsSQL Data TypesSQL Quick Ref

SQL Учебник

SQL ГлавнаяSQL ВведениеSQL СинтаксисSQL SELECTSQL SELECT DISTINCTSQL WHERESQL AND, OR, NOTSQL ORDER BYSQL INSERT INTOSQL Значение NullSQL Инструкция UPDATESQL Инструкция DELETESQL SELECT TOPSQL MIN() и MAX()SQL COUNT(), AVG() и …SQL Оператор LIKESQL ПодстановочныйSQL Оператор INSQL Оператор BETWEENSQL ПсевдонимыSQL JOINSQL JOIN ВнутриSQL JOIN СлеваSQL JOIN СправаSQL JOIN ПолноеSQL JOIN СамSQL Оператор UNIONSQL GROUP BYSQL HAVINGSQL Оператор ExistsSQL Операторы Any, AllSQL SELECT INTOSQL INSERT INTO SELECTSQL Инструкция CASESQL Функции NULLSQL ХранимаяSQL Комментарии

SQL Учебник

SQL ГлавнаяSQL ВведениеSQL СинтаксисSQL SELECTSQL SELECT DISTINCTSQL WHERESQL AND, OR, NOTSQL ORDER BYSQL INSERT INTOSQL Значение NullSQL Инструкция UPDATESQL Инструкция DELETESQL SELECT TOPSQL MIN() и MAX()SQL COUNT(), AVG() и …SQL Оператор LIKESQL ПодстановочныйSQL Оператор INSQL Оператор BETWEENSQL ПсевдонимыSQL JOINSQL JOIN ВнутриSQL JOIN СлеваSQL JOIN СправаSQL JOIN ПолноеSQL JOIN СамSQL Оператор UNIONSQL GROUP BYSQL HAVINGSQL Оператор ExistsSQL Операторы Any, AllSQL SELECT INTOSQL INSERT INTO SELECTSQL Инструкция CASESQL Функции NULLSQL ХранимаяSQL Комментарии

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

These functions perform date arithmetic. The date is a DATETIME or DATE value specifying the starting date. The expr is an expression specifying the interval value to be added or subtracted from the starting date. The expr is a string; it may start with a ‘-‘ for negative intervals.

A unit is a keyword indicating the units in which the expression should be interpreted.

The INTERVAL keyword and the unit specifier are not case sensitive.

The following table shows the expected form of the expr argument for each unit value.

unit Value Expected exprFormat
MICROSECOND MICROSECONDS
SECOND SECONDS
MINUTE MINUTES
HOUR HOURS
DAY DAYS
WEEK WEEKS
MONTH MONTHS
QUARTER QUARTERS
YEAR YEARS
SECOND_MICROSECOND ‘SECONDS.MICROSECONDS’
MINUTE_MICROSECOND ‘MINUTES.MICROSECONDS’
MINUTE_SECOND ‘MINUTES:SECONDS’
HOUR_MICROSECOND ‘HOURS.MICROSECONDS’
HOUR_SECOND ‘HOURS:MINUTES:SECONDS’
HOUR_MINUTE ‘HOURS:MINUTES’
DAY_MICROSECOND ‘DAYS.MICROSECONDS’
DAY_SECOND ‘DAYS HOURS:MINUTES:SECONDS’
DAY_MINUTE ‘DAYS HOURS:MINUTES’
DAY_HOUR ‘DAYS HOURS’
YEAR_MONTH ‘YEARS-MONTHS’

The values QUARTER and WEEK are available from the MySQL 5.0.0. version.

mysql> SELECT DATE_ADD('1997-12-31 23:59:59', 
   -> INTERVAL '1:1' MINUTE_SECOND);
+---------------------------------------------------------+
| DATE_ADD('1997-12-31 23:59:59', INTERVAL...             |
+---------------------------------------------------------+
| 1998-01-01 00:01:00                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_ADD('1999-01-01', INTERVAL 1 HOUR);
+---------------------------------------------------------+
| DATE_ADD('1999-01-01', INTERVAL 1 HOUR)                 |
+---------------------------------------------------------+
| 1999-01-01 01:00:00                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

MySQL String Functions

Function Description
ASCII Returns the ASCII value for the specific character
CHAR_LENGTH Returns the length of a string (in characters)
CHARACTER_LENGTH Returns the length of a string (in characters)
CONCAT Adds two or more expressions together
CONCAT_WS Adds two or more expressions together with a separator
FIELD Returns the index position of a value in a list of values
FIND_IN_SET Returns the position of a string within a list of strings
FORMAT Formats a number to a format like «#,###,###.##», rounded to a specified number of decimal places
INSERT Inserts a string within a string at the specified position and for a certain number of characters
INSTR Returns the position of the first occurrence of a string in another string
LCASE Converts a string to lower-case
LEFT Extracts a number of characters from a string (starting from left)
LENGTH Returns the length of a string (in bytes)
LOCATE Returns the position of the first occurrence of a substring in a string
LOWER Converts a string to lower-case
LPAD Left-pads a string with another string, to a certain length
LTRIM Removes leading spaces from a string
MID Extracts a substring from a string (starting at any position)
POSITION Returns the position of the first occurrence of a substring in a string
REPEAT Repeats a string as many times as specified
REPLACE Replaces all occurrences of a substring within a string, with a new substring
REVERSE Reverses a string and returns the result
RIGHT Extracts a number of characters from a string (starting from right)
RPAD Right-pads a string with another string, to a certain length
RTRIM Removes trailing spaces from a string
SPACE Returns a string of the specified number of space characters
STRCMP Compares two strings
SUBSTR Extracts a substring from a string (starting at any position)
SUBSTRING Extracts a substring from a string (starting at any position)
SUBSTRING_INDEX Returns a substring of a string before a specified number of delimiter occurs
TRIM Removes leading and trailing spaces from a string
UCASE Converts a string to upper-case
UPPER Converts a string to upper-case

FROM_UNIXTIME(unix_timestamp,format)

Returns a representation of the unix_timestamp argument as a value in ‘YYYY-MM-DD HH:MM:SS or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or in a numeric context. The value is expressed in the current time zone. The unix_timestamp argument is an internal timestamp values, which are produced by the UNIX_TIMESTAMP() function.

If the format is given, the result is formatted according to the format string, which is used in the same way as is listed in the entry for the DATE_FORMAT() function.

mysql> SELECT FROM_UNIXTIME(875996580);
+---------------------------------------------------------+
| FROM_UNIXTIME(875996580)                                |
+---------------------------------------------------------+
| 1997-10-04 22:23:00                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

Returns the integer difference between the date or datetime expressions datetime_expr1 and datetime_expr2. The unit for the result is given by the unit argument. The legal values for the unit are the same as those listed in the description of the TIMESTAMPADD() function.

mysql> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');
+---------------------------------------------------------+
| TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01')          |
+---------------------------------------------------------+
| 3                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

SQL Дата типы данных

MySQL поставляется со следующими типами данных для хранения даты или даты / значение времени в базе данных:

  • ДАТА — формат YYYY-MM-DD
  • DATETIME — формат: YYYY-MM-DD HH: MI: SS
  • TIMESTAMP — формат: YYYY-MM-DD HH: MI: SS
  • ГОД — формат YYYY или YY

SQL Server поставляется со следующими типами данных для хранения даты или даты / значение времени в базе данных:

  • ДАТА — формат YYYY-MM-DD
  • DATETIME — формат: YYYY-MM-DD HH: MI: SS
  • SMALLDATETIME — формат: YYYY-MM-DD HH: MI: SS
  • TIMESTAMP — формат: уникальный номер

Note: Типы даты выбраны для столбца при создании новой таблицы в базе данных!

Для обзора всех типов данных , доступных, перейдите к нашей полной Справочник по типам данных .


С этим читают