Earlier I wrote a blog post about SQL SERVER – Query to Find First and Last Day of Current Month and it is a very popular post. In this post, I convert the datetime to Varchar and later on use it. However, SQL Expert Michael Usov has made a good point suggesting that it is not always a good idea to convert datetime to any other date format as it is quite possible that we may need it the value in the datetime format for other operation. He has suggested a very quick solution where we can get the first day of the current month with or without time value and keep them with datatype datetime.
Here is the simple script for the same.
-- first day of month
-- with time zeroed out
SELECT CAST(DATEADD(DAY,-DAY(GETDATE())+1, CAST(GETDATE() AS DATE)) AS DATETIME)
-- with time as it was
SELECT DATEADD(DAY,-DAY(GETDATE())+1, CAST(GETDATE() AS DATETIME))
Here is the resultset:
Reference: Pinal Dave (https://blog.sqlauthority.com)
15 Comments. Leave new
select dateadd(month,-1,dateadd(day,1,eomonth(getdate())))
Instead of using dateadd function twice, use it once by following way
SELECT DATEADD(DAY,1,EOMONTH(GETDATE(),-1))
I also want to give one more solution. let me know the feedback for my post.
I also want to give one more solution. let me know the feedback for my post.
——————————————————————————————————–
—Get First day of Current Month
SELECT CAST(CONVERT(VARCHAR(20), YEAR(GETDATE())) + ‘-‘ + CONVERT(VARCHAR(20), MONTH(GETDATE())) + ‘-‘ + CONVERT(VARCHAR(20), 1) AS DATETIME)
—Get Last day of Current Month
SELECT DATEADD(DAY, -1, CAST(CONVERT(VARCHAR(20), YEAR(GETDATE())) + ‘-‘ + CONVERT(VARCHAR(20), MONTH(GETDATE()) + 1) + ‘-‘ + CONVERT(VARCHAR(20), 1) AS DATETIME))
Thanks for reply
This is the best I have seen
SELECT DATEADD(D,1, EOMONTH(GETDATE(), -1))
SQL 2012/14 only though
I have tried the above query in SQL SERVER 2012. I have received the below error,
Msg 195, Level 15, State 10, Line 1
‘EOMONTH’ is not a recognized built-in function name.
Any suggestion….
What is the compatibility level of the database you are trying to run the query on?
This SQL will tell you
SELECT compatibility_level
FROM sys.databases WHERE name = ‘MyDatabaseName’;
GO
The SQL 2012 compatibility level is 110. If your level is lower you need to upgrade the database.
my db is not showing any data….its showing only the header with ‘compatabilitylevel’…so what it means?
Have you changed ‘MyDatabaseName’ to the name of a database in the instance you are connected to?
Using any characters at all is going to slow it down tremendously. Thus, stick to straight date math:
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS first_day_of_month, DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)) AS last_day_of_month
Thanks for your contribution @Scott
Another simple one (only for Sql 2012 & 2014),
Select DATEFROMPARTS(DATEPART(YYYY,GETDATE()),DATEPART(MM,GETDATE()),1)
how about this: select cast(convert(varchar(6),getdate(),112) + ’01’ as datetime)
will work in any database which has only day function
declare @d datetime
set @d = ‘2020/10/05’
select @d – day( @d ) + 35 – day(@d -day( @d ) + 35) as [last day of month],
@d – day( @d ) +1 as [first day of month]