SQL Date 函数

一、SQL 日期

当我们处理日期时，最难的任务恐怕是确保所插入的日期的格式，与数据库中日期列的格式相匹配。

只要数据包含的只是日期部分，运行查询就不会出问题。但是，如果涉及时间，情况就有点复杂了。

在讨论日期查询的复杂性之前，我们先来看看最重要的内建日期处理函数。

二、MySQL Date 函数

下面的表格列出了 MySQL 中最重要的内建日期函数：

2.1、MySQL NOW() 函数

2.2.1、 定义和用法

NOW() 函数返回当前的日期和时间。

2.2.2、 语法

NOW()

2.2.3、 实例

2.2.3.1、 例子 1

下面是 SELECT 语句：

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

结果类似：

2.2.3.2、 例子 2

下面的 SQL 创建带有日期时间列 (OrderDate) 的 "Orders" 表：

CREATE TABLE Orders 
(
OrderId int NOT NULL,
ProductName varchar(50) NOT NULL,
OrderDate datetime NOT NULL DEFAULT NOW(),
PRIMARY KEY (OrderId)
)

请注意，OrderDate 列规定 NOW() 作为默认值。作为结果，当您向表中插入行时，当前日期和时间自动插入列中。

现在，我们希望在 "Orders" 表中插入一条新记录：

INSERT INTO Orders (ProductName) VALUES ('Computer')

"Orders" 表将类似这样：

2.2、MySQL CURDATE() 函数

2.2.1、 定义和用法

CURDATE() 函数返回当前的日期。

2.2.2、语法

CURDATE()

2.2.3、 实例

2.2.3.1、 例子 1

下面是 SELECT 语句：

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

结果类似：

2.2.3.2、例子 2

下面的 SQL 创建带有日期时间列 (OrderDate) 的 "Orders" 表：

CREATE TABLE Orders 
(
OrderId int NOT NULL,
ProductName varchar(50) NOT NULL,
OrderDate datetime NOT NULL DEFAULT CURDATE(),
PRIMARY KEY (OrderId)
)

请注意，OrderDate 列规定 CURDATE() 作为默认值。作为结果，当您向表中插入行时，当前日期和时间自动插入列中。

现在，我们希望在 "Orders" 表中插入一条新记录：

INSERT INTO Orders (ProductName) VALUES ('Computer')

"Orders" 表将类似这样：

2.3、MySQL CURTIME() 函数

2.3.1、 定义和用法

CURTIME() 函数返回当前的时间。

2.3.2、 语法

CURTIME()

2.3.3、 实例

下面是 SELECT 语句：

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

结果类似：

2.4、 MySQL DATE() 函数

2.4.1、定义和用法

DATE() 函数返回日期或日期/时间表达式的日期部分。

2.4.2、语法

DATE(date)

date 参数是合法的日期表达式。

2.4.3、实例

假设我们有如下的表：

我们使用下面的 SELECT 语句：

SELECT ProductName, DATE(OrderDate) AS OrderDate
FROM Orders
WHERE OrderId=1

结果：

2.5、MySQL EXTRACT() 函数

2.5.1、定义和用法

EXTRACT() 函数用于返回日期/时间的单独部分，比如年、月、日、小时、分钟等等。

2.5.2、语法

EXTRACT(unit FROM date)

date 参数是合法的日期表达式。unit 参数可以是下列的值：

2.5.3、实例

假设我们有如下的表：

我们使用下面的 SELECT 语句：

SELECT EXTRACT(YEAR FROM OrderDate) AS OrderYear,
EXTRACT(MONTH FROM OrderDate) AS OrderMonth,
EXTRACT(DAY FROM OrderDate) AS OrderDay
FROM Orders
WHERE OrderId=1

结果：

2.6、MySQL DATE_ADD() 函数

2.6.1、定义和用法

DATE_ADD() 函数向日期添加指定的时间间隔。

2.6.2、语法

DATE_ADD(date,INTERVAL expr type)

date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。

type 参数可以是下列值：

2.6.3、实例

假设我们有如下的表：

现在，我们希望向 "OrderDate" 添加 2 天，这样就可以找到付款日期。

我们使用下面的 SELECT 语句：

SELECT OrderId,DATE_ADD(OrderDate,INTERVAL 2 DAY) AS OrderPayDate
FROM Orders

结果：

2.7、MySQL DATE_SUB() 函数

2.7.1、定义和用法

DATE_SUB() 函数从日期减去指定的时间间隔。

2.7.2、语法

DATE_SUB(date,INTERVAL expr type)

date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。

type 参数可以是下列值：

2.7.3、实例

假设我们有如下的表：

现在，我们希望从 "OrderDate" 减去 2 天。

我们使用下面的 SELECT 语句：

SELECT OrderId,DATE_SUB(OrderDate,INTERVAL 2 DAY) AS OrderPayDate
FROM Orders

结果：

2.8、MySQL DATEDIFF() 函数

2.8.1、定义和用法

DATEDIFF() 函数返回两个日期之间的天数。

2.8.2、语法

DATEDIFF(date1,date2)

