SQL SERVER – Simple Method to Find FIRST and LAST Day of Current Date

SQL SERVER - Simple Method to Find FIRST and LAST Day of Current Date flmonth 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

Solarwinds
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

SQL SERVER - Simple Method to Find FIRST and LAST Day of Current Date date_result

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)

Solarwinds
, , ,
Previous Post
SQL SERVER – Remove Duplicate Rows Using UNION Operator
Next Post
SQL SERVER – Restoring SQL Server 2017 to SQL Server 2005 Using Generate Scripts

Related Posts

4 Comments. Leave new

  • Hi Pinal,

    Nice one, especially the logic used to calculate First day of the Current Date.

    Thanks,
    Srini

    Reply
  • 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.

    Reply
  • This was very helpful. Thanks!

    Reply
  • Glad I can help!

    Reply

Leave a Reply

Menu