How can I select the first day of a month in SQL?

问题:

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)

回答1:

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

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

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.

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:

Simple Query:

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

回答7:

This works too:

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

回答8:

SELECT @myDate - DAY(@myDate) + 1

回答9:

Please use this

1. For Server 2012

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

2. Before Server 2012

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

回答10:

Future googlers, on MySQL, try this:

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

回答11:

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

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

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

回答12:

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\'

回答13:

If using SQL Server 2012 or above;

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

回答14:

DECLARE @startofmonth date
SET @startofmonth = DATEADD(dd,1,EOMONTH(Getdate(),-2))

The -2 will get you the first day of last month. ie, getdate() is 10/15/18. Your results would be 9/1/18. Change to -1 and your results would be 10/1/18. 0 would be the start of next month, 11/1/2018.. etc etc.

or

DECLARE @startofmonth date
SET @startofmonth = DATEADD(dd,1,EOMONTH(@mydate,-1))

回答15:

----Last Day of Previous Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
LastDay_PreviousMonth
----Last Day of Current Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))
LastDay_CurrentMonth
----Last Day of Next Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0))
LastDay_NextMonth

回答16:

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

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

回答17:

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.

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

Change GETDATE() to the date you want

回答18:

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.

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

BTW, performance is great on this!

回答19:

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.

anyway feel free to try this.

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

回答20:

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)

回答21:

I like to use FORMAT, you can even specify a time

SELECT FORMAT(@myDate,\'yyyy-MM-01 06:00\') first_of_a_month

回答22:

This seems stupidly simple, but works for me:

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