Click here to Skip to main content
15,035,480 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I need to write an SQL Function (UDF) to find the First Day (int) and the Last Day (int) when I specify a date.

For example: When I specify (20130217) (i.e. Feb 17th, 2013), I should be able to find the First Day (01) and the Last Day (28) for the specified month (in this case, Feb).

How can I do that? Please provide the necessary code to implement this feature in my SQL Function.

Thanks.
Posted

The first day is always 1 so there's 50% of your work done!

As for the last day, you could try


SQL
CREATE FUNCTION dbo.LastDayInMonth (@when DATETIME)
RETURNS Int
AS
BEGIN
            Declare @lastDate int
            SELECT @lastdate = DAY(DATEADD(DAY,-1,DATEADD(MONTH,1,DATEADD(DAY,1-DAY(@when),@when))))
            RETURN @lastDate
End


which I googled for to save you the trouble
   
Comments
Mycroft Holmes 4-Mar-13 0:43am
   
I actually have all these in a bunch of function but dammed if I was going to do the work for him!
_Maxxx_ 4-Mar-13 0:57am
   
Me too - but not on me, and it was faster to google and cut and paste than anything else.

It's so rare I'm smart enough to answer a question I was quite please someone had asked something so basic ;)
Hi ,

Try like this...

SQL
DECLARE @Date VARCHAR(10)='20120404', @FirstDate VARCHAR(10), @FirstDay INT, @LastDay INT

SELECT @FirstDate=SUBSTRING(@Date,1,6)+'01'

SELECT @FirstDay=1, @LastDay=DATEDIFF(DAY,@FirstDate,DATEADD(MONTH,1,CONVERT(DATETIME,@FirstDate,103)))

SELECT RIGHT('00'+CAST(@FirstDay AS VARCHAR(5)),2) 'FirstDay', RIGHT('00'+CAST(@LastDay AS VARCHAR(5)),2) 'LastDay' 

Regards,
GVPrabu
   
v3
you can even do it in one line
SQL
SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)
SELECT DATEADD(DAY, -(DAY(DATEADD(MONTH, 1, GETDATE()))),DATEADD(MONTH, 1, GETDATE()))
   
   
Hi,

Try this....Form this into function and replace GetDate() into your input parameter variable

SQL
SELECT SUBSTRING(CONVERT(varchar,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0), 103),1,2)
SELECT SUBSTRING(CONVERT(varchar,DATEADD(ms,- 2,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0))), 103),1,2)
   
In the interests of completeness, I submit this solution based on Solution 7 above:
--USE [cpqaAnswers]
--GO
--CREATE SCHEMA [cpqa] ... -- etc

CREATE FUNCTION [cpqa].[udf_IF_firstAndLast]()
		RETURNS [nvarchar](256)
		AS
		BEGIN
			DECLARE @firstDate [datetime]
			SET @firstDate = (SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) AS [first])
			--RETURN @firstDate
		
			DECLARE @lastDate [datetime]
			SET @lastDate =(SELECT DATEADD(DAY, -(DAY(DATEADD(month, 1, GETDATE()))),DATEADD(month, 1, GETDATE())) AS [Last])
			--RETURN @lastDate
			
			DECLARE @softBAReturn [nvarchar](256)
			SET @softBAReturn = CONVERT([nvarchar],@firstDate,103)  + ' --- ' + CONVERT([nvarchar],@lastDate,103)
			RETURN @softBAReturn
			
		END

Run that function in the following manner:
SELECT [cpqa].[udf_IF_firstAndLast]()

And that returns:
01/03/2013 --- 31/03/2013
   
v2
You can do this by converting the value into a datetime data type and manipulating the value using the datetime functions.

First day - split the DT into the 2 date components, change the day value to 1 and put the type back together.

Last day, get the first day of the NEXT month and use DataAdd to add -1 day to the datetime.

As for writing the code for you, I charge $150 per hour for adhoc coding.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900