How to Select the First Day of a Month in Sql

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

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

SQL SELECT First Day and Last Day of the Month.

Convert @FirstDOM to DATE as below:

declare @FirstDOM datetime, @LastDOM datetime

set @FirstDOM = (select CONVERT(DATE,dateadd(dd,-(day(getdate())-1),getdate())) )
set @LastDOM = (select dateadd(s,-1,dateadd(mm,datediff(m,0,getdate())+1,0))) 
SELECT @FirstDOM,@LastDOM

I hope this will help!

Thanks,

Swapnil

SQL Query to only pull one record for first day of month, for every month

If all your time are zeroed all you do need is to get everything where DATEPART is first day.

select * from dbo.MyTable mt where DATEPART(day, mt.MyDate) = 1

It will work if you got one row per day. Off course you will need to use DISTINCT or an aggregation if you got more than one row per day.

SQL Retrieve data from First day of the month a year ago to today

select * 
from
where datefield>=
dateadd(year,-1,
DATEADD(month, DATEDIFF(month, 0, getdate()), 0)--gives first date of month
)

or from 2012: you can use DATEFROMPARTS as mentioned by Alex in comments

select DATEFROMPARTS(YEAR(GETDATE()) - 1, MONTH(GETDATE()), 1)

Rounding dates to first day of the month

If, for example, it is 15th of given month then you subtract 14 and cast the result to date:

SELECT ArrivalDate
     , CAST(DATEADD(DAY, -DATEPART(DAY, ArrivalDate) + 1, ArrivalDate) AS DATE) AS FirstDay
FROM (VALUES
    (CURRENT_TIMESTAMP)
) AS t(ArrivalDate)

ArrivalDate             | FirstDay
2019-05-15 09:35:12.050 | 2019-05-01

But my favorite is EOMONTH which requires SQL Server 2012:

SELECT ArrivalDate
     , DATEADD(DAY, 1, EOMONTH(ArrivalDate, -1)) AS FirstDay
FROM (VALUES
    (CURRENT_TIMESTAMP)
) AS t(ArrivalDate)

ArrivalDate             | FirstDay
2019-05-15 09:35:52.657 | 2019-05-01

SQL get First day of month 3 months before current Month

The logic is simple:

• Subtract the day of the month minus 1 days from the date
• Subtract three months

In SQL Server:

select dateadd(month, -3, dateadd(day, 1 - day(dte), dte))

For the current date:

select cast(dateadd(month, -3, dateadd(day, 1 - day(getdate()), getdate())) as date)

And as a string:

select convert(varchar(10),
               dateadd(month, -3, dateadd(day, 1 - day(getdate()), getdate())),
               120)

---

Related Topics