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

Question

How can one 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)

This is not very elegant, and not very fast either.

Is there a better way to do this?

Answer 1

For DATE type in any SQL Server

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

In 99.9% of cases, input parameters with time are not needed because DATE does not require explicit conversion to >= < and BETWEEN

It is also convenient to write WHERE CAST(Table.DateTime AS DATE) BETWEEN @DateB AND @DateE , this expression gives Index Seek YYYY-MM-DD 00:00:00 >= AND <= YYYY-MM-DD 23:59:59

Answer 2

You can use below function for getting 1st day of current month:

DECLARE @mydate date
set @mydate=GETDATE()
SELECT DATEFROMPARTS(YEAR(@mydate),MONTH(@mydate),1)

Answer 3

From SQL Server 2022 you can use DATETRUNC

SELECT DATETRUNC(month, @mydate)

for this need

Answer 4

If you would like to go for SQL Server 2012+ you can try solution I used:

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

Answer 5

Try the following:

select trunc(to_date('23-03-2021','DD-MM-YYYY'),'MM') from dual;

Answer 6

Try the following:

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

Answer 7

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 8

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 9

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://learn.microsoft.com/en-us/sql/t-sql/functions/datefromparts-transact-sql?view=sql-server-ver15

Answer 10

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 11

This query should work very well on MySQL:

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

Answer 12

Starting with SQL Server 2012:

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

Answer 13

First and last day of the current month:

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

Answer 14

Try executing the following query:

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

Answer 15

In Sql Server 2012,

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

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

Answer 16

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

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 18

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

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

Answer 19

If using SQL Server 2012 or above;

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

Answer 20

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 21

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 22

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 23

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 24

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 25

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 26

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 27

Simple Query:

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

Answer 28

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 29

SELECT @myDate - DAY(@myDate) + 1

Answer 30

In addition to all the above answer, a way based on a function introduced in sql 2012

SELECT DATEFROMPARTS(YEAR(@mydate),MONTH(@mydate),1)