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. His script to get the first day of current month is below.
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:
Why Keeping the First Day of Current Month as a Date Helps
Michael’s point is a good one, and it matters most in the WHERE clause. When the value stays a date, SQL Server can compare it directly with a date column and still use an index on that column. A string has to be converted back first, and if you convert the column instead of the value, the index often cannot be used for a seek.
The pattern I use most for a monthly report is simple: find the start of this month, add one month to it, and filter with OrderDate >= @Start AND OrderDate < @NextStart. It works whether the column is DATE, DATETIME or DATETIME2, and it never misses rows from the last evening of the month.
If you are on SQL Server 2012 or later, there are two more readable options. DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1) builds the date from its parts and returns a DATE. DATEADD(DAY, 1, EOMONTH(GETDATE(), -1)) takes the last day of the previous month and adds one day. Both give the same answer as the older DATEADD and DATEDIFF trick, so pick the one your team finds easiest to read. Whichever you choose, test it on the first and the last day of a month, because that is where date logic usually breaks.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.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]