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

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

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

0	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

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

Latest Posts