Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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 19:26pm
Rate this: bad
good
Please Sign up or sign in to vote.

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
            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
  Permalink  
Comments
Mycroft Holmes at 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_ at 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 ;)
Rate this: bad
good
Please Sign up or sign in to vote.

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 @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
  Permalink  
v3
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 7

you can even do it in one line
SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)
SELECT DATEADD(DAY, -(DAY(DATEADD(MONTH, 1, GETDATE()))),DATEADD(MONTH, 1, GETDATE()))
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

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)
SELECT SUBSTRING(CONVERT(varchar,DATEADD(ms,- 2,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0))), 103),1,2)
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

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]
			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
  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

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.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 6

  Permalink  

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

  Print Answers RSS
0 OriginalGriff 330
1 Sergey Alexandrovich Kryukov 279
2 BillWoodruff 245
3 Afzaal Ahmad Zeeshan 215
4 CPallini 205
0 OriginalGriff 5,635
1 DamithSL 4,496
2 Maciej Los 3,942
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,180


Advertise | Privacy | Mobile
Web04 | 2.8.141216.1 | Last Updated 7 Mar 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100