MySQL DATE

SQL Date 函数

一、SQL 日期

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

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

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

二、MySQL Date 函数

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

函数	描述
NOW()	返回当前的日期和时间
CURDATE()	返回当前的日期
CURTIME()	返回当前的时间
DATE()	提取日期或日期/时间表达式的日期部分
EXTRACT()	返回日期/时间按的单独部分
DATE_ADD()	给日期添加指定的时间间隔
DATE_SUB()	从日期减去指定的时间间隔
DATEDIFF()	返回两个日期之间的天数
DATE_FORMAT()	用不同的格式显示日期/时间

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()

结果类似：

NOW()	CURDATE()	CURTIME()
2008-12-29 16:25:46	2008-12-29	16:25:46

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" 表将类似这样：

OrderId	ProductName	OrderDate
1	'Computer'	2008-12-29 16:25:46.635

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()

结果类似：

NOW()	CURDATE()	CURTIME()
2008-12-29 16:25:46	2008-12-29	16:25:46

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" 表将类似这样：

OrderId	ProductName	OrderDate
1	'Computer'	2008-12-29

2.3、MySQL CURTIME() 函数

2.3.1、定义和用法

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

2.3.2、语法

CURTIME()

2.3.3、实例

下面是 SELECT 语句：

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

结果类似：

NOW()	CURDATE()	CURTIME()
2008-12-29 16:25:46	2008-12-29	16:25:46

2.4、 MySQL DATE() 函数

2.4.1、定义和用法

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

2.4.2、语法

DATE(date)

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

2.4.3、实例

假设我们有如下的表：

OrderId	ProductName	OrderDate
1	'Computer'	2008-12-29 16:25:46.635

我们使用下面的 SELECT 语句：

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

结果：

ProductName	OrderDate
'Computer'	2008-12-29

2.5、MySQL EXTRACT() 函数

2.5.1、定义和用法

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

2.5.2、语法

EXTRACT(unit FROM date)

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

Unit 值
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH

2.5.3、实例

假设我们有如下的表：

OrderId	ProductName	OrderDate
1	'Computer'	2008-12-29 16:25:46.635

我们使用下面的 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

结果：

OrderYear	OrderMonth	OrderDay
2008	12	29

2.6、MySQL DATE_ADD() 函数

2.6.1、定义和用法

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

2.6.2、语法

DATE_ADD(date,INTERVAL expr type)

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

type 参数可以是下列值：

Type 值
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH

2.6.3、实例

假设我们有如下的表：

OrderId	ProductName	OrderDate
1	'Computer'	2008-12-29 16:25:46.635

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

我们使用下面的 SELECT 语句：

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

结果：

OrderId	OrderPayDate
1	2008-12-31 16:25:46.635

2.7、MySQL DATE_SUB() 函数

2.7.1、定义和用法

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

2.7.2、语法

DATE_SUB(date,INTERVAL expr type)

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

type 参数可以是下列值：

Type 值
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH

2.7.3、实例

假设我们有如下的表：

OrderId	ProductName	OrderDate
1	'Computer'	2008-12-29 16:25:46.635

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

我们使用下面的 SELECT 语句：

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

结果：

OrderId	OrderPayDate
1	2008-12-27 16:25:46.635

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

结果：

DiffDate
1

2.8.3.2、例子 2

使用如下 SELECT 语句：

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

结果：

DiffDate
-1

2.9、MySQL DATE_FORMAT() 函数

2.9.1、定义和用法

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

2.9.2、语法

DATE_FORMAT(date,format)

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

可以使用的格式有：

