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

Question

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 unfortunately, this is not very elegant, and not very fast either.

Is there a better way to do this? I'm using SQL Server 2008.

Answer 1

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

Answer 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)

Answer 3

Starting with SQL Server 2012:

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

Answer 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)

Answer 5

Simple Query:

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

Answer 6

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

Answer 7

SELECT @myDate - DAY(@myDate) + 1

Answer 8

This works too:

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

Answer 9

First and last day of the current month:

select dateadd(mm, -1,dateadd(dd, +1, eomonth(getdate()))) as FirstDay, 
eomonth(getdate()) as LastDay

Answer 10

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'

https://docs.microsoft.com/en-us/sql/t-sql/functions/datefromparts-transact-sql?view=sql-server-ver15

Answer 11

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)
   

Answer 12

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

Answer 13

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

Answer 14

Future googlers, on MySQL, try this:

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

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

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

BTW, performance is great on this!

Answer 16

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.

Answer 17

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'

Answer 18

If using SQL Server 2012 or above;

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

Answer 19

Try executing the following query:

SELECT DATE_ADD(DATE_ADD(LAST_DAY(CURRENT_DATE-INTERVAL 1 DAY),INTERVAL 1 DAY),INTERVAL -1 MONTH)

Answer 20

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

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

Answer 21

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

Answer 22

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

Answer 23

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)

Answer 24

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

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

Answer 25

In Sql Server 2012,

 select getdate()-DATEPART(day, getdate())+1

 select DATEADD(Month,1,getdate())-DATEPART(day, getdate())

Answer 26

This query should work very well on MySQL:

SELECT concat(left(curdate(),7),'-01') 

Answer 27

For anyone still looking for an answer, this works like a charm and does away with any dateadds. The timestamp is optional, in case it needs specifying, but works without as well.

SELECT left(convert(varchar, getdate(),23),7)+'-01 00:00:00'

Answer 28

Get First Date and Last Date in the Date we pass as parameter in SQL

     @date DATETIME
    SELECT @date = GETDATE()
    SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@date)-1),@date),105) AS value,
    'First Day of Current Month' AS name
    UNION
    SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@date))),
    DATEADD(mm,1,@date)),105),
    'Last Day of Current Month'
    GO


      **OutPut**

12/01/2019  First Day of Current Month
12/31/2019  Last Day of Current Month

Answer 29

What about something different! Use Format.

DECLARE @Date Date =GetDate();
SELECT CONVERT(Date,Format(@Date,'yyyyMM01'));

We can remove the convert if we are casting to Date Column or variable

DECLARE @Date Date =GetDate();
SELECT @Date =Format(@Date,'yyyyMM01');
SELECT [Date]=@Date

Have Fun :)

Answer 30

Try the following:

select trunc(sysdate,'MM') 
from dual;