date1 和 date2 参数是合法的日期或日期/时间表达式。

注释：只有值的日期部分参与计算。

2.8.3、实例

2.8.3.1、例子 1

使用如下 SELECT 语句：

SELECT DATEDIFF('2008-12-30','2008-12-29') AS DiffDate

结果：

2.8.3.2、 例子 2

使用如下 SELECT 语句：

SELECT DATEDIFF('2008-12-29','2008-12-30') AS DiffDate

结果：

2.9、MySQL DATE_FORMAT() 函数

2.9.1、定义和用法

DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据。

2.9.2、语法

DATE_FORMAT(date,format)

date 参数是合法的日期。format 规定日期/时间的输出格式。

可以使用的格式有：

2.9.3、实例

下面的脚本使用 DATE_FORMAT() 函数来显示不同的格式。我们使用 NOW() 来获得当前的日期/时间：

DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p')
DATE_FORMAT(NOW(),'%m-%d-%Y')
DATE_FORMAT(NOW(),'%d %b %y')
DATE_FORMAT(NOW(),'%d %b %Y %T:%f')

结果类似：

Dec 29 2008 11:45 PM
12-29-2008
29 Dec 08
29 Dec 2008 16:25:46.635

三、SQL Server Date 函数

下面的表格列出了 SQL Server 中最重要的内建日期函数：

3.1、SQL Server GETDATE() 函数

3.1.1、定义和用法

GETDATE() 函数从 SQL Server 返回当前的时间和日期。

3.1.2、语法

GETDATE()

3.1.3、实例

3.1.3.1、例子 1

使用下面的 SELECT 语句：

SELECT GETDATE() AS CurrentDateTime

结果：

注释：上面的时间部分精确到毫秒。

3.1.3.2、例子 2

下面的 SQL 创建带有日期时间列 (OrderDate) 的 "Orders" 表：

CREATE TABLE Orders 
(
OrderId int NOT NULL PRIMARY KEY,
ProductName varchar(50) NOT NULL,
OrderDate datetime NOT NULL DEFAULT GETDATE()
)

请注意，OrderDate 把 GETDATE() 规定为默认值。结果是，当您在表中插入新行时，当前日期和时间自动插入列中。

现在，我们希望在 "Orders" 表中插入一条记录：

INSERT INTO Orders (ProductName) VALUES ('Computer')

"Orders" 表将成为这样：

3.2、SQL Server DATEPART() 函数

3.2.1、定义和用法

DATEPART() 函数用于返回日期/时间的单独部分，比如年、月、日、小时、分钟等等。

3.2.2、语法

DATEPART(datepart,date)

date 参数是合法的日期表达式。datepart 参数可以是下列的值：

3.2.3、实例

假设我们有下面这个 "Orders" 表：

我们使用如下 SELECT 语句：

SELECT DATEPART(yyyy,OrderDate) AS OrderYear,
DATEPART(mm,OrderDate) AS OrderMonth,
DATEPART(dd,OrderDate) AS OrderDay
FROM Orders
WHERE OrderId=1

结果：

3.3、SQL Server DATEADD() 函数

3.3.1、定义和用法

DATEADD() 函数在日期中添加或减去指定的时间间隔。

3.3.2、语法

DATEADD(datepart,number,date)

date 参数是合法的日期表达式。number 是您希望添加的间隔数；对于未来的时间，此数是正数，对于过去的时间，此数是负数。

datepart 参数可以是下列的值：

3.3.3、实例

假设我们有下面这个 "Orders" 表：

现在，我们希望向 "OrderDate" 添加 2 天，这样就可以找到付款日期。

我们使用如下 SELECT 语句：

SELECT OrderId,DATEADD(day,2,OrderDate) AS OrderPayDate
FROM Orders

结果：

3.4、SQL Server DATEDIFF() 函数

3.4.1、定义和用法

DATEDIFF() 函数返回两个日期之间的时间。

3.4.2、语法

DATEDIFF(datepart,startdate,enddate)

startdate 和 enddate 参数是合法的日期表达式。

datepart 参数可以是下列的值：

3.4.3、实例

3.4.3.1、例子 1

使用如下 SELECT 语句：

SELECT DATEDIFF(day,'2008-12-29','2008-12-30') AS DiffDate

结果：

3.4.3.2、例子 2

使用如下 SELECT 语句：

SELECT DATEDIFF(day,'2008-12-30','2008-12-29') AS DiffDate

结果：

3.5、SQL Server CONVERT() 函数

3.5.1、定义和用法

CONVERT() 函数是把日期转换为新数据类型的通用函数。

CONVERT() 函数可以用不同的格式显示日期/时间数据。

3.5.2、语法

CONVERT(data_type(length),data_to_be_converted,style)

data_type(length) 规定目标数据类型（带有可选的长度）。data_to_be_converted 含有需要转换的值。style 规定日期/时间的输出格式。

可以使用的 style 值：

3.5.3、实例

下面的脚本使用 CONVERT() 函数来显示不同的格式。我们将使用 GETDATE() 函数来获得当前的日期/时间：