格式	描述
%a	缩写星期名
%b	缩写月名
%c	月，数值
%D	带有英文前缀的月中的天
%d	月的天，数值(00-31)
%e	月的天，数值(0-31)
%f	微秒
%H	小时 (00-23)
%h	小时 (01-12)
%I	小时 (01-12)
%i	分钟，数值(00-59)
%j	年的天 (001-366)
%k	小时 (0-23)
%l	小时 (1-12)
%M	月名
%m	月，数值(00-12)
%p	AM 或 PM
%r	时间，12-小时（hh:mm:ss AM 或 PM）
%S	秒(00-59)
%s	秒(00-59)
%T	时间, 24-小时 (hh:mm:ss)
%U	周 (00-53) 星期日是一周的第一天
%u	周 (00-53) 星期一是一周的第一天
%V	周 (01-53) 星期日是一周的第一天，与 %X 使用
%v	周 (01-53) 星期一是一周的第一天，与 %x 使用
%W	星期名
%w	周的天（0=星期日, 6=星期六）
%X	年，其中的星期日是周的第一天，4 位，与 %V 使用
%x	年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y	年，4 位
%y	年，2 位

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 中最重要的内建日期函数：

函数	描述
GETDATE()	返回当前日期和时间
DATEPART()	返回日期/时间的单独部分
DATEADD()	在日期中添加或减去指定的时间间隔
DATEDIFF()	返回两个日期之间的时间
CONVERT()	用不同的格式显示日期/时间

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

结果：

CurrentDateTime
2008-12-29 16:25:46.635

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

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" 表将成为这样：

OrderId	ProductName	OrderDate
1	'Computer'	2008-12-29 16:25:46.635

3.2、SQL Server DATEPART() 函数

3.2.1、定义和用法

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

3.2.2、语法

DATEPART(datepart,date)

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

datepart	缩写
年	yy, yyyy
季度	qq, q
月	mm, m
年中的日	dy, y
日	dd, d
周	wk, ww
星期	dw, w
小时	hh
分钟	mi, n
秒	ss, s
毫秒	ms
微妙	mcs
纳秒	ns

3.2.3、实例

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

OrderId	ProductName	OrderDate
1	'Computer'	2008-12-29 16:25:46.635

我们使用如下 SELECT 语句：

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

结果：

OrderYear	OrderMonth	OrderDay
2008	12	29

3.3、SQL Server DATEADD() 函数

3.3.1、定义和用法

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

3.3.2、语法

DATEADD(datepart,number,date)

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

datepart 参数可以是下列的值：

datepart	缩写
年	yy, yyyy
季度	qq, q
月	mm, m
年中的日	dy, y
日	dd, d
周	wk, ww
星期	dw, w
小时	hh
分钟	mi, n
秒	ss, s
毫秒	ms
微妙	mcs
纳秒	ns

3.3.3、实例

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

OrderId	ProductName	OrderDate
1	'Computer'	2008-12-29 16:25:46.635

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

我们使用如下 SELECT 语句：

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

结果：

OrderId	OrderPayDate
1	2008-12-31 16:25:46.635

3.4、SQL Server DATEDIFF() 函数

3.4.1、定义和用法

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

3.4.2、语法

DATEDIFF(datepart,startdate,enddate)

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

datepart 参数可以是下列的值：

datepart	缩写
年	yy, yyyy
季度	qq, q
月	mm, m
年中的日	dy, y
日	dd, d
周	wk, ww
星期	dw, w
小时	hh
分钟	mi, n
秒	ss, s
毫秒	ms
微妙	mcs
纳秒	ns

3.4.3、实例

3.4.3.1、例子 1

使用如下 SELECT 语句：

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

结果：

DiffDate
1

3.4.3.2、例子 2

使用如下 SELECT 语句：

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

结果：

DiffDate
-1

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 值：

Style ID	Style 格式
100 或者 0	mon dd yyyy hh:miAM （或者 PM）
101	mm/dd/yy
102	yy.mm.dd
103	dd/mm/yy
104	dd.mm.yy
105	dd-mm-yy
106	dd mon yy
107	Mon dd, yy
108	hh:mm:ss
109 或者 9	mon dd yyyy hh:mi:ss:mmmAM（或者 PM）
110	mm-dd-yy
111	yy/mm/dd
112	yymmdd
113 或者 13	dd mon yyyy hh:mm:ss:mmm(24h)
114	hh:mi:ss:mmm(24h)
120 或者 20	yyyy-mm-dd hh:mi:ss(24h)
121 或者 21	yyyy-mm-dd hh:mi:ss.mmm(24h)
126	yyyy-mm-ddThh:mm:ss.mmm（没有空格）
130	dd mon yyyy hh:mi:ss:mmmAM
131	dd/mm/yy hh:mi:ss:mmmAM

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" 表：

