In my very old post SQL SERVER – Query to Find First and Last Day of Current Month – Date Function, I had explained how to find the first day and last day of the current date.
The method I showed you in the old posted uses CONVERT function as well DATEADD functions
There is a simple way to the same
Let us consider the following code
DECLARE @DATE DATETIME SET @DATE='2017-10-28' SELECT @DATE AS GIVEN_DATE, @DATE-DAY(@DATE)+1 AS FIRST_DAY_OF_DATE, EOMONTH(@DATE) AS LAST_DAY_OF_MONTH
There result is shown below
The logic is very simple.
The first part @DATE-DAY(@DATE) results to the Last day of a previous month and adding 1 to it will result on the first day of current month.
The second part EOMONTH(@DATE) makes use of SYSTEM function EOMONTH which results to the last day of the given date.
Note that the system function EOMONTH was introduced in version 2012. For older versions of SQL Server refer the following relevant posts for alternate methods.
SQL SERVER – Script/Function to Find Last Day of Month
SQL SERVER – Validating If Date is Last Day of the Year, Month or Day
Let me know if you know any other better way to get the first and last day of the current date. I will be happy to post your script with due credit to you.
Reference: Pinal Dave (https://blog.sqlauthority.com)
12 Comments. Leave new
Hi Pinal,
Nice one, especially the logic used to calculate First day of the Current Date.
Thanks,
Srini
Select
Dateadd(dd, datediff(dd, 0, getdate()), 0)
Will return today at midnight.
Replace the two occurrences of dd with MM to get the beginning of the month.
Need to round a time to the beginning of the minute? Replace the dd with minute.
Beginning of the quarter? QQ.
Beginning of the year? YY.
If I recall correctly, logic works the same also with beginning of the week.
Here’s how it works:
datediff(dd, 0, getdate())
Returns the number of date intervals (days, in this case) between date 0 (sql server’s epoch) and current date (or any other date you give it).
To explain the next step, let’s say that returns 1701
Dateadd(dd, 1701, 0)
Will return the date that’s 1701 time intervals (days, here) since the epoch, date 0.
This was very helpful. Thanks!
Glad I can help!
a life saver, thank you :D
Tks (Obrigada) :D
First day of current month doesn’t work when you pass first day of the month or you want to use GETDATE() in the first day of a month
Use that to get the first day SELECT dateadd(day,datediff(dd, 0, getdate()),-1)
@Houman, this seems to work just fine:
select dateadd(month,datediff(month, 0, ‘2021-02-01’), 0)
@DATE-DAY(@DATE) does not work for a “date” datatype
DECLARE @DATE DATETIME
SET @DATE=’2022-04-25′
SELECT @DATE AS GIVEN_DATE
, DATEADD(d,1,EOMONTH(@DATE)) AS FIRST_DAY_OF_MONTH
, EOMONTH(@DATE) AS LAST_DAY_OF_MONTH
This gives first day of next month