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