OrderId	ProductName	OrderDate
1	computer	2008-12-26
2	printer	2008-12-26
3	electrograph	2008-11-12
4	telephone	2008-10-19

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

我们使用如下 SELECT 语句：

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

结果集：

OrderId	ProductName	OrderDate
1	computer	2008-12-26
3	electrograph	2008-12-26

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

OrderId	ProductName	OrderDate
1	computer	2008-12-26 16:23:55
2	printer	2008-12-26 10:45:26
3	electrograph	2008-11-12 14:12:08
4	telephone	2008-10-19 12:56:10

如果我们使用上面的 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

posted @ 2023-07-11 16:36 OnlyOnYourself-Lzw 阅读(46) 评论(0) 收藏举报

刷新页面返回顶部

OnlyOnYourself-lzw

知易难行，珍惜当下

MySQL DATE

SQL Date 函数

一、SQL 日期

二、MySQL Date 函数

2.1、MySQL NOW() 函数

2.2.1、 定义和用法

2.2.2、 语法

2.2.3、 实例

2.2.3.1、 例子 1

2.2.3.2、 例子 2

2.2、MySQL CURDATE() 函数

2.2.1、 定义和用法

2.2.2、语法

2.2.3、 实例

2.2.3.1、 例子 1

2.2.3.2、例子 2

2.3、MySQL CURTIME() 函数

2.3.1、 定义和用法

2.3.2、 语法

2.3.3、 实例

2.4、 MySQL DATE() 函数

2.4.1、定义和用法

2.4.2、语法

2.4.3、实例

2.5、MySQL EXTRACT() 函数

2.5.1、定义和用法

2.5.2、语法

2.5.3、实例

2.6、MySQL DATE_ADD() 函数

2.6.1、定义和用法

2.6.2、语法

2.6.3、实例

2.7、MySQL DATE_SUB() 函数

2.7.1、定义和用法

2.7.2、语法

2.7.3、实例

2.8、MySQL DATEDIFF() 函数

2.8.1、定义和用法

2.8.2、语法

2.8.3、实例

2.8.3.1、例子 1

2.8.3.2、 例子 2

2.9、MySQL DATE_FORMAT() 函数

2.9.1、定义和用法

2.9.2、语法

2.9.3、实例

三、SQL Server Date 函数

3.1、SQL Server GETDATE() 函数

3.1.1、定义和用法

3.1.2、语法

3.1.3、实例

3.1.3.1、例子 1

3.1.3.2、例子 2

3.2、SQL Server DATEPART() 函数

3.2.1、定义和用法

3.2.2、语法

3.2.3、实例

3.3、SQL Server DATEADD() 函数

3.3.1、定义和用法

3.3.2、语法

3.3.3、实例

3.4、SQL Server DATEDIFF() 函数

3.4.1、定义和用法

3.4.2、语法

3.4.3、实例

3.4.3.1、例子 1

3.4.3.2、例子 2

3.5、SQL Server CONVERT() 函数

3.5.1、定义和用法

3.5.2、语法

3.5.3、实例

四、SQL Date 数据类型

五、SQL 日期处理

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

公告

2.2.1、定义和用法

2.2.2、语法

2.2.3、实例

2.2.3.1、例子 1

2.2.3.2、例子 2

2.2.1、定义和用法

2.2.3、实例

2.2.3.1、例子 1

2.3.1、定义和用法

2.3.2、语法

2.3.3、实例

2.8.3.2、例子 2