Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: Enterprise
Expertise: Beginner
Nov 30, 2000

Finding Last Day of the Month IN SQL Server


Certain applications require business logic to determine the last day of the month. This is the case for accounting applications that perform such tasks as invoice processing and customer billing. After seeing this logic implemented in various means, even as simple as harcoding the last day of each month, I developed a simple function that does the same:
 
DECLARE @inDate   	AS DATETIME
DECLARE @tempDate 	AS DATETIME
DECLARE @lastDayOfMonth AS DATETIME

/*incoming date to use to determine the last day of the month*/
SET @inDate = '1/1/2000' 

/* take the date and add one month */
SET @tempDate = DateAdd(MONTH, 1 , @inDate)

/* create a date MM/01/YYYY and subtract 1 day so that 
   it returns the last day of the month*/
SET @lastDayOfMonth = DateAdd(DAY, -1 , 
			CONVERT(DATETIME, CONVERT(char(2), MONTH(@tempDate)) + 
			'/01/' +  
			CONVERT(char(4), YEAR(@tempDate))))

PRINT 'Last Day of ' + DATENAME(month, @inDate) + 
      ' is ' + CONVERT(VARCHAR, @lastDayOfMonth)

Therefore the above T-SQL script uses January 1, 2000 and determine the last day of January is the 31st. The output is:
 
Last Day of January is Jan 31 2000 12:00AM
Andrew Holliday
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap