SQL 将日期四舍五入到当月的第一天
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33213243/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Rounding dates to first day of the month
提问by user3115933
I am using SQL Server 2014 and I am working with a column from one of my tables, which list arrival dates.
我正在使用 SQL Server 2014 并且我正在处理我的一个表中的一列，其中列出了到达日期。
It is in the following format:
它采用以下格式：
ArrivalDate
2015-10-17 00:00:00.000
2015-12-03 00:00:00.000
I am writing a query that would pull data from the above table, including the ArrivalDate column. However, I will need to convert the dates so that they become the first day of their respective months.
我正在编写一个查询，该查询将从上表中提取数据，包括 ArrivalDate 列。但是，我需要转换日期，使它们成为各自月份的第一天。
In other words, my query should output the above example as follows:
换句话说，我的查询应该输出上面的例子如下：
2015-10-01 00:00:00.000
2015-12-01 00:00:00.000
I need this so that I can create a relationship with my Date Table in my PowerPivot model.
我需要这个，以便我可以在我的 PowerPivot 模型中创建与我的日期表的关系。
I've tried this syntax but it is not meeting my requirements:
我已经尝试过这种语法，但它不符合我的要求：
CONVERT(CHAR(4),[ArrivalDate], 100) + CONVERT(CHAR(4), [ArrivalDate], 120) AS [MTH2]
回答by Salman A
If, for example, it is 15th of given month then you subtract 14 and cast the result to date:
例如，如果它是给定月份的 15 日，那么您减去 14 并将结果转换为日期：
SELECT ArrivalDate
, CAST(DATEADD(DAY, -DATEPART(DAY, ArrivalDate) + 1, ArrivalDate) AS DATE) AS FirstDay
FROM (VALUES
(CURRENT_TIMESTAMP)
) AS t(ArrivalDate)
ArrivalDate | FirstDay
2019-05-15 09:35:12.050 | 2019-05-01
But my favorite is EOMONTHwhich requires SQL Server 2012:
但我最喜欢的是EOMONTH它需要 SQL Server 2012：
SELECT ArrivalDate
, DATEADD(DAY, 1, EOMONTH(ArrivalDate, -1)) AS FirstDay
FROM (VALUES
(CURRENT_TIMESTAMP)
) AS t(ArrivalDate)
ArrivalDate | FirstDay
2019-05-15 09:35:52.657 | 2019-05-01
回答by Lukasz Szozda
回答by Metaphor
Round date to first of the month:
到本月第一天的回合日期：
DATEADD(MONTH, DATEDIFF(MONTH, 0, DateColumn), 0)
回答by Code Different
Beginning with SQL Server 2012, you can also use DATEFROMPARTS:
从 SQL Server 2012 开始，您还可以使用DATEFROMPARTS：
SELECT DATEFROMPARTS(YEAR(ArrivalDate), MONTH(ArrivalDate), 1)
FROM my_table
回答by Ryan Kramer
Or just simply use the ROUND function -
或者只是简单地使用 ROUND 功能 -
SELECT ROUND (TO_DATE ('27-OCT-00'),'YEAR') "New Year" FROM DUAL;
SELECT ROUND (TO_DATE ('27-OCT-00'),'YEAR') "新年" FROM DUAL;
New Year
新年
01-JAN-01
01-JAN-01