CONVERT(VARCHAR(19),GETDATE())
CONVERT(VARCHAR(10),GETDATE(),110) 
CONVERT(VARCHAR(11),GETDATE(),106)
CONVERT(VARCHAR(24),GETDATE(),113)

结果类似：

Dec 29 2008 11:45 PM
12-29-2008
29 Dec 08
29 Dec 2008 16:25:46.635

四、SQL Date 数据类型

MySQL 使用下列数据类型在数据库中存储日期或日期/时间值：

• DATE - 格式：YYYY-MM-DD
• DATETIME - 格式：YYYY-MM-DD HH:MM:SS
• TIMESTAMP - 格式：YYYY-MM-DD HH:MM:SS
• YEAR - 格式：YYYY 或 YY

SQL Server 使用下列数据类型在数据库中存储日期或日期/时间值：

• DATE - 格式 YYYY-MM-DD
• DATETIME - 格式: YYYY-MM-DD HH:MM:SS
• SMALLDATETIME - 格式: YYYY-MM-DD HH:MM:SS
• TIMESTAMP - 格式: 唯一的数字

五、SQL 日期处理

如果不涉及时间部分，那么我们可以轻松地比较两个日期！

假设我们有下面这个 "Orders" 表：

现在，我们希望从上表中选取 OrderDate 为 "2008-12-26" 的记录。

我们使用如下 SELECT 语句：

SELECT * FROM Orders WHERE OrderDate='2008-12-26'

结果集：

现在假设 "Orders" 类似这样（请注意 "OrderDate" 列中的时间部分）：

如果我们使用上面的 SELECT 语句：

SELECT * FROM Orders WHERE OrderDate='2008-12-26'

那么我们得不到结果。这是由于该查询不含有时间部分的日期。

六、MySQL 中 date 类型的一些比较

假如有个表product有个字段add_time,它的数据类型为datetime ，有人可能会这样写sql：

• select * from product where add_time = '2013-01-12' 
  

对于这种语句，如果你存储的格式是YY-mm-dd是这样的，那么OK，如果你存储的格式是：2013-01-12 23:23:56这种格式你就悲剧了，这是你就可以使用DATE()函数用来返回日期的部分，所以这条sql应该如下处理：

• select * from product where Date(add_time) = '2013-01-12' 
  

再来一个，如果你要查询2013年1月份加入的产品呢？

• select * from product where date(add_time) between '2013-01-01' and '2013-01-31'  
  -- 你还可以这样写:
  select * from product where Year(add_time) = 2013 and Month(add_time) = 1
  

这些你该知道mysql日期函数在对你处理日期比较问题的作用了吧？

其date_col的值是在最后30天以内：

• mysql> SELECT something FROM table  WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) <= 30; 
  

DAYOFWEEK(date)

返回日期date的星期索引(1=星期天，2=星期一, ……7=星期六)。这些索引值对应于ODBC标准。

• mysql> select DAYOFWEEK('1998-02-03');  -> 3
  

WEEKDAY(date)

返回date的星期索引(0=星期一，1=星期二, ……6= 星期天)。

• mysql> select WEEKDAY('1997-10-04 22:23:00');  -> 5  
  mysql> select WEEKDAY('1997-11-05');  -> 2
  

DAYOFMONTH(date)

返回date的月份中日期，在1到31范围内。

• mysql> select DAYOFMONTH('1998-02-03');  -> 3
  

DAYOFYEAR(date)

返回date在一年中的日数, 在1到366范围内。

• mysql> select DAYOFYEAR('1998-02-03');  -> 34
  

MONTH(date)

返回date的月份，范围1到12。

• mysql> select MONTH('1998-02-03');  -> 2
  

DAYNAME(date)

返回date的星期名字。

• mysql> select DAYNAME("1998-02-05");  -> 'Thursday'
  

MONTHNAME(date)

返回date的月份名字。

• mysql> select MONTHNAME("1998-02-05");  -> 'February'
  

QUARTER(date)

返回date一年中的季度，范围1到4。

• mysql> select QUARTER('98-04-01');  -> 2
  

本栏目推荐文章
• Jmeter 如何连接mysql数据库？
• 如何通过Navicat把MySQL远程数据库导入到本地数据库
• 【LeetCode 2701. 连续递增交易】MySQL用户变量编程得到严格递增连续子序列的开始位置，结束位置，长度等统计信息
• 【Leetcode 2474. 购买量严格增加的客户】MySQL用户变量编程解决严格递增连续子序列问题
• mysql发生连接异常Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
• 【LeetCode 2494. 合并在同一个大厅重叠的活动】MySQL用户变量编程解决区间合并问题
• 如何处理Mysql死锁
• 【LeetCode1747. 应该被禁止的 Leetflex 账户】MySQL用户变量编程；尝试维护一个multiset
• Mysql时间常用函数
• 【Leetcode1949. 坚定的友谊】使用MySQL在无向图中寻找{"CompleteTripartite", {1, 1, 3}}这个pattern

MySQL DATEdate_format format mysql date date_add mysql date add date_sub mysql date sub date_format函数format mysql 函数str_to_date mysql date 函数date_add mysql date date to_date date_add date_format