时间戳

首页 > 解决方案 > MySql 相当于 MS 访问 DatePart

mysql - MySql 相当于 MS 访问 DatePart

问题描述

我正在尝试将 MS Access 查询转换为 mysql 查询，但在SELECT部分中我发现了一个 DatePart 函数，它从表 tbl_trade 中选择年份。我知道我们在 mysql 中有内置函数，如 YEAR()、EXTRACT() 等，但似乎我不能将它们用作

SELECT YEAR(tbl_trade.create_date) from tbl_trade;

它给出了一个错误

Unknown table 'tbl_trade' in field list

另外我不知道如何在mysql中实现DatePart函数的第3个和第4个参数。您可以从https://www.techonthenet.com/access/functions/date/datepart.php找到有关 DatePart 函数的更多详细信息

最后，我希望能够实现这个

SELECT DatePart("yyyy", tbl_trade.create_date,1,2) FROM tbl_trade

标签： mysqlvbams-access

解决方案

最接近的等价物是extract. 它需要各种间隔。

select create_date, extract(year from create_date)
  from tbl_trade;

+---------------------+--------------------------------+
| create_date         | extract(year from create_date) |
+---------------------+--------------------------------+
| 2019-01-02 00:00:00 |                           2019 |
| 2019-01-02 00:00:00 |                           2019 |
+---------------------+--------------------------------+

至于更改星期和年份的定义，您可以将各种模式传递给week.

-- Mode 7: Week starts on Monday. Week 1 is the first with a Monday.

select create_date, week(create_date, 7) from tbl_trade;
+---------------------+----------------------+
| create_date         | week(create_date, 7) |
+---------------------+----------------------+
| 2019-01-02 00:00:00 |                   53 |
| 2019-01-02 00:00:00 |                   53 |
+---------------------+----------------------+

或者通过设置default_week_format影响所有日期功能来做到这一点。

set default_week_format = 7;

select create_date, extract(week from create_date) from tbl_trade;
+---------------------+--------------------------------+
| create_date         | extract(week from create_date) |
+---------------------+--------------------------------+
| 2019-01-02 00:00:00 |                             53 |
| 2019-01-02 00:00:00 |                             53 |
+---------------------+--------------------------------+

对于精细控制，有date_format.

-- %v is mode 3. First DOW is Monday. First week is has 4 days or more.

select create_date, date_format(create_date, '%W week %v') from tbl_trade;
+---------------------+----------------------------------------+
| create_date         | date_format(create_date, '%W week %v') |
+---------------------+----------------------------------------+
| 2019-01-02 00:00:00 | Wednesday week 01                      |
| 2019-01-02 00:00:00 | Wednesday week 01                      |
+---------------------+----------------------------------------+

推荐阅读