First and Last funda with dates in SQL Server

This post is to provide scripts to identify FIRST and LAST usages for a given date.


--How to get first day of a month 
Declare @Date Datetime = GETDATE()
SELECT DATEADD(month, DATEDIFF(month, 0, @date), 0);

--How to get Last day of a month 
Declare @Date Datetime = GETDATE()
SELECT DATEADD(DAY,-1,DATEADD(mm, DATEDIFF(m,0,@Date)+1,0));

--How to get first day of a Calendar Year 
DECLARE @DATE DATETIME = GETDATE()
SELECT DATEADD(YY, DATEDIFF(YY,0,@DATE), 0) AS STARTOFYEAR

--How to get Last day of a Calendar Year 
DECLARE @DATE DATETIME = GETDATE()
SELECT DATEADD(YY, DATEDIFF(YY,0,@DATE) + 1, -1) AS ENDOFYEAR

I will add more usages of FIRST/LAST day to the above list as I come across in future.

Advertisements

2 thoughts on “First and Last funda with dates in SQL Server”

    1. Hi Vinuraj, Thanks for reading and sharing the script.

      EOMONTH() function has been introduced in SQL Server 2012. My intention is to provide script which works in all SQL Server versions.
      However, a good point and I will definitely add this as a separate script in my post.

      Like

Leave a Reply to latheeshnk Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s