Содержание
MySQL Date Functions
Function | Description |
---|---|
ADDDATE | Adds a time/date interval to a date and then returns the date |
ADDTIME | Adds a time interval to a time/datetime and then returns the time/datetime |
CURDATE | Returns the current date |
CURRENT_DATE | Returns the current date |
CURRENT_TIME | Returns the current time |
CURRENT_TIMESTAMP | Returns the current date and time |
CURTIME | Returns the current time |
DATE | Extracts the date part from a datetime expression |
DATEDIFF | Returns the number of days between two date values |
DATE_ADD | Adds a time/date interval to a date and then returns the date |
DATE_FORMAT | Formats a date |
DATE_SUB | Subtracts a time/date interval from a date and then returns the date |
DAY | Returns the day of the month for a given date |
DAYNAME | Returns the weekday name for a given date |
DAYOFMONTH | Returns the day of the month for a given date |
DAYOFWEEK | Returns the weekday index for a given date |
DAYOFYEAR | Returns the day of the year for a given date |
EXTRACT | Extracts a part from a given date |
FROM_DAYS | Returns a date from a numeric datevalue |
HOUR | Returns the hour part for a given date |
LAST_DAY | Extracts the last day of the month for a given date |
LOCALTIME | Returns the current date and time |
LOCALTIMESTAMP | Returns the current date and time |
MAKEDATE | Creates and returns a date based on a year and a number of days value |
MAKETIME | Creates and returns a time based on an hour, minute, and second value |
MICROSECOND | Returns the microsecond part of a time/datetime |
MINUTE | Returns the minute part of a time/datetime |
MONTH | Returns the month part for a given date |
MONTHNAME | Returns the name of the month for a given date |
NOW | Returns the current date and time |
PERIOD_ADD | Adds a specified number of months to a period |
PERIOD_DIFF | Returns the difference between two periods |
QUARTER | Returns the quarter of the year for a given date value |
SECOND | Returns the seconds part of a time/datetime |
SEC_TO_TIME | Returns a time value based on the specified seconds |
STR_TO_DATE | Returns a date based on a string and a format |
SUBDATE | Subtracts a time/date interval from a date and then returns the date |
SUBTIME | Subtracts a time interval from a datetime and then returns the time/datetime |
SYSDATE | Returns the current date and time |
TIME | Extracts the time part from a given time/datetime |
TIME_FORMAT | Formats a time by a specified format |
TIME_TO_SEC | Converts a time value into seconds |
TIMEDIFF | Returns the difference between two time/datetime expressions |
TIMESTAMP | Returns a datetime value based on a date or datetime value |
TO_DAYS | Returns the number of days between a date and date «0000-00-00» |
WEEK | Returns the week number for a given date |
WEEKDAY | Returns the weekday number for a given date |
WEEKOFYEAR | Returns the week number for a given date |
YEAR | Returns the year part for a given date |
YEARWEEK | Returns the year and week number for a given date |
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 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
datepart 界限datepart boundaries
下列陳述式具有相同的 startdate 和相同的 enddate 值。The following statements have the same startdate and the same enddate values. 這些日期都很接近且時間差距為一百奈秒 (.0000001 秒)。Those dates are adjacent and they differ in time by a hundred nanoseconds (.0000001 second). 每個陳述式中 startdate 與 enddate 之間的差異會跨越其 datepart 的日曆或時間界限。The difference between the startdate and enddate in each statement crosses one calendar or time boundary of its datepart. 每個陳述式都會傳回 1。Each statement returns 1.
如果 startdate 和 enddate 具有不同年份值,但具有相同日曆週值,則 會針對 datepart week 傳回 0。If startdate and enddate have different year values, but they have the same calendar week values, will return 0 for datepart week.
引數Arguments
datepartdatepartDATEDIFF 所報告 startdate 和 enddate 之間差異的單位。The units in which DATEDIFF reports the difference between the startdate and enddate. 常用的 datepart 單位包括 或 。Commonly used datepart units include or .
Datepart 值不能在變數中指定,也不能是以引號括住的字串 (例如 )。The datepart value cannot be specified in a variable, nor as a quoted string like .
下表列出所有有效的 datepart 值。The following table lists all the valid datepart values. DATEDIFF 接受 datepart 的完整名稱,或任何所列出的完整名稱縮寫。DATEDIFF accepts either the full name of the datepart, or any listed abbreviation of the full name.
datepart 名稱datepart name | datepart 縮寫datepart abbreviation |
---|---|
yearyear | yy, yyyyyy, yyyy |
quarterquarter | qq, qqq, q |
monthmonth | mm, mmm, m |
dayofyeardayofyear | dy, ydy, y |
dayday | dd, ddd, d |
weekweek | wk, wwwk, ww |
hourhour | hhhh |
minuteminute | mi, nmi, n |
secondsecond | ss, sss, s |
millisecondmillisecond | msms |
microsecondmicrosecond | mcsmcs |
nanosecondnanosecond | nsns |
注意
每個特定 datepart 名稱和該 datepart 名稱的縮寫都會傳回相同值。Each specific datepart name and abbreviations for that datepart name will return the same value.
startdatestartdate可解析成下列其中一個值的運算式:An expression that can resolve to one of the following values:
- datedate
- datetimedatetime
- datetimeoffsetdatetimeoffset
- datetime2datetime2
- smalldatetimesmalldatetime
- timetime
請使用四位數年份以避免模糊不清。Use four-digit years to avoid ambiguity. 如需兩位數年份值的資訊,請參閱設定兩位數年份的截止伺服器設定選項。See Configure the two digit year cutoff Server Configuration Option for information about two-digit year values.
enddateenddate請參閱<startdate>。See startdate.
Example in SQL/Queries
You can also use the DateDiff function in a query in Microsoft Access.
For example:
In this query, we have used the DateDiff function as follows:
Expr1: DateDiff('d',,Date())
and
Expr2: DateDiff('d',#15/10/2003#,#22/11/2003#)
The first DateDiff function will calculate the difference in days between the CategoryDate field and the current system time. The results will be displayed in a column called Expr1. You can replace Expr1 with a column name that is more meaningful.
For example:
Difference: DateDiff('d',,Date())
The results would now be displayed in a column called Difference.
The second DateDiff function will calculate the difference in days between the two dates — 15/10/2003 and 22/11/2003. The results will be displayed in a column called Expr2.
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 Ключевые слова 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 Краткий справочник
How to use the DateDiff SQL function with an Aggregate function
The following example returns the number of years between the first hire date and the last hire date. In this case, we’re looking for the minimum HireDate and the maximum HireDate aggregate function used as input parameters for DATEDIFF SQL function. With these values, one could easily find the number of years existed between the first time hire and the last time hire.
1 2 |
SELECTDATEDIFF(YEAR,MIN(HireDate),MAX(HireDate))No_Of_Years FROMHumanResources.Employee; |
Let’s take a look at the following example. The value 1 is added to the various datepart and the value -1 is added to the hour datepart and -30 are added to minute datepart.
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 28 29 30 31 32 33 34 35 |
SELECT’TodayDate’, GETDATE() UNIONALL SELECT’year’, DATEADD(year,1,GETDATE()) UNIONALL SELECT’quarter’, DATEADD(quarter,1,GETDATE()) UNIONALL SELECT’month’, DATEADD(month,1,GETDATE()) UNIONALL SELECT’dayofyear’, DATEADD(dayofyear,1,GETDATE()) UNIONALL SELECT’day’, DATEADD(day,1,GETDATE()) UNIONALL SELECT’week’, DATEADD(week,1,GETDATE()) UNIONALL SELECT’weekday’, DATEADD(weekday,1,GETDATE()) UNIONALL SELECT’hour’, DATEADD(hour,-1,GETDATE()) UNIONALL SELECT’minute’, DATEADD(minute,-30,GETDATE()) UNIONALL SELECT’second’, DATEADD(second,1,GETDATE()) UNIONALL SELECT’millisecond’, DATEADD(millisecond,1,GETDATE()) |
In the output, we can see that the value is operated on the GETDATE() function as per the defined datepart.
Note: DATEADD and DATEDIFF SQL function can be used in the SELECT, WHERE, HAVING, GROUP BY and ORDER BY clauses.
Wrap Up
Thus far, we have seen very important SQL date format functions such as DATEPART, DATENAME, YEAR, MONTH, and DAY, with particular emphasis on the DATEADD SQL function and the DATEDIFF SQL function. SQL date formatfunctions in SQL server are really powerful and really helps to deliver impactful data analytics and reports. In some cases, it’s a matter of adjusting collation settings, location settings, simply readjusting the input stream of text value, or correction made the data source would suffice the date time problem.
It is recommended to use the neutral language format because it works better in most cases. At last, we see the difference between DATEDIFF SQL function and DATEDIFF_BIG SQL function. I hope you like this article. If you have any questions, feel free comment below
С этим читают
- Работа с датой и временем в mysql
- Mysql — string functions
- Like, regexp. вывод данных по шаблону (маске) в mysql
- In (transact-sql)in (transact-sql)
- Sql — оператор union
- Оператор between (transact-sql)between (transact-sql)
- Identity (функция) (transact-sql)identity (function) (transact-sql)
- Mysql функция sum()
- Sql server: alter table statement
- Таблицы oracle