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

Brann_47341 0
Viewed 1192130

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.

datetime sql-server-2008 tsql sql-server sql
34 Answers
user_123991600

First and last day of the current month:

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

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

This query should work very well on MySQL: 

SELECT concat(left(curdate(),7),'-01')
user_118693480 
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))
user_65048010 
----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
user_17747250

Try executing the following query:

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

user_97924180

Future googlers, on MySQL, try this:

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

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

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

In Sql Server 2012,

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

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

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

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
user_88979090

From SQL Server 2022 you can use DATETRUNC

SELECT DATETRUNC(month, @mydate)

for this need

user_732260

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

user_3473740

Try the following:

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

Try the following:

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