Find the first Day (01) and the Last Day (28) for a specified month in SQL Function (UDF), as an example (20091207)?

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

, +

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

iamFahhad

Add a Solution

8 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

_Maxxx_ · Answer 1 · 2013-03-03T18:40:00

Solution 2

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

Posted 3-Mar-13 18:40pm

_Maxxx_

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 ;)

gvprabu · Answer 2 · 2013-03-03T18:44:00

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

Amol_B · Answer 3 · 2013-03-06T22:38:00

Solution 7

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()))

Posted 6-Mar-13 22:38pm

Amol_B

muruga.n · Answer 4 · 2013-03-03T19:50:00

Solution 4

check this one,i think this one help you.

http://sqlandme.com/2011/05/16/how-to-get-first-and-last-day-of-a-month-tsql/[^]

Posted 3-Mar-13 19:50pm

muruga.n

Davidduraisamy · Answer 5 · 2013-03-03T19:50:00

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)

RedDk · Answer 6 · 2013-03-07T07:07:00

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

Mycroft Holmes · Answer 7 · 2013-03-03T18:40:00

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.

muruga.n · Answer 8 · 2013-03-03T19:52:00

Solution 6

Hi,
Look this,

http://sqlandme.com/2011/05/16/how-to-get-first-and-last-day-of-a-month-tsql/[^]

Posted 3-Mar-13 19:52pm

muruga.n

Find the first Day (01) and the Last Day (28) for a specified month in SQL Function (UDF), as an example (20091207)?

8 solutions

Solution 2

Solution 3

Solution 7

Solution 4

Solution 5

Solution 8

Solution 1

Solution 6

Add your solution here

Preview 0