13,050,358 members (140,198 online)
Rate this:
See more: , +
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 3-Mar-13 18:26pm

Rate this:

## Solution 2

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

As for the last day, you could try

```CREATE FUNCTION dbo.LastDayInMonth (@when DATETIME)
RETURNS Int
AS
BEGIN
Declare @lastDate int
RETURN @lastDate
End```

which I googled for to save you the trouble
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.

Rate this:

## Solution 3

Hi ,

Try like this...

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

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

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

Regards,
GVPrabu
v3
Rate this:

## Solution 7

you can even do it in one line
```SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)
Rate this:

Rate this:

## Solution 5

Hi,

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

```SELECT SUBSTRING(CONVERT(varchar,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0), 103),1,2)
Rate this:

## Solution 8

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]
--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
Rate this:

## Solution 1

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.
Rate this:

## Solution 6

Top Experts
Last 24hrsThis month
 OriginalGriff 268 Sheila Pontes 125 Graeme_Grant 109 Kornfeld Eliyahu Peter 70 omerkamran 40
 OriginalGriff 5,168 RickZeeland 1,934 ppolymorphe 1,758 F-ES Sitecore 1,566 Dave Kreskowiak 1,389