Finding If Date is LastDay of the Month – Part 2

In my earlier post I showed two methods on how to know if the given date is the last day of month. There are two more methods to know the same which I provide in this post

Consider the following data set

create table #temp(dates datetime)

insert into #temp
select '20090114' union all
select '20080131 10:10:10' union all
select '20070111' union all
select '20090330' union all
select '20120229' union all
select '20100228'

Method 1 : Use EOMONTH function (works from version 2012 onwards)

select 
	dates,
	case 
		when cast(dates as date)=eomonth(dates) then 
			'yes' 
		else 
			'no' 
	end 
		as is_lastday
from 
	#temp

Method 2 : Subtract day part from original date and add one month

select 
	dates,
	case 
		when dates=dateadd(month,1,dates-day(dates)) then 
			'yes' 
		else 'no' 
	end 
		as is_lastday
from 
	#temp

The result of the above two queries are

dates                   is_lastday
----------------------- ----------
2009-01-14 00:00:00.000 no
2008-01-31 10:10:10.000 yes
2007-01-11 00:00:00.000 no
2009-03-30 00:00:00.000 no
2012-02-29 00:00:00.000 yes
2010-02-28 00:00:00.000 yes

Advertisements