如何在 SQL 中选择一个月的第一天？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1520789/
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
How can I select the first day of a month in SQL?
提问by Brann
I just need to select the first day of the month of a given datetime variable.
我只需要选择给定日期时间变量的月份的第一天。
I know it's quite easy to do using this kind of code:
我知道使用这种代码很容易做到：
select CAST(CAST(YEAR(@mydate) AS VARCHAR(4))
+ '/' + CAST(MONTH(@mydate) AS VARCHAR(2)) + '/01' AS DATETIME)
But this is not very elegant, and probably not very fast either.
但这不是很优雅，也可能不是很快。
Is there a better way to do this? I'm using SQL Server 2008.
有一个更好的方法吗？我正在使用 SQL Server 2008。
回答by LukeH
SELECT DATEADD(month, DATEDIFF(month, 0, @mydate), 0) AS StartOfMonth
回答by Jithin Shaji
In addition to all the above answer, a way based on a function introduced in sql 2012
除了上面所有的答案，一种基于函数的方法介绍 sql 2012
SELECT DATEFROMPARTS(YEAR(@mydate),MONTH(@mydate),1)
回答by Marcos Krucken
Starting with SQL Server 2012:
从 SQL Server 2012 开始：
SELECT DATEADD(DAY,1,EOMONTH(@mydate,-1))
回答by Mayo
The casting of a string (i.e. "5/1/2009") to datetime is certainly more legible but we found code a while back that would return the first of the month...
将字符串（即“2009 年 5 月 1 日”）转换为日期时间肯定更清晰，但我们不久前发现代码将返回该月的第一天...
DECLARE @Date DATETIME
//...
SELECT DATEADD(mm, DATEDIFF(mm,0,@Date), 0)
回答by Abhishek Gupta
Simple Query:
简单查询：
SELECT DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)
-- Instead of GetDate you can put any date.
回答by dove
It is probably quite fast. Why not create it as a sql function.
它可能相当快。为什么不将其创建为 sql 函数。
CREATE FUNCTION [dbo].[GetFirstDayOfMonth] ( @InputDate DATETIME )
RETURNS DATETIME
BEGIN
RETURN CAST(CAST(YEAR(@InputDate) AS VARCHAR(4)) + '/' +
CAST(MONTH(@InputDate) AS VARCHAR(2)) + '/01' AS DATETIME)
END
GO
回答by Alan Burstein
This works too:
这也有效：
SELECT DATEADD(DAY,(DATEPART(DAY,@mydate)-1)*(-1),@mydate) AS FirstOfMonth
回答by adnan umar
Please use this
请使用这个
For Server 2012
DATEFROMPARTS(year('2015-06-30'),month('2015-06-30'),1)Before Server 2012
select cast(cast(year('2015-06-30') as varchar(4))+'-'+ cast(month('2015-06-30') as varchar(2))+'-01' as smalldatetime)
对于服务器 2012
DATEFROMPARTS(year('2015-06-30'),month('2015-06-30'),1)Server 2012 之前
select cast(cast(year('2015-06-30') as varchar(4))+'-'+ cast(month('2015-06-30') as varchar(2))+'-01' as smalldatetime)
回答by nicolai koroslev
First and last day of the current month:
当月的第一天和最后一天：
select dateadd(mm, -1,dateadd(dd, +1, eomonth(getdate()))) as FirstDay,
eomonth(getdate()) as LastDay
回答by Luc
This might be a new function, but you can also use old functions :
这可能是一个新函数，但您也可以使用旧函数：
select DATEFROMPARTS(year(@mydate),month(@mydate),'01')
If the date in the variable was for example '2017-10-29'it would return a date of '2017-10-01'
例如，如果变量中的日期是，'2017-10-29'它将返回一个日期'2017-10-01'
