Days in a month

One thing which amazes me is the lack of some basic programmability in SQL especially around some core requirements of T-SQL. Dates is one of them.

To get the number of days in a month we get the first day in the month and the last. We then subtract from each other. One contains the 1st of this month and the other contains the 1st of next month. The minus includes one of the days but not the other.

The first day is found by taking todays day of the month, minusing it from 1 (-day of month + 1 ) and then adding it to today day of the month which equates to 1.

the last day of the month / 1st day of next month is found by adding 1 month onto the first day


declare @daysInMonth as int
declare @firstMonth as dateTime
declare @firstNextMonth as dateTime

set @firstMonth = DATEADD (d, 1 - DAY(GETDATE()), GETDATE())
set @firstNextMonth = DATEADD(m, 1, DATEADD (d, 1 - DAY(GETDATE()), GETDATE()))
set @daysInMonth =  DATEDIFF(day,@firstMonth, @firstNextMonth)
select @daysInMonth
Advertisements

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