How to Get First,Last and No of Days in Month Using T-Sql

Introduction

I have read few questions in forums, about how to select first, last date month and how many days in a month. In my project had used some query to perform these tasks. I will share those with article.

1. How to select start day of the month,
2. How to select last day of the month,
3. How to get no of days in a month.

How to select start day of the month

1. How to get start date of current month

Sql Query

SELECT DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))-1),DATEADD(mm,0,GETDATE()))

In above script is very simple, you have understand each section is how it work. I have simplified like below,

SELECT DATEADD (dd,
		-(DAY(DATEADD(mm,1,GETDATE()))-1),
		DATEADD(mm,0,GETDATE())
		)

Now you see in above script has three arguments to first DATEADD function. The first one is datepart is day of we are going to add. The second argument is how many days we are going to add,

select -(DAY(DATEADD(mm,1,GETDATE()))-1)

Its means say today is 16, so I’m going to add -16 days. The last argument is current date.so final output will be like below,

Output

2010-12-01 00:42:16.937

2. How to get first day for previous month

Sql Query

SELECT DATEADD (dd,				  
		-(DAY(DATEADD(mm,1,GETDATE()))-1),
		DATEADD(mm,-1,GETDATE())
		)

You can use the same script with small modifications. In last argument I have given current date for get current month start date. So for previous month just added -1 for month.

Output

2010-11-01 00:44:13.937 

How to select last date of current month

1. How to Get Last day of current month

Sql Query

SELECT DATEADD(dd,
	 	-DAY(DATEADD(m,1,GETDATE()))
		,DATEADD(m,1,GETDATE())
		)

In above script I’m using DATEADD function, first argument as day and second argument is how many from start date of the current month until today it will be -17 and third argument is add a month to current month. So current month is 12 2010 so when I add one month the result will be 2011-01-17 22:45:21.217.so from 17 January 2011 to reduce the 17 days we will last date of current month.

Output

2010-12-31 22:46:27.530

2. How to Get last date of previous month

Sql Query

SELECT DATEADD(dd,
		-DAY(DATEADD(m,1,GETDATE()))
		,DATEADD(m,0,GETDATE())
		)

You can use the same script, but just add 0 instead 1 in third argument.as like above

Output

2010-11-30 22:47:04.920

How to number of days in Month

Sql Query

select datepart (dd,
		 dateadd(dd,-1,
		 dateadd(mm,1,
		 cast(cast(year(getdate()) as varchar)+'-'+cast(month(getdate()) as varchar)+'-1' as datetime)
		    )
		  )
		)

This query seem complicated, so I’m going to spilt about script into three parts,

1.select cast(cast(year(getdate()) as varchar)+'-'+cast(month(getdate()) as varchar)+'-1' as datetime)

2.select DATEADD(mm,1,cast(cast(year(getdate()) as varchar)+'-'+cast(month(getdate()) as varchar)+'-1' as datetime))

3.select DATEADD(dd,-1,DATEADD(mm,1,cast(cast(year(getdate()) as varchar)+'-'+cast(month(getdate()) as varchar)+'-1' as datetime)))

Above three queries I have break from bottom to top my original script. When I’m first script,

select cast(cast(year(getdate()) as varchar)+'-'+cast(month(getdate()) as varchar)+'-1' as datetime)

Output

2010-12-01

In above script I’m try to get current month first date. So I have to find first day to lower bound of the month.

Through the second script, I have to get next month first date. Because that’s upper bound current month date,

select DATEADD(mm,1,cast(cast(year(getdate()) as varchar)+'-'+cast(month(getdate()) as varchar)+'-1' as datetime))

Output

2011-01-01

 Third part is actual query to bring last date of current month,

select DATEADD(dd,-1,DATEADD(mm,1,cast(cast(year(getdate()) as varchar)+'-'+cast(month(getdate()) as varchar)+'-1' as datetime)))

Output

2010-12-31

So I have got last date of the current month, let’s use the DATEPART function to extract day value from third script. Final output  as like below,

Output

31

Hopes help, if you have comments please post it. Thank you for reading.