如何在SQL中选择一个月的第一天?

I just need to select the first day of the month of a given datetime variable.

我只需要选择给定datetime变量的第一天。

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)

19 个解决方案

#1

433

SELECT DATEADD(month, DATEDIFF(month, 0, @mydate), 0) AS StartOfMonth

#2

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)

#3

SQL Server 2008:

SELECT DATEADD(DAY,1,EOMONTH(@mydate,-1))

#4

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...

弦的铸造(例如“5/1/2009”)到datetime当然更容易理解，但我们之前发现的代码会返回第一个月……

DECLARE @Date DATETIME
//...
SELECT DATEADD(mm, DATEDIFF(mm,0,@Date), 0)

#5

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

#6

This works too:

其工作原理:

    SELECT DATEADD(DAY,(DATEPART(DAY,@mydate)-1)*(-1),@mydate) AS FirstOfMonth

#7

Simple Query:

简单查询:

SELECT DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0) 
-- Instead of GetDate you can put any date.

#8

SELECT @myDate - DAY(@myDate) + 1

#9

Future googlers, on MySQL, try this:

未来的谷歌人，在MySQL上试试这个:

select date_sub(ref_date, interval day(ref_date)-1 day) as day1;

#10

Please use this

请使用这个

For Server 2012

2012服务器

DATEFROMPARTS(year('2015-06-30'),month('2015-06-30'),1)

Before Server 2012

2012年服务器之前

select  cast(cast(year('2015-06-30') as varchar(4))+'-'+ cast(month('2015-06-30') as varchar(2))+'-01' as smalldatetime)

#11

Here we can use below query to the first date of the month and last date of the month.

在这里，我们可以使用下面的查询到本月的第一个日期和最后一个月的日期。

SELECT DATEADD(DAY,1,EOMONTH(Getdate(),-1)) as 'FD',Cast(Getdate()-1 as Date)
as 'LD'

#12

If using SQL Server 2012 or above;

如使用SQL Server 2012或以上;

SELECT DATEADD(MONTH, -1, DATEADD(DAY, 1, EOMONTH(GETDATE())))

#13

select CONVERT(date,DATEADD(dd,-(DATEPART(dd,getdate())-1),getdate()),120)

选择转换(日期,返回(dd -(DATEPART(dd,获取当前日期())1),获取当前日期()),120)

This function will provide you date part of start date of the month

此功能将为您提供每月开始日期的一部分

#14

SELECT DATEADD (DAY, -1 * (DAY(GETDATE()) - 1), GETDATE())

.....................................................................

If you dont want the time, then convert it to DATE or if want to make to time to 0:00:00, Convert to DATE and then back to DATETIME.

如果你不想要时间，那么将它转换为DATE或者如果你想把时间转换为0:00，将它转换为DATE，然后再转换为DATETIME。

SELECT CONVERT (DATETIME,  
CONVERT (DATE, DATEADD (DAY, -1 * (DAY(GETDATE()) - 1),
GETDATE())))

Change GETDATE() to the date you want

将GETDATE()更改为所需的日期

#15

I used GETDATE() as a date to work with, you can replace it with the date which you need.
Here's how this works: First we format the date in YYYYMMDD... format truncating to keep just the 6 leftmost characters in order to keep just the YYYYMM portion, and then append '01' as the month - and voila! you have the first day of the current month.

我使用GETDATE()作为工作日期，您可以用需要的日期替换它。这是如何工作的:首先我们在yyyyymmdd中格式化日期……为了保留YYYYMM的部分，保留6个最左边的字符，然后将“01”添加到这个月——这样就可以了!你有当月的第一天。

SELECT CAST(CONVERT(VARCHAR(6),GETDATE(),112) +'01' AS DATETIME) AS StartOfMonth

BTW, performance is great on this!

顺便说一句，这方面的表现很棒!

#16

I personal recommended that the sql below because when i try use date function in the condition clause, its slow down my query speed very much.

我个人建议使用下面的sql，因为当我尝试在condition子句中使用date函数时，它会极大地降低我的查询速度。

anyway feel free to try this.

不管怎样，你可以试试这个。

select CONCAT(DATEPART(YYYY,@mydate),'-',DATEPART(MM,@mydate),'-01')

#17

Not to compete with any of the great minds here, but a simple suggestion slightly different that the accepted answer above.

不是要与这里的任何一个伟大的头脑竞争，而是一个简单的建议，与上面所接受的答案稍有不同。

select dateadd(day, -(datepart(day,@date)+1,@date)

#18

If you are looking at this today, and using SQL server 2012 or newer you have the EOMONTH function which makes things easier:

如果你今天看这个，使用SQL server 2012或更新你有EOMONTH函数，这让事情变得更简单:

SELECT DATEADD(day, 1, EOMONTH(DATEADD(month, -1, GETDATE()))) as firstdateofmonth

You can change GETDATE() with whatever date variable you want.

您可以使用您想要的任何日期变量来更改GETDATE()。

#19

-2

This seems stupidly simple, but works for me:

这看起来简单得愚蠢，但对我来说是行得通的:

  select DATE_FORMAT(NOW(), '%Y-%m-1')

#1

433

SELECT DATEADD(month, DATEDIFF(month, 0, @mydate), 0) AS StartOfMonth

#2