Finding If Date is LastDay of the Month

Pinal Dave, blogger and speaker, posted about SQL SERVER – Finding If Date is LastDay of the Month in which two simple methods are shown to know if a given date is a last day of that month.

Here are two more methods to find the same

Consider the following set of data

create table #temp(dates datetime)

insert into #temp
select ‘20090114’ union all
select ‘20080131’ union all
select ‘20070111’ union all
select ‘20090330’ union all
select ‘20120229’ union all
select ‘20100228’

Method 1 : Add 1 day and see if the month is different. If it is then it is the last day

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

Method 2 : Find the last day for a given date. If the given date is equal to it, then it is a last day

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

 

Both the methods return the following results

dates                            is_lastday
———————————-  ————-
2009-01-14 00:00:00.000 no
2008-01-31 00:00:00.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

One thought on “Finding If Date is LastDay of the Month

Leave a 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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s