How do I get Days of Month & Year given in sql server

Posted by Sarvesh under Sql Server on 8/31/2009 | Views : 3232 | Status : [Member] | Replies : 3
hi sql experts

I have been asked to calculat No of Days for Month,Year given.

ex: if I give '05-2009' then It should show 31, '02-2009' then 28. This should be done and return by Procedure/Function in sql server itself.

Thanks in advance
sarvesh




Responses

Posted by: Pandians on: 8/31/2009 [Member] [MVP] Silver

Up
0
Down
Hi Sarvesh,

Your scenario can be done by the one of solution given below.

1. SQL Server Procedure / Function :
DECLARE @Month	VARCHAR(2),

@Year VARCHAR(4),
@Date DATETIME

SELECT @Month = '02',
@Year = '2009'

SELECT @Date =@Year + '-' + @Month + '-01'
SELECT DATEDIFF(DAY,@Date,DATEADD(MONTH,1,@Date)) 'Days'

2. CLR Assembly Function :
Create a function with returns Integer type in .Net (VB/C#).
ie: returns Date.DaysInMonth(2,2009)
And Create an Assembly & Function in SQL server which refer the Assembly you created @ .Net and Call the function as normal UDF

I hope the solution given is useful.

Cheers


Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Sarvesh, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Lakhangarg on: 8/31/2009 [Member] [Moderator] Silver

Up
0
Down
Hi Sarvesh-

This is the Generalize method to get the Required Output: You need to pass the value as '05-2009' or '02-2009' when you will pas '12-2009' that is also handled in this code.

Please have a look..

DECLARE @Date varchar(20),@Month int,@NextMonth varchar(20),@Year varchar(5)

SET @Date='12-2009'
SET @Year=SUBSTRING(@Date,4,7)
SET @Month =Convert(int,(SUBSTRING(@Date,1,2)))+1
SET @Date=SUBSTRING(@Date,1,3)+'01-'+@Year
IF(@Month>12)
BEGIN
SET @Month=1
SET @Year=CONVERT(varchar(5),(CONVERT(int, @Year)+1))
END

SET @NextMonth=convert(varchar(2),@Month)+'-01-'+@Year
SELECT Convert(int,((Convert(Datetime,@NextMonth))-(Convert(Datetime,@Date))))


Thanks & Regards
Lakhan Pal Garg
http://lakhangarg.blogspot.com/


Sarvesh, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Lakhangarg on: 8/31/2009 [Member] [Moderator] Silver

Up
0
Down
Hi Sarvesh-

There is one more method to do this :

[Code]
DECLARE @Date varchar(20),@FirstDay varchar(2),@Seperator char(1)
SET @Date='12-2009'
SET @FirstDay='01'
SET @Seperator=SUBSTRING(@Date,3,1)
SET @Date=SUBSTRING(@Date,1,3)+@FirstDay+@Seperator+SUBSTRING(@Date,4,7)
SELECT DATEDIFF(DAY,Convert(Datetime,@Date),DATEADD(MONTH,1,Convert(Datetime,@Date))) 'Days'
[/Code]

Sarvesh, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response